为什么 MySQL 自动增量会在插入失败时增加?

Why does MySQL autoincrement increase on failed inserts?(为什么 MySQL 自动增量会在插入失败时增加?)

问题描述

一位同事刚刚让我意识到 MySQL 的一个非常奇怪的行为.

A co-worker just made me aware of a very strange MySQL behavior.

假设您有一个带有 auto_increment 字段和另一个设置为唯一字段的表(例如用户名字段).当尝试使用表中已有的用户名插入一行时，插入失败，正如预期的那样.然而，当您在多次尝试失败后插入有效的新条目时，可以看出 auto_increment 值会增加.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

例如，当我们的最后一个条目看起来像这样......

For example, when our last entry looks like this...

ID: 10
Username: myname

...我们在下一次插入时尝试五个具有相同用户名值的新条目，我们将创建一个新行，如下所示:

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

ID: 16
Username: mynewname

虽然这本身并不是什么大问题，但正如 MySQL 参考手册所述:

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:

如果 [...] 值变得大于可以存储在指定整数类型中的最大整数，则未定义自动递增机制的行为."

"The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type."

这是预期的行为吗?

推荐答案

InnoDB 是一个事务引擎.

这意味着在以下场景中:

This means that in the following scenario:

1. Session A 插入记录 1
2. Session B 插入记录 2
3. Session A 回滚

1. Session A inserts record 1
2. Session B inserts record 2
3. Session A rolls back

，要么存在间隙，要么 session B 会锁定，直到 session A 提交或回滚.

, there is either a possibility of a gap or session B would lock until the session A committed or rolled back.

InnoDB 设计者(与大多数其他事务引擎设计者一样)选择允许间隙.

InnoDB designers (as most of the other transactional engine designers) chose to allow gaps.

来自文档: