应用自动增量时，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

我正在阅读的那个文件似乎是这样说的:

在这种情况下(当 AUTO_INCREMENT 列是多列索引的一部分时)，如果您删除任何组中具有最大 AUTO_INCREMENT 值的行，则会重用 AUTO_INCREMENT 值."

我真的不明白那里在说什么.值不是应该自动重用吗?

提前致谢...

解决方案

InnoDB 在您重新启动数据库时重置 auto_increment 字段.

当 InnoDB 重新启动时，它会在列中找到最高值，然后从那里开始.

这不会发生在 MyISAM 中，因为它缓存了最后增加的 id.

更新

此功能/错误自 2003 年以来一直存在，可能会导致严重的问题.举个例子，

1. 表 t1 有一个 auto-inc 主键.

2. 表 t2 在 t1 中有一个主键列，没有外键约束".换句话说，当 t1 中的一行被删除时，t2 中的相应行就会被孤立.

3. 正如我们所知，在 InnoDB 重启后，可以重新发布 id .因此，t2 中的孤立行可能会错误地链接到 t1 中的新行.

<块引用>

这个错误终于在 MySQL 8.0.0 中得到修复 WL#6204 - InnoDBautoinc 列的持久最大值.

InnoDB 将跟踪最大值并在重启时保留那个最大值并从那里开始.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

That document I'm reading seems to say something like:

"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?

Thanks in advance...

解决方案

InnoDB resets the auto_increment field when you restart the database.

When InnoDB restarts, it finds the highest value in the column and then starts from there.

This won't happen in MyISAM because it caches the last incremented id.

Update

This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,

1. Table t1 has an auto-inc primary key.

2. 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.

3. 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.

This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB persistent max value for autoinc columns.

InnoDB will keep track of the maximum value and on restart preserve that max value and start from there.

这篇关于应用自动增量时，MySQL 会重用已删除的 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！