插入时,究竟发生了什么?(非开车,纯技术交流)
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
(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;
(1)简单插入(simple insert)
(2)批量插入(bulk insert)
(3)混合插入(mixed-mode insert)
insert into t1(id, name) values(1,"shenjian");
insert into t1(name) values("zhangsan"),("lisi"),("ww");
insert into t1(name) select name from t2;
(1)replace … select …
(2)load data
InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each row is processed.
insert into t1(id, name) values
(111,"111"),(NULL, "abc"),(222,"222"),(NULL,"xyz");
insert into t1(name) values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;
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.
(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")
on duplicate key update ...
结果有一行插入冲突,实际是更新,导致224这个自增值并没有用上,于是就出现了奇怪的“自增值不连续”的怪异现象。
架构师之路-分享技术思路
相关文章:
插入时,发生了什么,搞透了吗?谢转。