为什么有时在自动增量列的值中存在多个缺口之一?

问题描述

我有一个这样的表:

// colors
+----+-------+
| id | color |
+----+-------+
| 1  | red   |
| 2  | blue  |
+----+-------+
-- id column is auto increment (PK)

当我在该表中插入一些新值时，有时id列中会有一些空白.像这样:

When I insert some new values into that table, sometimes there is some gaps in the id column. Something like this:

INSERT INTO colors (color)
            SELECT  'gray'
              UNION ALL
            SELECT  'black'
              UNION ALL
            SELECT  'green'
              UNION ALL
            SELECT  'pink';

/* output:
+----+-------+
| id | color |
+----+-------+
| 1  | red   |
| 2  | blue  |
| 6  | gray  | -- expected id is 3, but it is 6 
| 7  | black |
| 8  | green |
| 9  | pink  |
+----+-------+
*/

在 SQL小提琴和 RexTester

怎么了?为什么有时id的值超出正常顺序?我该如何预防?

A repro of this on SQL Fiddle and RexTester

Well what's wrong? Why sometimes the value of id goes out of the normal order? How can I prevent it?

我已经创建了这样的表:

I've created the table like this:

CREATE TABLE colors (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
color VARCHAR(30) NOT NULL
)

当我在两个不同的步骤中两次粘贴此查询时:

When I paste this query twice in two different steps:

INSERT INTO colors (color)
            SELECT  'gray'
              UNION ALL
            SELECT  'black'
              UNION ALL
            SELECT  'green'
              UNION ALL
            SELECT  'pink';

结果如下:

如您所见，id列的顺序不是连续的.为什么?

As you see the order of id column isn't continuous. Why?

注意1:引擎是 InnoDB .注2::这是下一行的自动递增数量:

Note1: Engine is InnoDB.Note2: Here is the number of auto increment for next row:

推荐答案

似乎与INSERT语句的形成方式有关.请参见此小提琴，该示例与您的示例略有不同.在这种情况下，您不会留有空隙(但是，如果您追加更多插入，则可能会... ... 实际上，确实如此.)

It appears to have something to do with how your INSERT statements are formed. See this fiddle, which is slightly modified from your example. In this case, you don't get gaps (but you probably will if you append more inserts... in fact, it does.).

经过多一点的挖掘，我发现如果使用MyISAM引擎(与InnoDB相对)，您将不会感到空白.因此，在InnoDB中也许是缺陷或好奇的设计选择...?

After a tiny bit more digging, I found that you don't get gaps if you use the MyISAM engine (as opposed to InnoDB). So, perhaps it is a flaw or curious design choice in InnoDB...?

进一步的挖掘表明，针对InnoDB提交的此错误存储引擎.它与原始问题的用例非常匹配.没有提供对该错误的解决方案，但是似乎一种解决方案是在mysqld启动之前将my.cnf文件中的innodb_autoinc_lock_mode设置为0.

EDIT 2: Further digging has revealed this bug, filed against the InnoDB storage engine. It matches the original question's use-case very closely. No resolution to the bug has been provided, however it appears that one resolution is to set innodb_autoinc_lock_mode to 0 in your my.cnf file prior to mysqld startup.

这篇关于为什么有时在自动增量列的值中存在多个缺口之一?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！