自增ID插入时,究竟发生了什么?

数据库 MySQL
知其然,知其所以然,我们先系统性讲讲相关知识点,InnoDB自增键,插入,如何插入,以及插入之后发生了什么?

《​​MySQL自增ID,居然大部分人都搞错了​​?​》中的作业题,有少量答对的人,但原理讲得不透,今天简单说下作业题中的答案,以及相关知识点。

作业题是这样的:

drop table t1;
create table t1(
id int not null auto_increment,
name varchar(10) unique,
count int default 0,
primary key(id),
index(name)
)engine=innodb;

insert into t1(id, name) values(1, "shenjian");

insert into t1(id, name) values
(111, "111"),(NULL, "abc"),(222, "222"),(NULL,"xyz");

执行结束后id分别是:

(1, 'shenjian')
(111, '111') // 允许指定值
(112, 'abc') // 忽略NULL,从最大值开始增
(222, '222') // 允许指定值
(223, 'xyz') // 忽略NULL,从最大值开始增

如果继续执行以下语句:

insert into t1(name)values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;

请问:

  • 会不会执行报错?
  • 如果报错,为什么呢?
  • 如果不报错,得到的数据是什么呢?

知其然,知其所以然。先系统性讲讲相关知识点,InnoDB自增键,插入,如何插入,以及插入之后发生了什么?

画外音:本文均以MySQL5.6,InnoDB引擎为例。

​什么是插入?

MySQL有一系列的语句,可以往数据库新增数据,称作泛插入语句(insert-like statement)。不同的插入方式,对自增键的影响是不一样的。

画外音:自增键,是指auto_increment,大家应该都用得很多。

有几种插入方式?​

更为细化后,总的来说,插入方式分为三类:

  • 简单插入(simple insert)
  • 批量插入(bulk insert)
  • 混合插入(mixed-mode insert)

什么是简单插入(simple insert)?

​普通的insert/replace语句,不管是单条插入还是多条插入,都是简单插入。

画外音:

  • 不包含递归的子查询;
  • 不包含insert … on duplicate key update… ;

例如,对于作业题中的数据表:

insert into t1(id, name) values(1,"shenjian");
insert into t1(name) values("zhangsan"),("lisi"),("ww");

都是简单插入。

简单插入的特点是,能够提前知道被插入的行数。因此,这类插入,在处理自增键时,是最容易的。

画外音:很容易保证自增键连续性。

什么是批量插入(bulk insert)?​

与简单插入相对,在插入时,不知道被插入的行数,是批量插入。

例如,对于作业题中的数据表:

insert into t1(name) select name from t2;

除此之外,像:

  • replace … select …
  • load data

都是批量插入。

由于不能够提前知道多少行插入,在处理自增列时,每插入一行,才会赋值新的自增值。

画外音,官网原文是:

InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each
row is processed.

这里的潜台词是,在批量插入事务并发时,“可能”出现同一个事务的自增键不连续。

什么是混合插入(mixed-mode insert)?

例如,对于作业题中的SQL:

insert into t1(id, name) values 

(111,"111"),(NULL, "abc"),(222,"222"),(NULL,"xyz");

有些行插入时指定了自增键,无需数据库生成;有些行插入时未指定自增键(NULL),需要数据库生成。

画外音:具体走哪个分支,实际执行时才知道。

以及,作业题中的如下SQL:

insert into t1(name) values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;

有些行插入实际上是修改,有些行插入实际上就是插入。​

画外音:具体走哪个分支,也是实际执行时才知道。

insert … on duplicate key update … 这种情况是最最复杂的,它可能导致,系统生成的自增值,在更新阶段用不上。

画外音,官网原文是:

an INSERT followed by a UPDATE, where the allocated value for the
AUTO_INCREMENT column may or may not be used during the update phase.

说了这么多,对于作业题,id分别是:​

(1, 'shenjian')
(111, '111') // 允许指定值
(112, 'abc') // 忽略NULL,从最大值开始增
(222, '222') // 允许指定值
(223, 'xyz') // 忽略NULL,从最大值开始增

如果继续执行以下语句,答案会是什么呢?

insert into t1(name)values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;

答案如下:

图片

对于:​

insert ("shenjian"),("aaa"),("bbb")

系统以为是批量插入,就生成了224,225,226三个连续自增键。

接下来:​

on duplicate key update ...

结果有一行插入冲突,实际是更新,导致224这个自增值并没有用上,于是就出现了奇怪的“自增值不连续”的怪异现象。​

责任编辑:赵宁宁 来源: 架构师之路
相关推荐

2021-01-21 14:09:28

云计算华为云

2018-03-16 15:31:50

2011-03-31 09:20:45

URLDNSWeb应用程序

2018-08-28 15:42:42

显卡NVIDIA厂商

2022-11-08 19:30:52

DjangoID自增

2020-04-02 09:00:00

DevOps架构

2024-02-23 08:38:34

AI模型计算机

2020-08-17 12:47:07

Mozilla裁员浏览器

2019-11-12 14:41:41

Redis程序员Linux

2018-12-14 15:35:20

MySQL索引数据库

2010-03-29 14:09:12

Oracle ID 自

2023-10-24 15:27:33

Mysql自增主键

2019-08-26 09:35:25

命令ping抓包

2010-02-07 09:00:29

AndroidLinux Kerne

2021-04-11 10:40:16

Git软件开发

2021-01-18 08:23:23

内存时底层CPU

2023-11-30 09:09:53

数据库Java

2010-06-28 09:59:47

SQL Server自

2024-06-14 08:34:36

2024-11-11 00:00:06

MySQLID数据类型
点赞
收藏

51CTO技术栈公众号