mysql8.0出现自增列值重复利用问题怎么样解决
下文主要给大家带来mysql8.0出现自增列值重复利用问题怎么样解决，希望这些内容能够带给大家实际用处，这也是我编辑mysql8.0出现自增列值重复利用问题怎么样解决这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。
这在很多场景下可能导致问题，包括但不限于：主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里，可以看到大量的同行抱怨。如，假设t1有个历史表t1_history用来存t1表的历史数据，那么mysqld重启前，ti_history表中可能已经有了（2,2）这条数据，而重启后我们又插入了（2，2），当新插入的(2,2)迁移到历史表时，会违反主键约束。这类问题是否在数据迁移中会出现呢，我们也需要注意一下。比如我们使用mysqldump导出数据，然后导入到另外一个环境。mysqldump导出数据里面是指定了自增长值的方式，而非空。
建表时可以指定AUTO_INCREMENT值，不指定时默认为1，这个值表示当前自增列的起始值大小，如果新插入的数据没有指定自增列的值，那么自增列的值即为这个起始值。对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。那么又问，既然这个值没有持久下来，为什么我们每次插入新的值后，show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。
知道了AUTO_INCREMENT是实时存储内存中的。那么，mysqld重启后，从哪里得到AUTO_INCREMENT呢？内存值肯定是丢失了，实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT，而这种方法就是造成自增id重复的原因。
InnoDB AUTO_INCREMENT锁定模式：
innodb_autoinc_lock_mode 配置参数 有三种可能的设置 。对于" 传统 "，" 连续 "或 " 交错 "锁定模式，设置分别为0,1或2 。从MySQL 8.0开始，交错锁定模式（innodb_autoinc_lock_mode=2）是默认设置。在MySQL 8.0之前，连续锁定模式是默认值（innodb_autoinc_lock_mode=1）。
《在MySQL8.0的解决思路：》
将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。
--因自增主键没有持久化而出现问题的常见场景：
1. 业务将自增主键作为业务主键，同时，业务上又要求主键不能重复。
2. 数据会被归档。在归档的过程中有可能会产生主键冲突。
所以，强烈建议不要使用自增主键作为业务主键。刨除这两个场景，其实，自增主键没有持久化的问题并不是很大，远没有想象中的"臭名昭著"。
--：最后，给出一个归档场景下的解决方案，
创建一个存储过程，根据table2（归档表）自增主键的最大值来初始化table1（在线表）。这个存储过程可放到init_file参数指定的文件中，该文件中的SQL会在数据库启动时执行。
DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;
###################################################################
下面复现MySQL8.0之前，自增主键复用的情况：
----创建测试表emp：
MySQL [test]> create table emp (id int auto_increment,name varchar(10),primary key (id));MySQL [test]> insert into emp values (1,'zhang');MySQL [test]> insert into emp values (null,'liu');MySQL [test]> insert into emp values (null,'huang');MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang || 2 | liu || 3 | huang |+----+-------+3 rows in set (0.00 sec)
---从下面的信息可以看出，emp表id自增的下一个数字是 4
MySQL [test]> show create table emp\G*************************** 1. row *************************** Table: empCreate Table: CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.08 sec)
---删除id=2和3的数据，然后再插入一条数据
MySQL [test]> delete from emp where id=2 or id=3;MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang |+----+-------+1 row in set (0.01 sec)MySQL [test]> insert into emp values (null,'hhhh');MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang || 4 | hhhh |+----+-------+2 rows in set (0.00 sec)
（从上面的结果可以看出，如果不重启数据库的情况下，虽然把前面的数据删除了，但是在此插入数据 它的自增id还是和没删除数据之前的顺序递增。）
---删除id为4的数据，只保留第一行数据，然后重启MySQL如下：
MySQL [test]> delete from emp where id=4;MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang |+----+-------+1 row in set (0.00 sec)
----重启MySQL后，再次插入一条数据：
MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang |+----+-------+1 row in set (0.00 sec)MySQL [test]> show create table emp\G ---（重启完MySQL在此查看该表的自增id，这时候就是2了）*************************** 1. row *************************** Table: empCreate Table: CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf81 row in set (0.00 sec)
---此时再插入一条数据，会发现自增id重复利用了：
MySQL [test]> insert into emp values (null,'feng');MySQL [test]> select * from emp;+----+-------+| id | name |+----+-------+| 1 | zhang || 2 | feng |+----+-------+2 rows in set (0.00 sec)
从上面的测试，我们看到在插入新的数据之前AUTO_INCREMENT为2，然后插入了（null,'feng'）， 上面的测试反映了mysqld重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况。如果存在从库可能会导致数据库不一致的情况！！！！！！！
注意：
另外，当MySQL开启一个事务后，有类INSERT操作，自增值就会增加；但是当事务回滚后，自增值并不会减小。也就是说自增值会有空洞。
￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥
二、不同的insert插入方式，会对有自增id有不同影响，如下：
1、第一种：带有 null 值的写法
mysql> create table emp(id int auto_increment, a int, primary key (id)) engine=innodb;mysql> insert into emp values (1,2),(2,2),(3,2);MySQL [test]> show create table emp\G ---这时候查看该表的自增值是4*************************** 1. row *************************** Table: empCreate Table: CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec) MySQL [test]> insert into emp values (4,2),(null,2),(null,2); ---使用null的方式插入值MySQL [test]> show create table emp\G ----这时候会发现自增值变成了8，但是查看该表的数据id列最大是6，*************************** 1. row *************************** Table: empCreate Table: CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set (0.00 sec)MySQL [test]> select * from emp;+----+------+| id | a |+----+------+| 1 | 2 || 2 | 2 || 3 | 2 || 4 | 2 || 5 | 2 || 6 | 2 |+----+------+6 rows in set (0.01 sec)MySQL [test]> insert into emp values (null,3); ---此时，向该表插入一条数据，id的值就变成了8MySQL [test]> select * from emp;+----+------+| id | a |+----+------+| 1 | 2 || 2 | 2 || 3 | 2 || 4 | 2 || 5 | 2 || 6 | 2 || 8 | 3 |+----+------+7 rows in set (0.00 sec)从上面的测试可以看出，采用null写法的时候，自增长值会多增加一个值。2、第二种：使用insert into ...select 方式插入数据，如下：mysql> create table t1(id int auto_increment primary key,name varchar(255));mysql> create table t2(name varchar(255))engine=innodb;mysql> insert into t2 values('aa'),('bb');mysql> insert into t1(name) select *from t2; ---将t2表的数据插入到t1mysql> select * from t1;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)mysql> show create table t1; ---然后查看下t1表的自增值，发现现在是4了，而数据只有2条| Table | Create Table | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |1 row in set (0.00 sec)这也说明了，采用insert .. select方式，自增长也会多增加一个值。
注意：
我们可以看到第一种带NULL值的写法，自增长值会多增加一个值；第二种insert .. select，自增长也会多增加一个值。这个会带来什么问题呢？你会发现从库自增长值却是正常的（当复制格式为ROW时），这个时候其实也就是主从数据不一致了，但影响不大，除非出现记录ID大于自增长ID，那样插入数据重复会报错。
究其原因，和insert语句的定位也有关系，目前有这几类insert语句。
1、simple insert，如insert into t(name) values('test')
2、bulk insert，如load data | insert into … select …. from ….
3、mixed insert，如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');
这个和参数innodb_autoinc_lock_mode有很大的关系，默认参数值为1。innodb_autoinc_lock_mode这个参数控制着在向有auto_increment列的表插入数据时，相关锁的行为，有三个取值：
0：这个表示tradition（传统）
它提供了一个向后兼容的能力，在这一模式下，所有的insert语句("insert like") 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁。注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值（它保证了基于语句复制的安全）。由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。
1：这个表示consecutive（连续）
这一模式下对simple insert做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以MySQL可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的（它保证了基于语句复制的安全）。由于现在MySQL已经推荐把二进制的格式设置成ROW格式，所以没有复制安全问题了。
这一模式也是MySQL的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁。
2：这个表示interleaved（交错）
由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。
注意：在MySQL8.0版本中已经将innodb_autoinc_lock_mode该参数的默认值改为2！！！！！
对于以上关于mysql8.0出现自增列值重复利用问题怎么样解决，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。
