在INSERT語句中，使用IGNORE關鍵字時，在INSERT語句執行過程中發生的錯誤將會被忽略。例如，當向建有UNIQUE索引或主鍵的欄位插入重複欄位時，會導致duplicate-key錯誤，執行的語句會失敗。當帶有IGNORE關鍵字時，這個錯誤會被忽略，只會產生警告。
當向分割槽表插入資料時，IGNORE關鍵字具有類似的效果。當沒有要插入的分割槽時，MySQL會忽略錯誤。
當資料轉換引發報錯時，可以透過IGNORE關鍵字來忽略錯誤，這時MySQL會產生警告而不退出語句的執行，無效的數值會被調整成接近的數值並被插入。

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)
Records: 5 Duplicates: 2 Warnings: 1

MySQL INSERT IGNORE語句的使用

相關文章