应用自动增量功能时，MySQL是否会重用已删除的ID [英] Will MySQL reuse deleted ID's when Auto Increment is applied
问题描述
http://dev.mysql.com/doc/refman/5.0 /en/example-auto-increment.html
我正在阅读的文档似乎是这样的:
That document I'm reading seems to say something like:
在这种情况下(当AUTO_INCREMENT列是多列索引的一部分时)，如果删除任何组中具有最大AUTO_INCREMENT值的行，则将重用AUTO_INCREMENT值."
"In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group."
我真的不明白那里所说的话.这些值不是应该自动重用吗?
I don't really understand what's being said there. Aren't the values supposed to be reused automatically?
预先感谢...
推荐答案
InnoDB在重新启动数据库时会重置auto_increment字段.
InnoDB resets the auto_increment field when you restart the database.
InnoDB重新启动时，它将在列中找到最大值，然后从该列开始.
When InnoDB restarts, it finds the highest value in the column and then starts from there.
在MyISAM中不会发生这种情况，因为它缓存了最后一个递增的ID.
This won't happen in MyISAM because it caches the last incremented id.
更新
此功能/错误自2003年以来一直存在，并且可能导致严重的问题.以下面的示例为例，
This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,
-
表t1具有自动增量主键.
Table t1 has an auto-inc primary key.
表t2在t1的主键列中没有外键"constraint".换句话说，当在t1中删除一行时，t2中的相应行将被孤立.
Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.
众所周知，随着InnoDB重新启动，可以重新发出ID .因此，t2中的孤立行可能会错误地链接到t1中的新行.
As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.
此错误终于在 MySQL 8.0.0 中得到了解决: WL#6204-InnoDB autoinc列的持久最大值 .
This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB persistent max value for autoinc columns.
InnoDB将跟踪最大值并在重新启动时保留 该最大值并从此处开始.
InnoDB will keep track of the maximum value and on restart preserve that max value and start from there.
这篇关于应用自动增量功能时，MySQL是否会重用已删除的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
