自动增加主要的计数差距 [英] 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.我在插入记录时使用了插入忽略，以便当我尝试在唯一列中插入重复项时不会出现错误.

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屋！