MySQL - Handling Duplicates
※ Download: Mysql insert on duplicate key ignore
You can access the patch from: ChangeSet 1. Bug 11765650, Bug 58637 See also. ON DUPLICATE KEY UPDATE statements, when SELECT returns duplicated values and UPDATE clause tries to assign NULL values to NOT NULL fields.
Edit: Thought I'd add some examples. It returns the column values from the INSERT portion of the statement. Generally, tables or result sets sometimes contain duplicate records. Eliminating Duplicates from a Query Result You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table.
Using INSERT IGNORE with MySQL to prevent duplicate key errors - Although you shouldn't normally attempt to insert a record without first checking if the primary key you want to use has already been used, there may be times when this is required, such as when multiple developers need to update their own copies of a database, and a particular record may already exist in one or other of the databases.
When updating summary tables we typically use ON DUPLICATE KEY UPDATE, a MySQL extension to INSERT statements since version 4. When a new event is processed the daily summary table should also be updated, an operation which is ideally suited to an INSERT ON DUPLICATE KEY UPDATE query. An important thing to keep in mind when using this approach is that the order in which you update your fields is very important. I was wrongly under the impression that the updates took place in one mass-assignment after the entire query had been interpreted by MySQL. Conditions using multiple fields Keeping in mind the importance of the order in which fields are updated, conditions that involve multiple fields are a little trickier. Updates to this table should only be made if the session is newer, or if the session is the same and the event is newer. Again events can be processed out of order and it is possible for multiple sessions to be active at the same time. In certain situations the result of the IF statement will change within a single query as the updates are made, leading to inconsistent data. To make this work it is necessary evaluate the IF condition once, before any updates occur, and to store the result in a that can be referenced in the rest of the query. Our lawyers have asked us to point out that no DBAs were harmed in the making of the post!
Pacerier, it depends on whether your application checks for warnings. If you query data from subscribers table, you will find that only one row was actually inserted and the row that causes the error was not. From the manual: If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as that employs table-level locks locks any partitions of the table in which a partitioning key column is updated. Use the INSERT IGNORE command rather than the INSERT command. After this fix such behaviour becomes legalized. If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. Or, you can use syntax if you want to perform an mysql insert on duplicate key ignore to the record instead when you encounter a duplicate. Seuss'1960 ; Query OK2 rows affected 0.
