坑 - 当insert ignore遇到not null
坑 - 当insert ignore遇到not null
十毛
发布于 2019-03-27 15:16:57
发布于 2019-03-27 15:16:57
表中存在唯一索引时，有时候为了避免插入重复数据，我们会使用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)参考
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019.03.10 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。