絮絮叨叨
- mysql插入数据时,出现
Duplicate entry 'xxxxx' for key 'yyyy'
的问题。 - 问题的可能原因:
-
插入的数据中,主键的值已经存在,违反了主键的唯一性。例如,将id设置为自增的主键,插入数据的id已经存在,会提示如下信息:
ERROR 1062 (23000): Duplicate entry '2093' for key 'PRIMARY'
-
插入的数据中,唯一索引的值已经存在,违反其唯一性。例如,将用户名和资源组名设置为复合的唯一索引,插入数据的用户名和资源组名已经存在,会提示如下信息:
ERROR 1062 (23000): Duplicate entry '11120066-ad-resource' for key 'uniq_index'
-
- 第二种原因,非常容易出现。因为,代码开发中往往存在这样的需求:该用户的信息之前被标记为了无效,后来由于业务需求,又需要被重新添加。
- 笨的程序员,可能有一些笨办法去解决:
- 先检查唯一索引是否已经存在,如果存在,将该条数据标记为有效
- 先检查唯一索引是否已经存在,如果存在,将该条数据删除后,再插入新的数据。
- 这些之所以说是笨办法,因为需要多次访问数据库,才能解决问题,代码性能并不是很好
解决办法一:ON DUPLICATE KEY UPDATE
-
巧妙使用MySQL的特有语法
ON DUPLICATE KEY UPDATE
,对insert语句做如下更新:insert into authority values(0, 'user1', '03eff6d4', 'NORMAL', 'ad-resource', '2021-01-10 14:34:32', 'user21', 1) on duplicate key update role='NORMAL', update_time='2021-01-10 14:34:32', operator='user21',is_active=1;
-
将遇到
duplicate entry
时,想要更新的字段放到ON DUPLICATE KEY UPDATE
之后,单独指出。 -
这样,当MySQL检查到
duplicate entry
时,就会自动执行后面的更新语句。- 如果没有出现
duplicate entry
,则该SQL语句实现的是插入操作,影响1条记录 - 如果出现
duplicate entry
,则该条SQL语句实现的是更新操作,影响了2条记录
- 如果没有出现
-
注意:
- mysql非常的智能,如果批量插入语句,它只会对发生
duplicate entry
的记录进行update。 - 同时,这也带来一个隐患,只要出现
duplicate entry
,所有对应记录的之都会被更新为相同的值。
- mysql非常的智能,如果批量插入语句,它只会对发生
解决办法二:replace into
-
replace into
是insert into
的增强版:- 如果插入的数据不重复,执行的是
insert into
操作,影响1条记录 - 如果插入的数据重复,执行的是
update
操作,影响2条记录:先删除旧的数据,再插入新的数据。
- 如果插入的数据不重复,执行的是
-
伪代码解释
replace into
语法:关于update的情况,应该是先删除已有的数据,再插入新数据if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else // 个人倾向于,还有delete操作 update t set update_time = getdate() where id = 1
-
replace into
示例:replace into druid_authority values(0,'user4','03eff6d4','NORMAL','test-creator','2021-01-10 14:34:32','user5',1);
-
缺点: 如果定义了自增的id,
replace into
执行update
语法后,数据的id会发生变化,成为最新的id。