Geek Answers Handbook

Disable MySQL double insert

I have a mysql table with auto increment key. I need a way to only insert into this table if the table does not contain the row that I am inserting. INSERT IGNORE and INSERT ON DUPLICATE KEY UPDATE will not work because an auto-incrementing key will cause the row to always be different. How can I insert the next row only if there is no duplicate row? Thanks.

INSERT INTO TableName (column1, column2, column3) VALUES ("value1", "value2", "value3");
+4
source share
2 answers

Set a UNIQUE on any column that must be unique, or a combination of columns.

For instance:

 ALTER TABLE `TableName` ADD UNIQUE `constrain_me` (`column1`, `column2`);

If you want to ignore any error that a duplicate insert may indicate, use INSERT IGNORE , although you might want to catch this error rather than cleaning it under the carpet.

+5
source

You can create unique indexes in fields that you do not want to duplicate.

 CREATE UNIQUE INDEX MyIndex ON column1

Thus, if a duplicate value is added, the request will fail. It is also worth noting that this method allows you to add NULL values ​​(i.e. two rows with NULL values ​​column1 will not be considered duplicates)

+1
source

More articles:


All Articles