如何插入？ | 非开车，纯技术交流

《InnoDB自增键基础知识测试》中的四道测试题，全答对的朋友少之又少，为了讲清楚InnoDB自增键，今天先系统性讲讲，什么是插入，如何插入。

 

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

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

 

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

• 简单插入(simple insert)

• 批量插入(bulk insert)

• 混合插入(mixed-mode insert)

 

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

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

画外音：

（1）不包含递归的子查询；

（2）不包含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)？

如《自增键测试》里的实验三：

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

 

有些行插入时指定了自增键，无需数据库生成；

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

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

 

以及《自增键测试》里实验四：

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.

 

说了这么多，《InnoDB自增键基础知识测试》中的四道测试题，大家心中都有谱了吧？看完本文，是不是解答了四道题的疑问呢？

画外音：昨天确实答对率比较低，今天可以再回过头重新做一遍。

 

末了，最近骂我的水友比较多，反馈帮朋友推广的文章越来越多了，我统一回复下：

（1）精力有限，平均每周产出2-3篇技术文的频率没有降低；

（2）朋友让我帮忙，我帮还是不帮？

（3）我的地盘我做主；

 

另外|||

 

在这里，能有机会系统性思考，之前没有想过的问题。

画外音：当然，我只能分享和引导，收获有多大，得看大伙细节扣得多深。就像昨天和今天的两篇，“自增键”和“插入”谁都会用，但真的思考过这些“coding时用不到”的看似无用的知识么。

 

在这里，你能看到别处没有的技术文章。

画外音：

（1）文章原创；

（2）以“戏谑”的文笔“踏实”的做学问：素材可以复现，绝不“张口就来”；知识点亲自考证，要么官网，要么源码，绝不传播楼主不确定的二手消息；

 

希望大伙以包容的心态，少骂两句，楼主如果玻璃心，说不定哪天被喷没了。

架构师之路-分享技术思路

最后，再挖两个坑，大伙想想。

 

作业题一：

insert into t1(name)values("zhangsan"),("lisi"),("wangwu");

这类简单插入，当有并发事务执行时，自增键仍能保证连续性，是如何做到的？

A： 行粒度加锁，实施互斥

B： 表粒度加锁，实施互斥

C： 全局自增键单例，实施互斥

D： SQL语句粒度加锁，实施互斥

E： 事务粒度加锁，实施互斥

 

作业题二：

基于SQL复制的主从同步，如果执行并发批量插入(bulk insert)事务，“可能”出现同一个事务的自增键不连续，那么能否保证主从数据的一致性？如果能，是怎么保证的？

 

昨天看到一则留言：“取消了80%曾经关注的公众号，唯独架构师之路一直置顶”。

感动！

感谢！