一、场景准备测试场景为MySQL8.0：主键重复场景唯一键重复场景1.创建一张表，包括主键和唯一约束CREATETABLEt1(idint(11)NOTNULLauto_increment,c1varchar(64)DEFAULTNULL,c2int(11)DEFAULTNULL,PRIMARYKEY(id),UNIQUEKEYuk_c1(c1))ENGINE=InnoDBDEFAULTCHARSET=utf8；2.写入初始测试数据insertintot1(c1,c2)values('a',1),('b',2),('c',3);mysql>select*fromt1;+----+------+------+|编号|c1|c2|+----+-----+-----+|1|一个|1||2|乙|2||3|c|3|+----+-----+-----+3rowsinset(0.00sec)2.开始测试insertinto#Testprimarykeyrepeatmysql>insertintot1values(1,'aaa',111);ERROR1062(23000):Duplicateentry'1'forkey't1.PRIMARY'#Testuniquekeyduplicationmysql>insertintot1(c1,c2)values('a',4);ERROR1062(23000):Duplicateentry'a'forkey't1.uk_c1'insertignoreintoinsert当以insertignoreintoinsert的方式插入数据时，语句会被终止，并告知错误原因处理过程中出现主键传统等错误。但是如果使用insertignore进行数据插入，则会忽略错误插入的行，插入没有问题的行记录，最后会显示警告。#测试主键重复mysql>insertignoreintot1values(1,'aaa',111);QueryOK,0rowsaffected,1warning(0.00sec)mysql>showwarnings;+----------+------+------------------------------------------+|级别|代码|留言|+--------+------+--------------------------------------+|警告|第1062章键“t1.PRIMARY”的重复条目“1”|+--------+------+------------------------------------------+1rowinset(0.01sec)#testuniquekeyduplicationmysql>insertignoreintot1(c1,c2)values('a',4);QueryOK,0rowsaffected,1warning(0.00sec)mysql>showwarnings;+-------+------+-----------------------------------------+|级别|代码|留言|+--------+------+------------------------------------+|警告|第1062章键't1.uk_c1'的重复条目'a'|+---------+------+--------------------------------------+1rowinset(0.00sec)测试的时候惊奇的发现主键的自增列在test表变了，经过上次操作变成了7：mysql>显示创建表t1\G**************************1.行******************************表：t1Create表：CREATETABLE`t1`(`id`intNOTNULLAUTO_INCREMENT,`c1`varchar(64)DEFAULTNULL,`c2`intDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_c1`(`c1`))ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=utf8mb31rowinset(0.00sec)#但是表的行数据没有增加mysql>从t1中选择*；+----+-----+-----+|编号|c1|c2|+----+-----+-----+|1|一个|1||2|乙|2||3|c|3|+----+-----+------+3rowsinset(0.00sec)#new写入一条数据后自增id变为7mysql>insertintot1(c1,c2)values('d',4);QueryOK,1rowaffected(0.00sec)mysql>select*fromt1;+----+------+------+|编号|c1|c2|+----+-----+-----+|1|一个|1||2|乙|2||3|c|3||7|d|4|+----+-----+------+4rowsinset(0.00sec)replaceinto最后，replaceinto方法的结果是，如果插入的数据是原始值，然后主键冲突，主键的内容将被替换。如果唯一键冲突，则删除唯一值所在的行。重新插入新行，如果没有冲突，则正常插入数据#测试主键重复mysql>replaceintot1values(1,'aaa',111);QueryOK,2rowsaffected(0.00sec)mysql>从t1中选择*;+----+-----+-----+|编号|c1|c2|+----+-----+-----+|1|啊啊|第111话2|乙|2||3|c|3||7|d|4|+----+-----+-----+4rowsinset(0.00sec)#Testuniquekeyrepetitionmysql>replaceintot1(c1,c2)values('b',4);查询正常，影响2行（0.01秒）mysql>select*fromt1;+----+------+------+|编号|c1|c2|+----+-----+-----+|1|啊啊|第111话3|c|3||7|d|4||8|乙|4|+----+-----+------+4rowsinset(0.00sec)above测试了三种插入数据的方式，但是在测试过程中，发现当插入失败，自增列的自增值实际增加了。3.问题分析为了更好的理解，我们先来了解下AUTO_INCREMENT属性在不同的存储引擎中，其自增值的存储策略是不同的：MyISAM引擎的自增值保存在数据文件中。InnoDB引擎的自增值其实是保存在内存中的，而MySQL8.0版本之后，自增值的变化记录在redolog中。MySQL重启时，redolog用于恢复重启前的自增值。之前表的自增值现在是7，重启后变成了4。但是，理解了这一点之后，我们并不能马上理解当前的问题。我们知道，在插入数据的时候，如果插入数据中的自增列没有指定它的值，那么该列就会使用当前的自增值作为它的值。如果指定它的值，将插入指定的值。当然也满足唯一性原则。同时，当指定值大于自增值时，自增值也会相应变化。自增使用的算法由auto_increment_offset参数决定，auto_increment_increment用于确定步长。默认为1：mysql>select@@auto_increment_offset;+----------------------+|@@auto_increment_offset|+------------------------+|1|+------------------------集合中的+1行（0.00秒）mysql>select@@auto_increment_increment;+----------------------------+|@@auto_increment_increment|+---------------------------+|1|+------------------------+1rowinset(0.00sec)那么，为什么会出现这样的情况呢数据没有插入成功但自增值变大？原因很简单，用插入数据的过程来分析：因为自增值的保存是在真正执行插入数据之前完成的，所以会出现这种问题。这时候有人会想，AUTO_INCREMENT的值能改回来吗？简单测试：mysql>showcreatetablet1\G**************************1.row******************************表：t1Create表：CREATETABLE`t1`(`id`intNOTNULLAUTO_INCREMENT,`c1`varchar(64)DEFAULTNULL,`c2`intDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_c1`(`c1`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb31rowinset(0.00sec)mysql>select*fromt1;+----+------+-----+|编号|c1|c2|+----+-----+-----+|1|一个|1||3|c|3||7|d|4||8|乙|4|+----+-----+------+4rowsinset(0.00sec)#修改自增值为15mysql>altertablet1auto_increment=15;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0#Modifysuccessfullymysql>showcreatetablet1\G*****************************1.行***************************表：t1Create表：CREATETABLE`t1`(`id`intNOTNULLAUTO_INCREMENT,`c1`varchar(64)DEFAULTNULL,`c2`intDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_c1`(`c1`))ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8mb31rowinset(0.00sec)#novalueinserted,modifyGo返回修改成功mysql>altertablet1auto_increment=9;QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0mysql>showcreatetablet1\G*****************************1.行***************************表：t1CreateTable:CREATETABLE`t1`(`id`intNOTNULLAUTO_INCREMENT,`c1`varchar(64)DEFAULTNULL,`c2`intDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_c1`(`c1`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb31rowinset(0.00sec)#修改回自增的中间值mysql>altertablet1auto_increment=5;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0#不会报错但是无法修改mysql>showcreatetablet1\G********************************1.行**************************表：t1创建T能够：创建表`t1`（`id`intNOTNULLAUTO_INCREMENT,`c1`varchar(64)DEFAULTNULL,`c2`intDEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_c1`(`c1`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb31rowinset(0.00sec)很明显，自增值如果往大的方向修改是没有问题的，但是如果修改小的话，就要看插入到当前数据库的值会不会“卡住”修改后的自己了-在中间增加价值。在这种情况下，就没有办法改回来了。原因很明显。自增属性与主键一起使用。如果表中id=4和id=6之间有5的值??，将自增值改回5。当插入数据时，自增值会插入5的值，并加1自增值，就会出现这个问题。这时候自增值再插入就会违反唯一性原则。4.问题扩展生产环境中类似的问题还有很多。题，如：#当前插入值为8，自增值为9mysql>select*fromt1;+----+------+------+|编号|c1|c2|+-----+------+------+|1|a|1||3|c|3||7|d|4||8|b|4|+----+------+------+4行set(0.00sec)#Insertdataequivalentto(9,'t1',1)mysql>insertintot1values(null,'t1',1);QueryOK,1rowaffected(0.00sec)#Opentransactionmysql>begin;QueryOK,0rowsaffected(0.00sec)#插入数据相当于(10,'t2',2)mysql>insertintot1values(null,'t2',2);QueryOK,1rowaffected(0.00sec)#Transactionrollbackmysql>rollback;QueryOK,0rowsaffected(0.00sec)#Insertdata相当于(11,'3',3)mysql>insertintot1values(null,'t3',3);QueryOK,1rowaffected(0.01sec)mysql>select*fromt1;+----+------+------+|编号|c1|c2|+----+-----+-----+|1|一个|1||3|c|3||7|d|4||8|乙|4||9|t1|1||11|t3|3|+----+-----+-------+6rowsinset(0.00sec)在插入过程中开始了一个事务，在插入过程中回滚了事务。回滚后再次插入数据时，发现自增值出现了“空洞”，于是问题又来了。为什么插入数据时有回滚，数据回滚了，自增的值却没有回滚？为了更直观，继续测试，假设有两笔交易测试前的数据：mysql>select*fromt1;+----+-----+-----+|编号|c1|c2|+----+-----+-----+|1|a|1||3|c|3||7|d|4||8|b|4||9|t1|1||11|t3|3||13|t4|3|+----+-----+-----+7rowsinset(0.00sec)fortesting:session1session2begin;insertintot1values(null,'s1',1);begin;insertintot1values(null,'s2',2);commit;rollback;Dataaftertest:mysql>select*fromt1;+----+------+------+|编号|c1|c2|+----+-----+-----+|1|一个|1||3|c|3||7|4||8|乙|4||9|t1|1||11|t3|3||13|t4|3||15|+------+8rowsinset(0.00sec)发现还是“空”，此时答案已经很明确了，在不同事务写操作的时候申请自增，为了避免两个事务申请同一个自增值，所以需要加锁，自增值按一定顺序申请。按照前面的例子：首先两个session都开启了事务，session1在id=14的自增值前面，session2申请了id=15的自增值，然后在session2之后提交事务插入成功，此时session1在事务回滚时插入成功或失败。这时候就会出现上面提到的问题。没有办法回滚，回滚会出现在自增中间“卡死”。以后有机会继续说自增锁。
