mysql - 为什么有时自动增量列的值中存在多个间隙之一？

Question

我有一个这样的表:

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

当我向该表中插入一些新值时，有时 id 列中会出现一些空白。像这样:

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 Fiddle 上的复制和 RexTester

怎么了？为什么有时 id 的值会乱序？我该如何预防？

---

编辑:我创建了这样的表:

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

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

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

结果如下:

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

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

Answer 1

这似乎与您的 INSERT 语句的形成方式有关。参见 this fiddle ，这是从你的例子略有修改。在这种情况下，你不会得到间隙(但如果你添加更多插入，你可能会得到...... in fact, it does 。)。

编辑:在稍微深入挖掘之后，我发现如果使用 MyISAM 引擎(与 InnoDB 相对)，则不会出现间隙。所以，也许这是 InnoDB 中的一个缺陷或奇怪的设计选择......？

编辑 2:进一步挖掘揭示了 this bug ，针对 InnoDB 存储引擎提交。它非常符合原始问题的用例。尚未提供该错误的解决方案，但似乎一种解决方案是在 mysqld 启动之前在 my.cnf 文件中将 innodb_autoinc_lock_mode 设置为 0。