On duplicate key do nothing
Three ways. Either IGNORE
duplicate errors
(but don't use that, see note in the end):
INSERT IGNORE
... ; -- without ON DUPLICATE KEY
or try to do a redundant update when there is a duplicate:
INSERT
...
ON DUPLICATE KEY UPDATE
id = id ;
or check for duplicates before inserting:
INSERT INTO requests
(id, ctg, msg, nick, filled, dated, filldate)
SELECT
NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
dual
WHERE NOT EXISTS
( SELECT * FROM requests WHERE (nick, msg) = ('Hermione', 'Help!') )
AND NOT EXISTS
( SELECT * FROM requests WHERE (ctg, msg) = ('urgent', 'Help!') ) ;
A difference between the 3rd way and the first two is that when there are duplicates, the id
will not be incremented. With INSERT IGNORE
and INSERT ... ON DUPLICATE KEY
, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id
.
I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.
*Note: INSERT IGNORE
will ignore all insert related errors, even not null constraint violations, so it is best to avoid it.