MySQL Insert If Not Exists
Why doesn't MySQL Insert If Not exists work?????
UPDATE: CHECK AT THE BOTTOM OF POST
First of all, this information relates solely to MySQL and may not apply to MSSQL or PostgreSQL.
Have you been trying to insert records into a table based on whether there is already a record there of not. Trying multiple SQL queries of the form:
INSERT INTO list
(code, name, place)
SELECT *
FROM
(SELECT ('ABC', 'abc ', 'Johannesburg',)) AS tmp
WHERE NOT EXISTS
(
SELECT * FROM sharelist WHERE code = 'ABC'
);
Some people also use:
INSERT INTO table (fields) VALUES (values) ON DUPLICATE KEY UPDATE ID=ID;
Provided you have a unique primary key. This is not the correct way of using a Mysql Insert if not Exists...
Well Bogdan has the answers.
There is syntax used to tackle this exact problem in an efficient way and that is:
INSERT IGNORE INTO `list`
`code` = 'ABC',
`name` = 'abc',
`place` = 'Johannesburg',
;
It's that easy.
If the record exists it will be ignored (silently skipped). If it does not it will be inserted.
Warm and Easy.
Update: No maybe not so easy. Damn you Mysql Insert if not Exists
The problem arises when you have a field (most likely an id) which is not added by you. So an auto incremented field. The above code will not skip the insert and will always insert the record in as it is unique because of the auto increment field.
So if you have an auto increment use the following:
INSERT INTO list (code, name, address,)
SELECT 'ABC', 'ABC name', 'Johannesburg'
FROM list
WHERE NOT EXISTS(
SELECT code, name, address
FROM list
WHERE code = 'ABC'
AND name = 'ABC name'
AND address = 'Johannesburg'
)
LIMIT 1
That will do the job. However, one important point is that the table cannot be empty it must have at least one field so that is unfortunately a flaw.
Who thought a Mysql insert if not Exists would be this (easy) hard.
Read this question on stack: prevent autoincrement on MYSQL duplicate insert
Original article found on this blog: Mr.TimRosenblatt
If someone has a better idea please contact me: ME or add a comment.
Thanks
this method is prone to race condition. the select and insert are not executed as one atomic operation but as 2 individual operations. so between the select and insert, some other process might do an insert
It shouldn’t be a problem if you have a single connection. Like a script that runs sequentially. If this is not the case you can always use transactions
Even a transaction with Repeatable Read isolation wont help here. Repeatable Read doesnt acquire range locks, so another process can do an insert between SELECT and INSERT