MySQL:为什么在自动递增ID时我的INSERT语句会跳过56个数字? [英] MySQL: Why does my INSERT statement skip 56 numbers when auto-incrementing the id?

问题描述

在向我的SQL课程的学生演示INSERT语句的同时，我们提出了MySQL 8.0中的一些奇怪行为.请帮助我们了解正在发生的事情.(不需要变通方法，因为我们知道一些变通方法，这是学习而不是生产.谢谢)

While demonstrating the INSERT statement to the students of my SQL course, we've come up on some odd behavior in MySQL 8.0. Please help us learn what is happenning. (No need for workarounds as we're aware of a few and this is for learning, not for production. Thank you)

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

We are creating a new database and copying some rows from the well-known Sakila sample DB, like so:

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;

当我们执行此操作并选择* * FROM``character''时，我们看到来自 sakila.actor 的所有200条记录已正确复制到新的字符表.

When we do this and SELECT * FROM ``character`` we see that all 200 records from sakila.actor have been copied correctly over to the new character table.

最后一行的 character_id 自动递增的PK的值为 200 .输出窗口在以上任何命令中均未显示任何错误.

The last row gets the value 200 for its character_id auto-incremented PK. The output window shows no errors in any of the above commands.

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

Then, when we immediately add one more record manually:

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

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

Quite oddly, we find that this record gets the value 256 as its character_id and not 201.

尽管运行了 SHOW VARIABLES LIKE'auto_inc％'; 的事实表明， auto_increment_increment 和 auto_increment_offset 都设置为 1 .

This is despite the fact that running SHOW VARIABLES LIKE 'auto_inc%'; shows that both auto_increment_increment and auto_increment_offset are set to 1.

我们想学习为什么 MySQL会跳过56个数字吗?

We would like to learn why does MySQL skip 56 numbers?

请注意，此问题不同于 MySQL的autoincrement列跳了10-为什么?，因为 auto_incerement_increment 为1，在我们的(容易复制的)方案中没有DELETE操作，并且我们都是唯一的用户我们的预期数据库.另外，该问题的答案都无法确定实际发生了什么.最后，请参阅@Postman的精彩答案，该答案引用了上述问题的任何答案中都未提及的根本原因.谢谢

Please note, this question is different from MySQL InnoDB auto_increment value increases by 2 instead of 1. Virus? and MySQL autoincrement column jumps by 10- why? because auto_incerement_increment is 1, there are no DELETE operations in our (easily reproducible) scenario and we each are the only users of our prospective DBs. Plus none of the answers to that question are conclusive as to what actually happened. Finally, please see @Postman's wonderful answer which references a root cause not mentioned in any of the answers to the above questions. Thank you

推荐答案

此行为与和

This behavior has something to do with "bulk inserts" and the innodb_autoinc_lock_mode setting.

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

As far as I understand it (the documentation isn't quite clear about this), when you use a INSERT INTO ... SELECT statement, MySQL cannot know how many rows are actually being inserted before running the query, but the IDs for the new AUTO_INCREMENT values have to be reserved when using innodb_autoinc_lock_mode=1 (consecutive) or 2 (interleaved). From my observation it reserves a set of AUTO_INCREMENT numbers where the count is a power of 2 (cannot confirm this, only a guess). See the following example:

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个值.

When inserting the 5 rows from the source table, it reserves the next 8 possible AUTO_INCREMENT values because that is the closest power of 2 number greater than 5. However, it will use only 5 of them since you insert only 5 rows.

在您的情况下，您要插入200行，因此大于2的2的最接近幂将是256.因此，您有56个缺失的AUTO_INCREMENT值的间隙"，下一个条目的ID为256.

In your case, you are inserting 200 rows, so the closest power of 2 number greater than 200 would be 256. So you have a "gap" of 56 missing AUTO_INCREMENT values and the next entry gets the ID 256.

这篇关于MySQL:为什么在自动递增ID时我的INSERT语句会跳过56个数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！