为什么失败插入时MySQL自动增加？

Question

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

假设您有一个表，其中包含一个auto_increment字段和另一个设置为唯一的字段（例如，用户名字段）。尝试插入表中已有用户名的行时，插入失败，正如预期的那样。然而，几次尝试失败后插入有效的新条目时，可以看到auto_increment值增加了。

例如，当我们的上一个条目如下所示时…

ID: 10
Username: myname

…并且在下一次插入时尝试使用相同的用户名值尝试五个新条目，我们将创建一个新行，如下所示：

ID: 16
Username: mynewname

尽管这本身并不是什么大问题，但是通过将失败的插入请求充斥到表中杀死表似乎是一个非常愚蠢的攻击手段，如《 MySQL参考手册》所述：

“如果该值变得大于可以以指定整数类型存储的最大整数，则不会定义自动递增机制的行为。”

这是预期的行为吗？

Answer 1

InnoDB 是一个交易引擎。

这意味着在以下情况下：

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

，则存在间隙的可能性，或者session B将锁定直到session A提交或回滚。

InnoDB 设计人员（与大多数其他事务引擎设计人员一样）选择留出空隙。

从 文档中 ：

访问自动增量计数器时，请InnoDB使用特殊的表级AUTO- INC锁，该锁保留在当前SQL语句的末尾，而不是事务的末尾。引入了特殊的锁定释放策略，以提高插入到包含AUTO_INCREMENT列的表中的并发性

…

InnoDB只要服务器运行，就使用内存中自动增量计数器。如前所述，当服务器停止并重新启动服务器后，请InnoDB为每个表的第一个表重新初始化计数器INSERT。

如果您担心id列会缠绕，请使其BIGINT（8字节长）。