insert select 主键ID 不连续的问题

最新推荐文章于 2023-06-30 17:07:21 发布

coder~

最新推荐文章于 2023-06-30 17:07:21 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/chai471793/article/details/107211813

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

问题来源

以前没注意过，最近使用insert … select 语句构造测试数据的时候发现主键ID 并没有连续增长，很疑惑

mysql> show create table  t_primary\G;
*************************** 1. row ***************************
       Table: t_primary
Create Table: CREATE TABLE `t_primary` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>
//清空下表
mysql> truncate  t_primary;
Query OK, 0 rows affected (0.01 sec)
//表中数据为空 
mysql> select * from t_primary;
Empty set (0.00 sec)
//插入数据
mysql>  insert into t_primary(a) values('a'),('b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_primary;
+----+---+
| id | a |
+----+---+
|  1 | a |
|  2 | b |
+----+---+
2 rows in set (0.00 sec)

mysql>  insert into t_primary(a)  select a from t_primary;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_primary;
+----+---+
| id | a |
+----+---+
|  1 | a |
|  2 | b |
|  3 | a |
|  4 | b |
+----+---+
4 rows in set (0.00 sec)

mysql>  insert into t_primary(a)  select a from t_primary;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
//what the fuck   4直接蹦到6 了
mysql> select * from t_primary;
+----+---+
| id | a |
+----+---+
|  1 | a |
|  2 | b |
|  3 | a |
|  4 | b |
|  6 | a |
|  7 | b |
|  8 | a |
|  9 | b |
+----+---+
8 rows in set (0.00 sec)
mysql>  insert into t_primary(a)  select a from t_primary;

//9  直接蹦到了13
mysql> select * from t_primary;
+----+---+
| id | a |
+----+---+
|  1 | a |
|  2 | b |
|  3 | a |
|  4 | b |
|  6 | a |
|  7 | b |
|  8 | a |
|  9 | b |
| 13 | a |
| 14 | b |
| 15 | a |
| 16 | b |
| 17 | a |
| 18 | b |
| 19 | a |
| 20 | b |
+----+---+

这是为什么呢？

官方文档真详细啊
InnoDB中的AUTO_INCREMENT处理

innodb_autoinc_lock_mode 参数分析

innodb_autoinc_lock_mode 参数

用于生成 自动增量 值 的锁定模式。对于传统的，连续的或交错的，允许的值分别为0、1或2。默认设置为1（连续）。
有关每种锁定模式的特征，请参见InnoDB AUTO_INCREMENT锁定模式。

InnoDB提供了一种可配置的锁定机制，可以显着提高将行添加到带有AUTO_INCREMENT列的表的SQL语句的可伸缩性和性能。要将 AUTO_INCREMENT机制与InnoDB表一起使用， AUTO_INCREMENT必须将列定义为索引的一部分，以便可以等效于对表进行索引查找以获取最大列值。通常，这是通过使该列成为某个表索引的第一列来实现的。 SELECT MAX(ai_col)

InnoDB AUTO_INCREMENT 三种锁定模式

四种插入术语

“ INSERT-like ” 语句

 在表中生成新的行中的所有语句，包括 INSERT， INSERT ... SELECT，REPLACE， REPLACE ... SELECT，
 和LOAD DATA。包括“ 简单插入 ”， “ 批量插入 ”和“ 混合模式 ” 插入。

“ 简单的插入 ”

可以预先确定要插入行数的语句（最初处理该语句时）。这包括单行和多行 INSERT以及 
REPLACE没有嵌套子查询但没有嵌套子查询的语句 INSERT ... ON DUPLICATE KEY UPDATE。

“ 大量插入 ”

 事先不知道要插入行数（以及所需的自动增量值的数目）的语句。这包括 
 INSERT ... SELECT， REPLACE ... SELECT和LOAD DATA语句，但不是纯 INSERT。
 在处理每一行时，一次为一列InnoDB分配新值AUTO_INCREMENT。

“ 混合模式的插件 ”

 这些是“ 简单插入 ”语句，用于指定部分（但不是全部）新行的自动增量值。
 下面c1是一个示例，其中 AUTO_INCREMENTtable 的 列是 t1：
 另一种类型的“ 混合模式插入 ”是 INSERT ... ON DUPLICATE KEY UPDATE，
 在最坏的情况下实际上是，INSERT 其后是a UPDATE，其中AUTO_INCREMENT
 在更新阶段可能会或可能不会使用为列分配的值 。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

innodb_autoinc_lock_mode = 0 （“ 传统 ”锁定模式）

在这种锁定模式下，所有“ 类似于INSERT ”的语句都将获得特殊的表级AUTO-INC 锁定，以将其插入具有 AUTO_INCREMENT列的表中。此锁通常保持在语句的末尾（而不是事务的末尾），以确保为给定的INSERT 语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增值任何给定语句分配的都是连续的。

锁释放：语句结束释放

innodb_autoinc_lock_mode = 1 （“ 连续 ”锁定模式）默认是这个

这是默认的锁定模式。在这种模式下，“ 批量插入 ”使用特殊的AUTO-INC 表级锁并将其保持到语句结束。这适用于所有 INSERT … SELECT， REPLACE … SELECT和LOAD DATA语句。一次AUTO-INC只能执行一个持有该锁的语句。如果批量插入操作的源表与目标表不同，则AUTO-INC在对从源表中选择的第一行进行共享锁定之后，将对目标表进行锁定。如果批量插入操作的源和目标位于同一表中，则AUTO-INC在所有选定行上执行共享锁之后，将执行锁定。
有点像串行分配自动增量

锁释放：
普通 insert ，自增锁申请后释放；
insert …select 批量插入，语句结束释放；

innodb_autoinc_lock_mode = 2 （“ 交错 ”锁定模式）

锁释放：申请后释放锁

InnoDB AUTO_INCREMENT锁定模式的用法含义

InnoDB AUTO_INCREMENT计数器初始化

查看本地配置默认1

mysql> show variables like "innodb_autoinc_lock_mode";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)