insert on duplicate key update多个唯一索引踩坑

insert on duplicate key update多个唯一索引踩坑

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

这里需要注意的是：如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

如果该表中，由多个唯一索引，需要特别注意，出现重复时则该语句只能更新其中一行记录

举例：表test_tbl有如下数据，但是有两个唯一索引：UNIQUE KEY a (a) USING BTREE，UNIQUE KEY b (b) USING BTREE

id	a	b	c
1	aaa	aaa	test
2	bbbb	bbbb	test

执行insert into test_tbl(a, b) value(‘aaa’, ‘bbbb’) on duplicate key update c= ‘demo’;

结果是：

id	a	b	c
1	aaa	aaa	demo
2	bbbb	bbbb	test

可以看出，id=1和id=2两行都匹配到了，但是只更新了一行数据，受影响的行数为2。原因就是更新时会按照键的顺序来更新。（按索引被添加到表上的顺序排序）。因此在主从复制时会存在问题。

官方文档描述：MySQL Bugs: #58637: Mark INSERT…ON DUPLICATE KEY UPDATE unsafe when there is more than one key

CREATE TABLE t1 (a INT, b INT UNIQUE KEY) ENGINE = InnoDB;
ALTER TABLE t1 ADD UNIQUE KEY(a);
--sync_slave_with_master
# Same table definition, only given in one statement instead of two
DROP TABLE t1;
CREATE TABLE t1 (a INT UNIQUE KEY, b INT UNIQUE KEY) ENGINE = InnoDB;
--connection master

INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (1, 2)
    ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
SELECT * FROM t1;
--sync_slave_with_master
SELECT * FROM t1;

Suggested fix:
Mark INSERT ... ON DUPLICATE KEY UPDATE unsafe when there are more than one indexes.