自动递增主计数间隔

Question

我有一张主键自动递增的表。这个表是用来存储数百万条记录的，我现在不需要删除任何内容。问题是，当插入新行时，由于某些错误，auto increment键在auto increment id中留下一些间隙。。例如，在5之后，下一个id是8，留下6和7的间隔。结果是当我计算行数时，结果是28000，但最大id是58000。原因可能是什么？我没有删除任何内容。我怎样才能解决这个问题。

另外，我在插入记录时使用insert ignore，这样当我试图在unique列中插入重复条目时不会出错。

Answer 1

您可以创建一个触发器，将自动增量处理为：

CREATE DEFINER=`root`@`localhost` TRIGGER `mytable_before_insert` BEFORE INSERT ON `mytable` FOR EACH ROW 
BEGIN
  SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM mytable);;
END

Answer 2

这是MySQL的存储引擎InnoDB中的一个问题。

当您检查“AUTO_INCREMENT Handling in InnoDB”上的文档时，实际上这不是问题，它基本上说InnoDB在启动时使用一个特殊的表来执行自动增量

它使用的查询类似于

SELECT MAX(ai_col) FROM t FOR UPDATE;

这可以在不影响数据的情况下提高并发性。

不使用MyISAM而不是InnoDB作为存储引擎

Answer 3

这是故意的，而且永远都会发生。

为什么？

让我们取两个正在执行插入的重叠事务

• 事务1执行INSERT，获取值（比如说42），执行更多的工作
• 事务2执行插入，获取值43，执行更多操作

那么

• 事务1失败。滚回去。42个未使用
• 事务2以43结束

如果连续值得到保证，那么每个事务都必须一个接一个地发生。可扩展性不强。

另请参见Do Inserted Records Always Receive Contiguous Identity Values（SQL Server，但适用相同的原则）