SQL如何避免重复插入主键

已知条件：MySQL数据库 
存在一张表，表名为teacher，主键为id，表中有4行数据

select * from teacher;

---

要求：要求使用数据库插入语句往表中插入数据，若需要插入表中的数据（或者数据的主键）如果已经在表中存在，那么要求SQL在执行的时候不能报错。

例如：插入一行id=3，name=丁老师，salary=5000的记录，

insert into teacher(id,name,salary) values(3,'丁老师',5000);

因为id=3的主键在表中已经存在，所以强行执行SQL插入的话程序会报错

---

 

方法（1）：使用 replace 代替 insert

replace into teacher(id,name,salary) values(3,'丁老师',5000);

• 在MySQL中 replace 的执行效果和 insert 的效果是一样的，不同的是replace 语句会把后来插入表中的记录替换掉已经存在于表中的记录，此法不推荐使用。 
  因为若此时再插入一条语句 
  replace into teacher(id,name,salary) values(3,'苏老师',9000); 
  那么这条语句的内容会把先前插入表中的内容id=3，name=丁老师，salary=5000的记录替换掉

  ---

  方法（2）：结合select的判断式insert 语句

       灵感代码：

insert into tableA(x,y,z) select * from tableB

      模板代码：

insert into teacher(id,name,salary) select ?,?,? from teacher where not exists(select * from teacher where id=?) limit 1;

     或者

insert into teacher(id,name,salary) select distinct ?,?,? from teacher where not exists(select * from teacher where id=?);

例子：插入一行id=3，name=丁老师，salary=5000的记录

insert into teacher(id,name,salary)

select 3,'丁老师',5000 from teacher

where not exists(select * from teacher where id=3) limit 1;

或者

insert into teacher(id,name,salary)

( select 4,'白老师',4000 from teacher

where not exists(select * from teacher where id=4) limit 1);

在上面的SQL语句中：执行的原理解析：
若teacher表中不存在id=3的那条记录，则生成要插入表中的数据并插入表；
若teacher表中存在id=3的那条记录，则不生成要插入表中的数据。

其实程序可以分开看：
① select * from teacher where id=3 若查询有值，则表示真，即存在id=3这条记录，若查询没有值则表示假，即不存在id=3这条记录，

②若果不存在id=3这条记录，那么又因为 not exists 本身表示假，即不存在的意思；假假为真，所以此时程序可以形象的理解为
select 3,'丁老师',5000 from teacher where not exists (false) limit 1;
等价于
select 3,'丁老师',5000 from teacher where true limit 1;

③所以程序就会生成一行为 3,'丁老师',5000的记录

④最后生成的数据就会插入表中

方法（3）：ignore

插入时检索主键列表，如存在相同主键记录，不更改原纪录，只插入新的记录

INSERT IGNORE INTO

ignore关键字所修饰的SQL语句执行后，在遇到主键冲突时会返回一个0，代表并没有插入此条数据。如果主键是由后台生成的（如uuid），我们可以通过判断这个返回值是否为0来判断主键是否有冲突，从而重新生成新的主键key。

这是此ignore关键字比较常用的一种用法。

方法（4）：ON DUPLICATE KEY UPDATE

先声明一点，ON DUPLICATE KEY UPDATE为Mysql特有语法，这是个坑 
语句的作用，当insert已经存在的记录时，执行Update

用法

什么意思？举个例子： 
user_admin_t表中有一条数据如下

表中的主键为id，现要插入一条数据，id为‘1’，password为‘第一次插入的密码’，正常写法为：

INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 

执行后刷新表数据，我们来看表中内容

此时表中数据增加了一条主键’_id’为‘1’，‘password’为‘第一次插入的密码’的记录，当我们再次执行插入语句时，会发生什么呢？

-- 执行
INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 

[SQL]INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 

[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

Mysql告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE 
INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 
ON DUPLICATE KEY UPDATE 
_id = 'UpId',
password = 'upPassword';

我们再一次执行：

[SQL]INSERT INTO user_admin_t (_id,password) 
VALUES ('1','第一次插入的密码') 
ON DUPLICATE KEY UPDATE 
_id = 'UpId',
password = 'upPassword';
受影响的行: 2
时间: 0.131s

可以看到 受影响的行为2，这是因为将原有的记录修改了，而不是执行插入，看一下表中数据：

原本‘id’为‘1’的记录，改为了‘UpId’，‘password’也变为了‘upPassword’，很好的解决了重复插入问题

扩展

当插入多条数据，其中不只有表中已存在的，还有需要新插入的数据，Mysql会如何执行呢？会不会报错呢？

其实Mysql远比我们想象的强大，他会智能的选择更新还是插入，我们尝试一下：

INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','第一次插入的密码') ,
('2','第二条记录')
ON DUPLICATE KEY UPDATE 
_id = 'UpId',
password = 'upPassword';

运行sql

[SQL]INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','第一次插入的密码') ,
('2','第二条记录')
ON DUPLICATE KEY UPDATE 
_id = 'UpId',
password = 'upPassword';
受影响的行: 3
时间: 0.045s

Mysql执行了一次修改，一次插入，表中数据为：

VALUES修改

那么问题又来了，有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值，如果我想要分别给不同的记录插入不同的值怎么办呢？

INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2')
ON DUPLICATE KEY UPDATE 
password =  VALUES(password);

方法之一可以将后面的修改条件改为VALUES(password)，动态的传入要修改的值，执行以下：

[SQL]INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2')
ON DUPLICATE KEY UPDATE 
password =  VALUES(password);
受影响的行: 4
时间: 0.187s

成功的修改了两条记录，刷新一下表

我们成功的为不同id的password修改成了不同的值

总结

其实修改的方法有很多种，包括SET或用REPLACE，连事务都省的做，ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求，但它是Mysql的特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。