如何处理MySQL中auto_increment ID列的碎片

Question

我有一个带有 auto_increment 字段的表，有时行会被删除，因此 auto_increment 会留下间隙。有没有办法避免这种情况，或者如果没有，至少如何编写一个 SQL 查询来：

1. 将 auto_increment 值更改为 max(当前值) + 1
2. 返回新的 auto_increment 值？

我知道如何编写 1 和 2 部分，但是我可以将它们放在同一个查询中吗？

如果不可能：

如何“选择”（返回）auto_increment 值或auto_increment 值+ 1？

Answer 1

重新编号会造成混乱。现有报告将引用记录 99，但如果系统重新编号，它可能会将该记录重新编号为 98，现在所有报告（和填充的 UI）都是错误的。一旦分配了唯一的 ID，它就必须保持固定。

将 ID 字段用于除简单唯一编号之外的任何内容都会出现问题。 “无间隙”的要求与能够删除的要求根本不一致。也许您可以将记录标记为已删除，而不是删除它们。那么就真的没有间隙了。假设您正在生成编号发票：您将拥有带有该编号的零值取消发票，而不是将其删除。

Answer 2

即使在 autoinc 表中，也有一种方法可以手动插入 id。您所要做的就是识别丢失的 ID。

但是，不要这样做。如果您的数据库是关系数据库，这可能会非常危险。删除的 id 可能已被其他地方使用。删除后，它不会造成太大问题，也许会孤立一条记录。如果替换，它将带来一个巨大的问题，因为会出现错误的关系。

考虑一下我有一个汽车表和一个人员表

car
carid
ownerid
name

person
personid
name

并且有一些简单的数据

car
1 1 Van
2 1 Truck
3 2 Car
4 3 Ferrari
5 4 Pinto

person
1 Mike
2 Joe
3 John
4 Steve

，现在我删除了约翰。

person
1 Mike
2 Joe
4 Steve

如果我在表中添加一个新人吉姆，并且他获得了一个填补空白的 id，那么他最终将获得 id 3

1 Mike
2 Joe
3 Jim
4 Steve

，并且根据关系，他将成为法拉利的所有者。

Answer 3

我总体上同意本页（以及重复问题）上的智者的建议，建议不要重复使用自动增量ID。这是很好的建议，但我不认为由我们来决定提出问题的正确或错误，让我们假设开发人员知道他们想做什么以及为什么。

答案是，正如 Travis J 所提到的，您可以通过在插入语句中包含 id 列并分配所需的特定值来重用自动增量 id。

这里有一点需要注意：MySQL 本身（至少 5.6 InnoDB）将在以下情况下重用自动增量 ID：

• 删除任何具有最高自动增量 id 的行
• 停止并启动 MySQL
• 插入新行

插入的行将有一个 id，计算为 max(id)+1，它不会从已删除的 id 继续。

Answer 4

正如 djna 在她/他的回答中所说，以这种方式更改数据库表不是一个好的做法，如果您选择了正确的方案和数据类型，也没有必要这样做。顺便说一句，根据你的问题的一部分：

我有一个带有 auto_increment 字段的表，有时行会被删除，因此 auto_increment 会留下间隙。有什么办法可以避免这种情况吗？

1. 如果您的表在其自动增量列中存在太多间隙，可能是由于进行了大量测试 INSERT 查询而导致的，
2. 并且如果您想通过删除间隙来防止出现压倒性的 id 值
3. ，而且如果 >id 列只是一个计数器，与数据库中的任何其他列无关

，这可能是您（或任何其他正在寻找此类内容的人）正在寻找的内容：

解决方案

1. 删除原始 id 列
2. 使用 auto_increment 再次添加它，

但如果您只想将 auto_increment 重置为第一个可用值：

ALTER TABLE `table_name` AUTO_INCREMENT=1

Answer 5

不确定这是否有帮助，但在 SQL Server 中，您可以重新设置身份字段。 mySql 中似乎有一个 ALTER TABLE 语句来实现这一点。例如，将id设置为继续在59446。

ALTER TABLE table_name AUTO_INCREMENT = 59446; 

我认为您应该能够组合查询来获取auto_increment字段的最大值，然后使用alter table根据需要进行更新。