mysql - MySQL:为什么我的 INSERT 语句在自动递增 id 时会跳过 56 个数字?
问题描述
在向我的 SQL 课程的学生演示 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``
我们看到所有 200 条记录sakila.actor
都已正确复制到新character
表中。
最后一行获取200
其character_id
自动递增 PK 的值。输出窗口显示上述任何命令都没有错误。
然后,当我们立即手动添加一条记录时:
INSERT INTO `character`
(first_name, last_name, shoe_size)
VALUES
('Bart', 'Simpson', 35);
很奇怪的是,我们发现这条记录的值256
是 itscharacter_id
而不是201
。
尽管运行SHOW VARIABLES LIKE 'auto_inc%';
显示两者auto_increment_increment
和auto_increment_offset
都设置为1
.
我们想了解为什么MySQL 会跳过 56 个数字?
请注意,这个问题不同于MySQL InnoDB auto_increment 值增加 2 而不是 1。病毒?和MySQL 自动增量列跳跃 10- 为什么?因为auto_incerement_increment
是 1,所以在我们的(易于重现的)场景中没有 DELETE 操作,我们每个人都是我们预期数据库的唯一用户。另外,对于这个问题的答案,对于实际发生的事情,没有一个是决定性的。最后,请参阅@Postman 的精彩答案,其中引用了上述问题的任何答案中未提及的根本原因。谢谢
解决方案
此行为与“批量插入”和innodb_autoinc_lock_mode
设置有关。
据我了解(文档对此不太清楚),当您使用INSERT INTO ... SELECT
语句时,MySQL 无法知道在运行查询之前实际插入了多少行,但新的 AUTO_INCREMENT 值的 ID 必须是使用innodb_autoinc_lock_mode=1
(连续)或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 值,因为这是大于 5 的 2 的最接近的幂。但是,由于您仅插入 5 行,因此它将仅使用其中的 5 个。
在您的情况下,您要插入 200 行,因此大于 200 的 2 的最接近幂为 256。因此,您有 56 个缺失 AUTO_INCREMENT 值的“间隙”,下一个条目的 ID 为 256。
推荐阅读
- python - Django Rest Framework 不适用于一对一关系
- python - Python。如何检查当前值是大于、等于还是小于前一个值?
- selenium - 尝试通过 sendKeys 方法输入值时 Appium 抛出错误
- eclipse - Eclipse C++ 资源配置灰显
- python - 在 astropy Table 中删除带有掩码元素的行
- javascript - GatsbyJS webpack错误,未定义的变量
- autofac - 在 `RegisterBuildCallback` 之前调用 Autofac `IStartable.Start()`
- sql - Oracle SQL where 语句的问题
- html - 使用 html 部分标签时在 CSS-Grid 上提供相同的 css 属性
- javascript - 用 Emscripten 编译用 C++ 编写的 OpenFace,得到警告:“未解析的符号”