一文弄清：mysql自增表插入数据时，Id不连续问题

2023-09-06 17:40| 来源: 网络整理| 查看: 265

今天查看数据库时，发现id设置了auto_increment，但是，数据库中id是不连续。正好趁着这个机会总结一下mysql常用的插入语句（insert into、insert ignore into、insert into … on duplicate key update)，在项目中的数据库使用innodb引擎。

实际项目使用介绍表定义 CREATE TABLE `user_key_record` ( id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id', user_id BIGINT UNSIGNED NOT NULL COMMENT '用户id', user_key BIGINT UNSIGNED NOT NULL COMMENT 'key', UNIQUE KEY uniq_id_key(`user_id`, `user_key`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户记录表'; 代码中调用记录 public int insert(long userId, long key) { String sql = "insert ignore into %s (user_id, key) values (:userId, :key)"; MapSqlParameterSource params = new MapSqlParameterSource("userId", userId) .addValue("activityKey", activityKey); return jdbcTemplate.update(sql, params); } 发现了什么问题？

代码中使用了insert ignore into，就是这个insert ignore into引起的这个不连续问题。下面我们详细分析一下。

mysql常用插入语句分析

以上面的表举例讨论各插入语句

insert into insert into user_key_record (user_id, user_key) values (0, 0) 插入成功， id+1 insert into user_key_record (user_id, user_key) values (0, 0) 1062-Duplicate entry '0-0' for user_key_record.uniq_id_key 插入失败 id+1 insert into user_key_record (user_id, user_key) values (0, 0) 1062-Duplicate entry '0-0' for user_key_record.uniq_id_key 插入失败 id+1 insert into user_key_record (user_id, user_key) values (1, 0) 插入成功 id+1 select * from user_key_record 结果： id user_id user_key 1 0 0 4 1 0

这样，我们就知道了，插入成功、失败id都会自增+1

insert ignore into insert ignore into user_key_record (user_id, user_key) values (1, 0); 执行成功，数据不变 id+1 insert ignore into user_key_record (user_id, user_key) values (2, 0); 插入成功，数据不变 id+1 select * from user_key_record 结果： id user_id user_key 1 0 0 4 1 0 6 2 0

这样，我们就知道了，unique key冲突会忽略插入，但是id会+1

insert into … on duplicate key update insert into user_key_record (user_id, user_key) values (2, 0) on duplicate key update user_key = 1; 执行成功，数据更新 id+1 insert ignore into user_key_record (user_id, user_key) values (3, 0) on duplicate key update user_key = 1; 插入成功，id+1 select * from user_key_record 结果： id user_id user_key 1 0 0 4 1 0 6 2 1 8 3 0

这样，我们就知道了，unique key冲突会更新user_key，同时id也会+1

到此为止，常用的insert语句我们就列举完成了，那大家肯定会问能不能避免id不连续插入问题？答案当然是可以的。

可以业务自己保证原子性，通过业务逻辑避免unique key冲突的问题可以通过修改的变量 innodb_autoinc_lock_mode的值解决, 将其设为0后, 在INSERT未成功执行时不会自增主键。 innodb_autoinc_lock_mode

根据MySQL官方手册的说明:

There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. As of MySQL 8.0, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default (innodb_autoinc_lock_mode=1). 在MySQL8中, 默认值为2 (interleaved, 交错), 在MySQL8以前, 准确地说在8之前, 5.1之后, 默认值为 1 (consecutive, 连续), 在更早的版本是0 innodb_autoinc_lock_mode的说明

这个值主要用于平衡性能与安全(主从的数据一致性), insert主要有以下类型

innodb_autoinc_lock_mode = 0:

与更高版本的MySQL向后兼容在这一模式下，所有的insert语句都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，一个事务可能包涵有一个或多个语句它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全) 由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入

innodb_autoinc_lock_mode = 1:

这一模式对simple insert做了优化，由于simple insert一次性插入值的个数可以立即确定, 所以mysql可以一次生成几个连续的值用于这个insert语句, 总的来说这个对复制也是安全的(保证了基于语句复制的安全) 这一模式也是MySQL8.0之前的默认模式, 这个模式的好处是auto_inc锁不要一直保持到语句的结束, 只要语句得到了相应的值后就可以提前释放锁，唯一的缺陷是产生的自增值不一定是完全连续的

innodb_autoinc_lock_mode = 2:

由于这个模式下已经没有了auto_inc锁, 所以这个模式下的性能是最好的, 但是它也有一个问题, 就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。现在mysql已经推荐把二进制的格式设置成row, 所以在binlog_format不是statement的情况下这个模式可以达到最好的性能。

至此问题分析结束。

参考

insert doc innodb_autoinc_lock_mode doc

至此，今天对这个问题分析结束，有表述不准确的欢迎指出，分享知识，共同进步！！

【本文地址】

公司简介

联系我们