MySql upsert 和 auto-increment 导致 gaps

Question

我有一个带有自动递增主键的 MySql 表，似乎所有各种更新插入方法(INSERT IGNORE 和 ON DUPLICATE KEY UPDATE)都受到自动递增字段的影响增量，即使更新了一行但未插入。这意味着在表格中引入了间隙，我认为这是不可取的。

所以问题是:如果更新插入实际上只是更新行，是否有任何方法可以在具有自动递增字段的表中更新插入记录而不自动递增该字段。在我看来，这是 upsert 应该的行为方式，但它似乎并非如此。

Answer 1

这个“问题”只存在于 InnoDB 中。

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

来自docs :

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

…

InnoDB initializes but does not increment the value and stores it for use by later inserts

…

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

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