Mysql upsert和自动增量导致差距

Question

我有一个带有自动递增主键的MySql表，并且似乎所有各种upsert方法（INSERT IGNORE和ON DUPLICATE KEY
UPDATE）都具有自动递增字段递增甚至递增的功能。如果行已更新但未插入。这意味着将间隙引入表中，我发现这是不希望的。

因此，问题是：是否有任何方法可以在具有自动递增字段的表中向上插入记录， 而无需
自动递增该字段，如果实际上仅由向上插入来更新该行。在我看来，这是upsert的行为方式，但似乎并非如此。

Answer 1

此“问题”仅存在于中InnoDB。

它是设计使然，旨在提高并发性：另一个线程可以使用，AUTO_INCREMENT而不必等待UPSERT操作结果。

从 文档 ：

服务器启动后，对于第一次插入表t，将InnoDB执行以下语句的等效项：

SELECT MAX(ai_col) FROM t FOR UPDATE;

…

InnoDB 初始化但不增加值并将其存储以供以后的插入使用

…

访问自动增量计数器时，请InnoDB使用特殊的表级AUTO- INC锁，该锁保留在当前SQL语句的末尾，而不是事务的末尾。引入了特殊的锁定释放策略，以提高插入到包含AUTO_INCREMENT列的表中的并发性。但是，两个事务不能AUTO- INC同时在同一个表上拥有锁，如果AUTO-INC长时间持有该锁，则会对性能产生影响。对于这样的语句，例如INSERT INTO t1 ... SELECT ... FROM t2将一个表的所有行插入到另一个表，可能就是这种情况。

MyISAM不会表现出这种行为，因为它的AUTO_INCREMENT算法实现方式不同（由于其支持并发的能力有限DML）。