【无标题】

最新推荐文章于 2024-10-13 21:08:42 发布

直待凌云

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量40

点赞数

文章标签： mysql

原文链接：https://novoland.github.io/%E6%95%B0%E6%8D%AE%E5%BA%93/2015/08/17/INSERT%20ON%20DUPLICATE%20KEY%20UPDATE%20%E5%87%A0%E4%B8%AA%E8%A6%81%E6%B3%A8%E6%84%8F%E7%9A%84%E9%97%AE%E9%A2%98.html

版权

文章详细讨论了MySQL的INSERTONDUPLICATEKEYUPDATE语句的使用，包括受影响的行数（insert1,update2,update值不变0），在JDBC中的行为，以及LAST_INSERT_ID()在插入和更新操作后的返回值。还提到了AUTO_INCREMENT字段在并发情况下的行为，特别是可能产生的ID空洞问题。对于如何获取插入或更新后的ID，文章提供了一种解决方案。

摘要由CSDN通过智能技术生成

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也是会增长的，不过这一般不是问题