INSERT ON DUPLICATE KEY UPDATE 几个要注意的问题

1. rows affected 是多少？

根据官方文档：

For INSERT … ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

总结下，insert 1，update 2，update 的值和原来的值一样 0。但如果通过 JDBC 调用，最后一种情况也会返回1，这是因为客户端连接时如果设置了 CLIENT_FOUND_ROWS 标志，会用 rows found 代替 rows affected 当做返回值，而JDBC默认是会设置该标志的。在 JDBC 连接字符串中指定 useAffectedRows=true 可以取消这个flag。

useAffectedRows
Don’t set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on “found” rows vs. “affected rows” for DML statements), but does cause “correct” update counts from “INSERT … ON DUPLICATE KEY UPDATE” statements to be returned by the server.
Default: false
Since version: 5.1.7

2. 执行后，select LAST_INSERT_ID() 返回什么？

上官方文档：

If a table contains an AUTO_INCREMENT column and INSERT … UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful.

insert：返回刚刚生成的自增ID，update：返回值无意义，MyBatis实测返回0而不是null，因此DAO这么写：

@Insert(" INSERT INTO ... ON DUPLICATE KEY UPDATE ...")
@SelectKey(statement = "SELECT LAST_INSERT_ID() AS id", keyProperty = "id", before = false, resultType = Long.class)
int insert(Entity obj);

然后调用方试图通过观察 id 是否为null，推测是发生了更新还是插入，这是行不通的。

要获得插入或者更新的ID，可以在SQL执行完后，利用唯一索引再把id查出来：

@Insert(" INSERT INTO t(v) VALUES(#{v}) ON DUPLICATE KEY UPDATE v=#{v}")
@SelectKey(statement = "SELECT id FROM t WHERE v=#{v}", keyProperty = "id", before = false, resultType = Long.class)
int insert(Entity obj);

3. AUTO_INCREMENT 字段的GAP

根据官方文档的描述，INSERT ON DUPLICATE KEY UPDATE 属于 Mixed-mode inserts，只分析SQL无法知道需要几个自增id，在 innodb_autoinc_lock_mode 为 1（默认值）或 2 时，这类 insert 不走 AUTO_INC 表级锁，而是用一个轻量级的 mutex，一次性分配最坏情况下所需要的自增id，至于用不用的完就不管了。上锁的只是分配id的过程，不会锁整个sql语句，这样一来提高了并发度，但代价是和后续insert分配的自增id之间可能存在空洞。具体到INSERT ON DUPLICATE KEY UPDATE，即使最终执行了 update，自增ID也是会增长的，不过这一般不是问题。