mysql中Duplicate entry ‘xxxxx‘ for key ‘yyyy‘的问题

絮絮叨叨

• mysql插入数据时，出现Duplicate entry 'xxxxx' for key 'yyyy'的问题。
• 问题的可能原因：

  1. 插入的数据中，主键的值已经存在，违反了主键的唯一性。例如，将id设置为自增的主键，插入数据的id已经存在，会提示如下信息：

     ERROR 1062 (23000): Duplicate entry '2093' for key 'PRIMARY'
     

  2. 插入的数据中，唯一索引的值已经存在，违反其唯一性。例如，将用户名和资源组名设置为复合的唯一索引，插入数据的用户名和资源组名已经存在，会提示如下信息：

     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，所有对应记录的之都会被更新为相同的值。

解决办法二：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。