MySql 5.7.19版本自动递增值问题

一：问题详情：

线上有一个定时任务，主要处理当天的报表信息，处理完后将所有的数据迁移到一张历史表中；

时间长了后出现一个问题，数据迁移insert时候出现重复的主键。

网上找了很多解决方案，特此做下记录，方便后续快速回顾。

 

二：问题原因：(InnoDB)

如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。自动增长计数器仅被存储在主内存中，而不是存在磁盘上。

InnoDB使用下列算法来为包含一个名为satis_id的AUTO_INCREMENT列的表buss_satisfication_session初始化自动增长计数器：服务器启动之后，当一个用户对表buss_satisfication_session做插入之时，InnoDB执行等价如下语句的动作：

 

SELECT MAX(satis_id) FROM buss_satisfication_session FOR UPDATE;

语句取回的值逐次加一，并被赋给列和自动增长计数器。

如果表是空的，值1被赋予该列。如果自动增长计数器没有被初始化，而且用户调用为表 buss_satisfication_session 显示输出的SHOW TABLE STATUS语句，则计数器被初始化（但不是增加计数）并被存储以供随后的插入使用。

注意，在这个初始化中，我们对表做一个正常的独占读锁定，这个锁持续到事务的结束。

InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。

注意，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0，InnoDB处理行，就仿佛值还没有被指定，且为它生成一个新值。

自动增长计数器被初始化之后，如果用户插入一个明确指定该列值的行，而且该值大于当前计数器值(小于无效)，则计数器被设置为指定列值。如果没有明确指定一个值，InnoDB给计数器增加一，并且赋新值给该列。

当访问自动增长计数器之时，InnoDB使用专用的表级的AUTO-INC锁定，该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略，来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。

注意，如果你回滚从计数器获得数的事务，你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。

如果用户给列赋一个赋值，或者，如果值大过可被以指定整数格式存储的最大整数，自动增长机制的行为不被定义。

在CREATE TABLE和ALTER TABLE语句中，InnoDB支持AUTO_INCREMENT = n 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因，这个选项的影响在服务器重启后就无效了。

 

三：演示一个案例

1.建表语句：

 

CREATE TABLE `buss_satisfaction_session` (
  `satis_id` int(8) NOT NULL AUTO_INCREMENT,
  `session_id` int(8) NOT NULL,
  `model_type` int(2) DEFAULT NULL COMMENT '模式一、二。三',
  `satis_content` varchar(200) DEFAULT NULL,
  `label_id` varchar(80) DEFAULT NULL,
  `session_code` varchar(255) DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `ROBOT_ID` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`satis_id`),
  KEY `session_id` (`session_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2.查询当前状态

 

 

mysql> show create table buss_satisfaction_session\G;
*************************** 1. row ***************************
       Table: buss_satisfaction_session
Create Table: CREATE TABLE `buss_satisfaction_session` (
  `satis_id` int(10) NOT NULL AUTO_INCREMENT,
  `session_id` int(8) NOT NULL,
  `model_type` int(2) DEFAULT NULL COMMENT '模式一、二。三',
  `satis_content` varchar(200) DEFAULT NULL,
  `label_id` varchar(80) DEFAULT NULL,
  `session_code` varchar(255) DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `ROBOT_ID` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`satis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

3.插入语句;(多插入几条)

 

 

INSERT INTO `buss_satisfaction_session` VALUES (null, 1, '2', '满意', 'assg', '29b8af85-5cd0-42f6-9800-af8d8b5ceee0', '2020-08-06 09:20:33', '29b8af85-5cd0-42f6-9800-af8d8b5ceee0');

 

4.查看内容：

 

5.删除最后一条数据

 

 

delete from buss_satisfaction_session where satis_id=4;

 

6.查看（目前一切正常）

 

7.service mysql restart

 

8.查看表信息，发现自增序列回到了4

 

9.插入数据并查看主键：（4那条被复用了。我的业务是1-4（old）插入新表，重启后插入了4就会造成主键重复）

 

四：解决方案：

1. 切换引擎：innodb更改为myisam（该玩意不支持事务，业务上不支持，放弃）

2. 更新mysql版本到8.0+版本（该值支持序列化到磁盘，不会随着重启丢失）

3. 脏数据（处理完脚本就插一条脏数据）

 

 

 

 

 

参考文章：

https://blog.csdn.net/jbgtwang/article/details/8837896?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param