首先创建了一个表 t,可以看到id是自增主键,c是唯一键,不允许重复!
然后我们插入数据,其中主键为null
发现插入后id为1 ,为啥是1呢?
如果插入数据时id字段指定为0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT值填到自增字段
执行show create table t;
可以看到关于表的信息,现在AUTO_INCREMENT就是2了,表示下一次插入会是2
但是这里前提是你插入的时候,id为0或者空
插入一行,AUTO_INCREMENT就是3了,同时这行数据id为2,可以从下图看到
那我如果下次插入的id小于3呢
AUTO_INCREMENT依旧是3,这是为啥呢?
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是X,当前的自增值是Y。
- 如果X<Y,那么这个表的自增值不变;
- 如果X≥Y,就需要把当前自增值修改为新的自增值
接下来,我插入(0,1,1)出现了唯一键冲突,插入失败,再插入了一行数据
发现AUTO_INCREMENT变成5了?不应该是4嘛,因为我才插入了一行数据啊!
要解决这个问题,我们就必须看下面的插入数据流程图了!
会发现,我们在真正插入数据的时候,会先把表的AUTO_INCREMENT改动
也就是说:不管你是否插入成功,我这个AUTO_INCREMENT依旧是会改变的!!!
同样地,事务回滚也会产生类似的现象,这就是第二种原因
我在下面做了实验,可以看我下面的实验结果
AUTO_INCREMENT为11
首先我关闭了mysql的自动提交
然后开启了一个事务,执行一条插入语句,然后我们通过show create table t命令观察表t的信息
可以看到 表t的AUTO_INCREMENT发生了变化为12
AUTO_INCREMENT也还是12
另外,在发生批量insert数据的时候,也会发生主键不连续的情况,具体如下:
首先往t里面插入四行数据 分别是1 2 3 4
然后insert批量到t2当中
然后往t2当中插入数据,结果主键不是5,而是8??
insert…select,实际上往表t2中插入了4行数据。但是,这四行数据是分三次申请的自增id,第一次申请到了id=1,第二次被分配了id=2和id=3, 第三次被分配到id=4到id=7。
由于这条语句实际只用上了4个id,所以id=5到id=7就被浪费掉了。之后,再执行insert into t2 values(null, 5,5),实际上插入的数据就是(8,5,5)。
因此,对于批量插入数据的语句,MySQL有一个批量申请自增id的策略:
- 语句执行过程中,第一次申请自增id,会分配1个;
- 1个用完以后,这个语句第二次申请自增id,会分配2个;
- 2个用完以后,还是这个语句,第三次申请自增id,会分配4个;
- 依此类推,同一个语句去申请自增id,每次申请到的自增id个数都是上一次的两倍;