How to use MySQL INSERT IGNORE statement
How to use MySQL INSERT IGNORE statement, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.
When the IGNORE keyword is used in an INSERT statement, errors that occur during the execution of the INSERT statement are ignored. For example, inserting duplicate fields into fields with UNIQUE indexes or primary keys results in duplicate-key errors and the executed statement fails. When the IGNORE keyword is present, this error is ignored and only a warning is generated.
The IGNORE keyword has a similar effect when inserting data into a partitioned table. MySQL ignores errors when there are no partitions to insert.
When data conversion causes an error, the IGNORE keyword can be used to ignore the error, MySQL will generate a warning without exiting the execution of the statement, invalid values will be adjusted to the nearest value and inserted.
mysql> desc t10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | 0 | |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t10;
+----+--------+
| id | name |
+----+--------+
| 10 | Neo |
| 20 | Lucy |
| 30 | Lily |
| 50 | Dingyi |
| 60 | Jones |
+----+--------+
5 rows in set (0.00 sec)
mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | 0 | |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t7;
+----+--------+
| id | name |
+----+--------+
| 50 | Dingyi |
| 60 | Jones |
+----+--------+
2 rows in set (0.00 sec)
mysql> insert into t7 select * from t10;
ERROR 1062 (23000): Duplicate entry '50' for key 'PRIMARY'
mysql> select * from t7;
+----+--------+
| id | name |
+----+--------+
| 50 | Dingyi |
| 60 | Jones |
+----+--------+
2 rows in set (0.00 sec)
mysql> select * from t10;
+----+--------+
| id | name |
+----+--------+
| 10 | Neo |
| 20 | Lucy |
| 30 | Lily |
| 50 | Dingyi |
| 60 | Jones |
+----+--------+
5 rows in set (0.00 sec)
mysql> insert ignore into t7 select * from t10;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.
