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

在向我的 SQL 课程的学生演示 INSERT 语句时，我们发现了 MySQL 8.0 中的一些奇怪行为。请帮助我们了解发生了什么。 （不需要解决方法，因为我们知道一些解决方法，这是为了学习，而不是为了生产。谢谢）

我们正在创建一个新数据库并从众所周知的数据库中复制一些行Sakila https://dev.mysql.com/doc/sakila/en/示例数据库，如下所示：

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``我们看到所有 200 条记录来自sakila.actor已正确复制到新的character table.

最后一行获取值200为其character_id自动递增PK。输出窗口显示上述任何命令都没有错误。

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

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

很奇怪的是，我们发现这条记录得到了值256 as its character_id并不是201.

尽管事实上正在运行SHOW VARIABLES LIKE 'auto_inc%';表明两者auto_increment_increment and auto_increment_offset被设置为1.

我们想学习whyMySQL会跳过56个数字吗？

请注意，这个问题不同于MySQL InnoDB auto_increment 值增加 2 而不是 1。 病毒？ https://stackoverflow.com/questions/3590391/mysql-innodb-auto-increment-value-increases-by-2-instead-of-1-virus and MySQL自动增量列跳跃10-为什么？ https://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why因为auto_incerement_increment为 1，在我们的（易于重现的）场景中没有 DELETE 操作，并且我们每个人都是我们预期数据库的唯一用户。另外，这个问题的答案都不能确定到底发生了什么。最后，请参阅@Postman 的精彩答案，其中引用了上述问题的任何答案中未提及的根本原因。谢谢

这种行为与“批量插入” https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes和innodb_autoinc_lock_mode https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode环境。

据我了解（文档对此不太清楚），当您使用INSERT INTO ... SELECT语句，MySQL 在运行查询之前无法知道实际插入了多少行，但是在使用时必须保留新 AUTO_INCREMENT 值的 IDinnodb_autoinc_lock_mode=1（连续）或2（交错）。根据我的观察，它保留了一组 AUTO_INCRMENT 数字，其中计数是 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_INCRMENT 值，因为这是大于 5 的最接近的 2 次幂。但是，它只会使用其中的 5 个，因为您只插入 5 行。

在您的例子中，您要插入 200 行，因此大于 200 的最接近的 2 次幂将为 256。因此，您有 56 个缺失 AUTO_INCREMENT 值的“间隙”，下一个条目的 ID 为 256。