为什么 MySQL 自动增量会在插入失败时增加？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2787910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does MySQL autoincrement increase on failed inserts?
提问by Sorcy
A co-worker just made me aware of a very strange MySQL behavior.
一位同事刚刚让我意识到一个非常奇怪的 MySQL 行为。
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.
假设您有一个带有 auto_increment 字段和另一个设置为唯一字段的表（例如用户名字段）。当尝试使用表中已有的用户名插入一行时，插入失败，正如预期的那样。然而，当您在多次尝试失败后插入有效的新条目时，可以看出 auto_increment 值会增加。
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
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:
虽然这本身不是一个大问题，但通过用失败的插入请求淹没表来杀死表似乎是一个非常愚蠢的攻击媒介，正如 MySQL 参考手册所述：
"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."
“如果 [...] 值变得大于可以存储在指定整数类型中的最大整数，则未定义自动递增机制的行为。”
Is this expected behavior?
这是预期的行为吗？
采纳答案by Quassnoi
InnoDBis a transactional engine.
InnoDB是一个事务引擎。
This means that in the following scenario:
这意味着在以下场景中：
Session Ainserts record1Session Binserts record2Session Arolls back
Session A插入记录1Session B插入记录2Session A回滚
, there is either a possibility of a gap or session Bwould lock until the session Acommitted or rolled back.
, 要么存在间隙，要么session B会锁定，直到session A提交或回滚。
InnoDBdesigners (as most of the other transactional engine designers) chose to allow gaps.
InnoDB设计者（与大多数其他事务引擎设计者一样）选择允许间隙。
From the documentation:
从文档：
When accessing the auto-increment counter,
InnoDBuses a special table-levelAUTO-INClock that it keeps to the end of the currentSQLstatement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing anAUTO_INCREMENTcolumn…
InnoDBuses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted,InnoDBreinitializes the counter for each table for the firstINSERTto the table, as described earlier.
当访问自动递增计数器时，
InnoDB使用一个特殊的表级AUTO-INC锁，它一直保持到当前SQL语句的末尾，而不是事务的末尾。引入了特殊的锁释放策略以提高插入到包含AUTO_INCREMENT列的表的并发性…
InnoDB只要服务器运行，就使用内存中自动递增计数器。当服务器停止并重新启动时，InnoDB为第一个表重新初始化每个表的计数器，INSERT如前所述。
If you are afraid of the idcolumn wrapping around, make it BIGINT(8-byte long).
如果您害怕id列环绕，请将其设为BIGINT（8 字节长）。
回答by DRapp
Without knowing the exact internals, I would say yes, the auto-increment SHOULD allow for skipped values do to failure inserts. Lets say you are doing a banking transaction, or other where the entire transaction and multiple records go as an all-or-nothing. If you try your insert, get an ID, then stamp all subsequent details with that transaction ID and insert the detail records, you need to ensure your qualified uniqueness. If you have multiple people slamming the database, they too will need to ensure they get their own transaction ID as to not conflict with yours when their transaction gets committed. If something fails on the first transaction, no harm done, and no dangling elements downstream.
在不知道确切的内部结构的情况下，我会说是的，自动增量应该允许跳过值对失败插入执行。假设您正在进行银行交易，或者其他交易和多条记录要么全有要么全无。如果您尝试插入，获取 ID，然后使用该交易 ID 标记所有后续详细信息并插入详细记录，则需要确保您的合格唯一性。如果您有多个人抨击数据库，他们也需要确保他们获得自己的事务 ID，以免在提交事务时与您的事务发生冲突。如果第一次交易失败，不会造成任何伤害，下游也没有悬空元素。
回答by DavidA
Old post, but this may help people, You may have to set innodb_autoinc_lock_modeto 0 or 2.
旧帖子，但这可能对人们有所帮助，您可能必须将innodb_autoinc_lock_mode设置为0 或 2。
System variables that take a numeric value can be specified as --var_name=valueon the command line or as var_name=valuein option files.
可以--var_name=value在命令行或var_name=value选项文件中指定采用数值的系统变量。
Command-Line parameter format:
命令行参数格式：
--innodb-autoinc-lock-mode=0
OROpen your mysql.ini and add following line :
或打开您的 mysql.ini 并添加以下行：
innodb_autoinc_lock_mode=0
回答by ThinkkSo
I know that this is an old article but since I also couldn't find the right answer, I actually found a way to do this. You have to wrap your query within an if statement. Its usually insert query or insert and on duplicate querys that mess up the organized auto increment order so for regular inserts use:
我知道这是一篇旧文章，但由于我也找不到正确的答案，我实际上找到了一种方法来做到这一点。您必须将查询包装在 if 语句中。它通常插入查询或插入和重复查询会弄乱有组织的自动增量顺序，因此对于常规插入使用：
$check_email_address = //select query here\
if ( $check_email_address == false ) {
your query inside of here
}
and instead of INSERT AND ON DUPLICATEuse a UPDATE SET WHERE QUERYin or outside an if statement doesn't matter and a REPLACE INTO QUERYalso does seem to work
而不是INSERT AND ON DUPLICATE在 if 语句内部或外部使用UPDATE SET WHERE QUERY并不重要，并且REPLACE INTO QUERY似乎也有效
