mysql ignore-columns_坑 - 当insert ignore遇到not null

表中存在唯一索引时，有时候为了避免插入重复数据，我们会使用MySQL的INSERT IGNORE语法，这样如果唯一索引有冲突就会自动忽略。 看起来很完美的一个解决办法，但是背后隐藏着一个坑，最后不得不放弃。

不仅仅是DUPLICATE KEY

INSERT IGNORE不仅仅会忽略DUPLICATE KEY错误，也会忽略非空错误

数据库有表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)

因为class_id非空，所以插入失败

mysql> insert into student(name) values("tenmao");

ERROR 1364 (HY000): Field 'class_id' doesn't have a default value

使用ignore后插入失败，class_id被设置为int的默认值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会被设置为默认值''(空字符串)

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后，会自动设置一个没有用的默认值，导致系统数据错误。 那怎么办？

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)

参考