为什么失败插入时MySQL自动增加? [英] Why does MySQL autoincrement increase on failed inserts?

问题描述

一位同事让我意识到了一个非常奇怪的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.

来自 文档 :

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

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

...

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

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

如果您怕id列环绕，请将其设为BIGINT(8字节长).

If you are afraid of the id column wrapping around, make it BIGINT (8-byte long).

这篇关于为什么失败插入时MySQL自动增加?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！