MySQL：获取Auto_increment失败问题记录

项目场景：

人员权限设置，定义了一张存储自增id的表sys_id_sequence（A表）

/*A表定义*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_id_sequence
-- ----------------------------
DROP TABLE IF EXISTS `sys_id_sequence`;
CREATE TABLE `sys_id_sequence`  (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `date_stamp` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1147 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

和一张人员权限表auth_rule_admin_role（B表），其中B表的主键为U+六位A表的自增主键，不足六位前置补零，例如U000001，每次插入B表前，先进行A表的插入删除，用来进行自增

#插入自增序号                                
insert into sys_id_sequence (date_stamp)
          values(now());

#删除自增序号
delete from sys_id_sequence;

这样，每次查询自增id就是最新的（不要问为啥这么设计，这个项目的司龄比我大）

#获取自增id
select
        CONCAT('U', LPAD(CAST(
        (SELECT Auto_increment FROM information_schema.`TABLES` WHERE Table_Schema = 'tacs_specs'
        AND table_name = 'sys_id_sequence') AS CHAR),6,'0'));

然后把最新的自增id拼接好吐给B表即可

问题描述

获取自增id失败，通过查询sql查出来结果是0，导致插入B表失败

原因分析：

考虑过是MySQL版本问题，导致这样获取自增id失效，因此查看版本信息

#版本
show VARIABLES like '%version%';

在这里插入图片描述
发现版本没问题，不是网上说的MySQL8失效问题，然后猜想是否因为A表为空导致，毕竟它执行了删除，因此插入一条数据，然后再获取，可以自增了，但是步长为2，看下步长设置吧

#查询自增步长
SHOW VARIABLES LIKE '%auto_increment%';

在这里插入图片描述
给它步长设置为1

#设置步长
SET auto_increment_increment=1

但是项目中明明是插入删除，运行了好几年都没问题，因此插入数据也不可靠，但是转念一想，是不是因为插入了数据给它重新赋值了当前的自增序号，因此，查下自增序列

#查询当前自增序列
SHOW TABLE STATUS FROM tacs_specs LIKE 'sys_id_sequence';

在这里插入图片描述
果然如此，那么我们给它重置下自增序号即可，查询B表，找到最大的序号，设置为那个值即可（注：这里的截图我已经重置完成了）

解决方案：

重置自增序号

#重置自增
alter table sys_id_sequence auto_increment=1147;

在这里插入图片描述
再次查询，序号没问题，然后让运维同事试着新增数据，成功，完美解决（猜想这个问题是因为服务关闭，MySQL重启导致自增序号失效，当然，MySQL获取自增id通过SELECT_LAST_INSERT()函数也可以，项目当初没有使用这个方法是在考虑线程安全问题吗？可这个项目没多少人用啊，搞不懂老项目…）