即使没有添加记录，INSERT IGNORE 也会增加自动增量计数器? [英] INSERT IGNORE increases auto increment counter even no record is added?
问题描述
在 MySQL 中，我使用 INSERT IGNORE 语句向表中插入行.因为一列是 UNIQUE，所以没有插入一些行(因为它们已经存在).执行该语句后，我注意到自动增量列在行之间缺少一些数字，后来我意识到这是由于行被忽略且未添加而发生的.
In MySQL I used INSERT IGNORE statement to insert rows to table. Because one column is UNIQUE, some rows were not inserted (as they already been there). After execution of that statement I noticed that auto increment column has some missing numbers between rows, which later I realized that happened due to rows that was ignored and not added.
如果没有使用 IGNORE 子句插入行，是否可以设置系统不增加 自动递增计数器?
Is it possible to setup system to not increase auto increment counter if no row is inserted with IGNORE clause?
推荐答案
引用 INSERT 手册页:
如果您使用 IGNORE 关键字，则在执行INSERT 语句被视为警告.例如，没有IGNORE，复制现有 UNIQUE 索引或 PRIMARY KEY 的行表中的值导致重复键错误，语句为中止.使用 IGNORE，该行仍然没有插入，但没有错误发布.
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.
INSERT IGNORE 语法只是一种抑制某些错误消息的方法，当您意识到这些错误可能会发生和/或希望在稍后阶段处理它们时，它会很有帮助.在幕后，你仍然有一个常规的插入，除了它由于违反键而失败.MySQL 需要实际的行值来进行插入，并且 AUTO_INCREMENT 计数器将根据 常规规则:
The INSERT IGNORE syntax is just a way to suppress certain error messages and it's helpful when you are aware that those errors might happen and/or want to handle them at a later stage. Behind the scenes, you still have a regular insert, except that it fails due to a violated key. MySQL needs the actual row values to make an insert and the AUTO_INCREMENT counter will increment according to regular rules:
- 该列的值为 NULL.
- 未设置该列的值.
- 列的值大于计数器.
因此，除非您可以重新考虑您的逻辑(例如，在进行插入之前测试键值是否存在)，否则重置计数器的唯一方法是 ALTER TABLE:
So unless you can rethink your logic (e.g., test whether the key values exist before making the insert), the only way to reset the counter is ALTER TABLE:
ALTER TABLE t2 AUTO_INCREMENT = value;
这篇关于即使没有添加记录，INSERT IGNORE 也会增加自动增量计数器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
