Mysql - insert into on duplicate

1. 背景

场景: 向表admin插入一条数据，如果business_id字段如果有重复的，则is_update置为1

• create_sql

CREATE TABLE `admin` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `business_id` bigint(20) DEFAULT NULL COMMENT '业务id',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '名称',
  `is_update` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否修改 0否 1是',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`),
  UNIQUE KEY `uk_business_id` (`business_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

2. 执行sql

insert into admin(business_id) values (12345) on duplicate key update is_update = 1;

3. 发现问题

表中business_id有12345的重复数据，执行之后发现执行成功，但是该数据的is_update并没有改变。

4. 过程分析

发现因为name字段是唯一索引，并且默认填充为''，所以只插入business_id，表中已经存在name为''的数据就会造成name重复导致插入失败，而这个插入失败并不是business_id字段重复造成的，所以就没有is_update字段的更改。

• 由此分析出

1. unique key的异常级别高于duplicate key的重复判断。执行过程是先确保insert语句可执行，之后再判断duplicate key。
2. insert操作异常反馈的重复字段如果不在sql中，则不会执行后续的update。

5. 解决办法

在执行insert duplicate key语句的时候，尽量使用主键作为判断，主键的重复判断优先级比其他字段的unique key优先级高，所以在执行插入重复的时候能正常触发update。