Insert on duplicated key 踩坑

场景

1. 若你使用了主从Mysql
2. 若你的表有多个唯一索引

那么不建议使用on duplicated key，因为mysql不知道用哪个唯一key做去重，虽然默认选择第一个（按索引被添加到表上的顺序排序），但存在极端情况，如主库按顺序添加索引，而从库一股脑一起所有唯一索引一起添加，那么从库就不知道哪个是第一个索引，去重就会有问题。

Description:
When mysql executes INSERT ON DUPLICATE KEY INSERT, the storage engine checks if the inserted row would generate a duplicate key error. If yes, it returns the existing row to mysql, mysql updates it and sends it back to the storage engine.

When the table has more than one unique or primary key, this statement is sensitive to the order in which the storage engines checks the keys. Depending on this order, the storage engine may determine different rows to mysql, and hence mysql can update different rows.

The order that the storage engine checks keys is not deterministic. For example, InnoDB checks keys in an order that depends on the order in which indexes were added to the table. The first added index is checked first. So if master and slave have added indexes in different orders, then slave may go out of sync.

There are realistic scenarios when this can happen. For example, maybe the slave has not started when master creates a table without indexes, then adds indexes.  Then the slave is bootstrapped using mysqldump. The slave will then add all indexes at once, using a single CREATE TABLE statement.

This bug does not affect REPLACE, since REPLACE removes *all* existing duplicate rows, one by one, until the new row can be inserted without error.

How to repeat:
--source include/have_innodb.inc
--source include/master-slave.inc

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.