自动增量主要在计数中留下空白 [英] auto increment primary leaving gaps in counting

问题描述

我有一个带有自动增量主键的表.该表旨在存储数百万条记录，我现在不需要删除任何内容.问题是，当插入新行时，由于某些错误，自增键在自增 ID 中留下了一些空白.例如，在 5 之后，下一个 id 是 8，留下了 6 和 7 的空白. 结果是当我计算行数时，结果为 28000，但最大 id 为 58000.可能是什么原因?我没有删除任何东西.我该如何解决这个问题.

I have got a table with auto increment primary key. This table is meant to store millions of records and I don't need to delete anything for now. The problem is, when new rows are getting inserted, because of some error, the auto increment key is leaving some gaps in the auto increment ids.. For example, after 5, the next id is 8, leaving the gap of 6 and 7. Result of this is when I count the rows, it results 28000, but the max id is 58000. What can be the reason? I am not deleting anything. And how can I fix this issue.

附言我在插入记录时使用了插入忽略，以便在我尝试在唯一列中插入重复条目时不会出错.

P.S. I am using insert ignore while inserting records so that it doesn't give error when I try to insert duplicate entry in unique column.

推荐答案

这是设计使然，总会发生.

This is by design and will always happen.

为什么?

让我们来看看 2 个正在执行 INSERT 的重叠事务

Let's take 2 overlapping transaction that are doing INSERTs

• 事务 1 执行 INSERT，获取值(假设为 42)，执行更多工作
• 事务 2 执行 INSERT，获得值 43，执行更多工作

然后

• 事务 1 失败.回滚.42 保持未使用
• 事务 2 以 43 完成

如果保证连续的值，那么每笔交易都必须一个接一个地发生.可扩展性不强.

If consecutive values were guaranteed, every transaction would have to happen one after the other. Not very scalable.

另见插入的记录是否总是接收连续的标识值 (SQL Server 但同样的原则适用)

Also see Do Inserted Records Always Receive Contiguous Identity Values (SQL Server but same principle applies)

这篇关于自动增量主要在计数中留下空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！