When you insert multiple rows in mysql database and want to skip duplicate entries. Otherwise you always get mysql failure message of duplicate key.
To overcome this issue Mysql provide two type of Query for insert data. First one is INSERT IGNORE and other is ON DUPLICATE KEY UPDATE.
Lets check it how we can use these Mysql Query:
Insert Ignore Mysql Query :
When you use Insert Ignore query instead of Insert command while inserting too many rows data. If any duplicate row entry exist, than its silently discard the MYSQL function without show any error.
Syntax:
|
1 |
INSERT IGNORE INTO 'Table Name' ('column name1', 'column name1' ) VALUES ('Value 1','Value 2') |
On Duplicate Key Update Mysql Query :
Using this mysql query while insert rows data in database table, find any duplicate entry row, Update that row with the data. Basically this query is when you want to change the data on result of a duplicate key.
It will insert sql statement data when get duplicate key in table without show any error.
Syntax:
|
1 |
INSERT INTO 'table name' ('id_name', 'column_name') VALUES (1, 1) ON DUPLICATE KEY UPDATE col = 1 |
Using both above MySQL query you can prevent Duplicate Key error. As per performance basis i recommended to use INSERT IGNORE way while insert too many rows data. Its execute faster and omit the duplicate entry without any replacement in data.
