表中存在唯一索引時，有時候?yàn)榱吮苊獠迦胫貜?fù)數(shù)據(jù)，我們會使用MySQL的
INSERT IGNORE語法，這樣如果唯一索引有沖突就會自動忽略。 看起來很完美的一個解決辦法，但是背后隱藏著一個坑，最后不得不放棄。
不僅僅是DUPLICATE KEY
INSERT IGNORE不僅僅會忽略DUPLICATE KEY錯誤，也會忽略非空錯誤
- 數(shù)據(jù)庫有表
student如下：
mysql> show create table student;
+---------+----------------------------+
| Table | Create Table |
+---------+----------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`class_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------+
1 row in set (0.00 sec)
- 因?yàn)?code>class_id非空，所以插入失敗
mysql> insert into student(name) values("tenmao");
ERROR 1364 (HY000): Field 'class_id' doesn't have a default value
- 使用
ignore后插入失敗，class_id被設(shè)置為int的默認(rèn)值0
mysql> insert ignore into student(name) values("tenmao");
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1364 | Field 'class_id' doesn't have a default value |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | tenmao | 0 |
+----+--------+----------+
1 row in set (0.00 sec)
-
varchar會被設(shè)置為默認(rèn)值''（空字符串）
mysql> insert ignore into student(class_id) values(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | tenmao | 0 |
| 2 | | 1 |
+----+--------+----------+
2 rows in set (0.00 sec)
字段
NOT NULL是為了防止插入時漏掉這個字段，但是使用INSERT IGNORE后，會自動設(shè)置一個沒有用的默認(rèn)值，導(dǎo)致系統(tǒng)數(shù)據(jù)錯誤。 那怎么辦？
ON DUPLICATE KEY
解決辦法也很簡單，使用ON DUPLICATE KEY
mysql> insert into student(id, name, class_id) values(1, '十毛', 2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into student(id, name, class_id) values(1, '十毛', 2) on duplicate key update id=id;
Query OK, 0 rows affected (0.00 sec)
