MySQL:为什么我的 INSERT 语句在自动增加 id 时会跳过 56 个数字？

Question

在向我的 SQL 类(class)的学生演示 INSERT 语句时，我们发现了 MySQL 8.0 中的一些奇怪行为。请帮助我们了解正在发生的事情。 (不需要解决方法，因为我们知道一些，这是为了学习，而不是用于生产。谢谢)

我们正在创建一个新数据库并从著名的 Sakila 中复制一些行。示例数据库，如下所示:

CREATE DATABASE simpsons;

USE simpsons;

CREATE TABLE `character` (
    character_id smallint unsigned NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20),
    shoe_size INT,
    PRIMARY KEY (character_id));

INSERT INTO `character` 
        (first_name, last_name)
    SELECT 
        first_name, last_name 
    FROM 
        sakila.actor;

当我们这样做时， SELECT * FROM ``character``我们看到来自 sakila.actor 的所有 200 条记录已正确复制到新 character table 。

最后一行获取值 200为其 character_id自动增加的 PK。输出窗口在上述任何命令中均未显示错误。

然后，当我们立即手动添加一条记录时:

INSERT INTO `character`
    (first_name, last_name, shoe_size)
VALUES
    ('Bart', 'Simpson', 35);

很奇怪，我们发现这条记录的值是 256作为其 character_id而不是 201 .

尽管事实上运行 SHOW VARIABLES LIKE 'auto_inc%';显示两者 auto_increment_increment和 auto_increment_offset设置为 1 .

我们想了解为什么 MySQL 跳过 56 个数字？

请注意，这个问题不同于 MySQL InnoDB auto_increment value increases by 2 instead of 1. Virus?和 MySQL autoincrement column jumps by 10- why?因为 auto_incerement_increment是 1，在我们的(容易重现的)场景中没有 DELETE 操作，我们每个人都是我们预期数据库的唯一用户。此外，该问题的所有答案都不能确定实际发生的情况。最后，请参阅@Postman 的精彩答案，其中引用了上述问题的任何答案中未提及的根本原因。谢谢

Answer 1

这种行为与"bulk inserts"有关。和 innodb_autoinc_lock_mode 环境。

据我了解(文档对此不太清楚)，当您使用 INSERT INTO ... SELECT 时语句，MySQL 在运行查询之前无法知道实际插入了多少行，但是在使用 innodb_autoinc_lock_mode=1 时必须保留新 AUTO_INCREMENT 值的 ID。 (连续)或 2 (交错)。根据我的观察，它保留了一组 AUTO_INCREMENT 数字，其中计数是 2 的幂(无法确认，只是猜测)。请参阅以下示例:

CREATE TABLE sourceTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE targetTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    original VARCHAR(30)
);

INSERT INTO sourceTable(name) VALUES ('one');
INSERT INTO sourceTable(name) VALUES ('two');
INSERT INTO sourceTable(name) VALUES ('three');
INSERT INTO sourceTable(name) VALUES ('four');
INSERT INTO sourceTable(name) VALUES ('five');

INSERT INTO targetTable(original) SELECT name FROM sourceTable;

INSERT INTO targetTable(original) VALUES ('manual');

SELECT * FROM targetTable;

这将生成以下输出:

+----+----------+
| id | original |
+----+----------+
|  1 | one      |
|  2 | two      |
|  3 | three    |
|  4 | four     |
|  5 | five     |
|  8 | manual   |
+----+----------+

当从源表插入 5 行时，它会保留接下来的 8 个可能的 AUTO_INCREMENT 值，因为这是 2 大于 5 的最接近的幂。但是，由于您只插入了 5 行，因此它将只使用其中的 5 个。

在您的情况下，您要插入 200 行，因此大于 200 的最接近的 2 次幂将是 256。因此您有 56 个缺少 AUTO_INCREMENT 值的“差距”，下一个条目获得 ID 256。