[solution] Inserting data, ignoring any key conflicts

By.

min read

My profile

Share this:

How to insert data, but you are not sure if the data exists, but dont’ want to see a mysql error while inserting?
This is really handy when inserting [url=http://www.ramonfincken.com/tag/xml.html]XML[/url] / [url=http://www.ramonfincken.com/tag/rss.html]RSS[/url] feeds !

There are two solutions:

[b:56c77dd7c6]Solution1 :[/b:56c77dd7c6]
[code:1:56c77dd7c6]$sql = ‘INSERT INTO ‘.TABLE_NAME .’ ( key1, key2 ) VALUES ( val1, val2 ) ON DUPLICATE KEY UPDATE fetched=fetched+1′;[/code:1:56c77dd7c6]
This will insert the data if there is no key conflict.
If there is a key conflict ( existing key = key you want to insert ), no error will be given and NO data will be updated except for the row called “fetched” which has a counter and will increase with the count of 1;
( fetched NULL allowed, DEFAULT = 0 )

[b:56c77dd7c6]Solution2a :[/b:56c77dd7c6]
[code:1:56c77dd7c6]$sql = ‘INSERT IGNORE INTO ‘. TABLE_NAME . ‘ ( key1, key2 ) VALUES ( val1, val2 )’;[/code:1:56c77dd7c6]
This will insert the data if there is no key conflict.
If there is a key conflict ( existing key = key you want to insert ), no error will be given and NO data will be updated.

[b:56c77dd7c6]Solution2b :[/b:56c77dd7c6]
You can even do cool things like this, if you don’t care if the insert is carried out right away:
[code:1:56c77dd7c6]$sql = ‘INSERT LOW_PRIORITY IGNORE INTO ‘. TABLE_NAME . ‘ ( key1, key2 ) VALUES ( val1, val2 )’;[/code:1:56c77dd7c6]

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *