正文 

当前位置： 首页 > 知识 > java教程

mysql中的自增主键id自增到最大，使用完id会发生什么，怎么办？(代码片段)

Agly_Charlie Agly_Charlie     2023-02-13     144

关键词：

文章目录

背景

之前的学习和实践上了解到的是：

SQL插入异常，主键冲突错误，因为自增ID达到上限后，再申请时它的值不会改变，继续插入数据会导致报主键冲突错误

/* SQL Error (1062): Duplicate entry '2147483647' for key 'PRIMARY' */

但我在偶然中发现，还会有这个错误信息，读取自增ID失败。

/* SQL Error (1467): Failed to read auto-increment value from storage engine */

测试过程

于是产生了进一步实验的想法，以下是操作过程：
本次测试环境MySQL 5.7.22

创建表，自增ID

CREATE TABLE `tb_id_incr` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

场景一：测试自增ID已经达到最大值

  • 第1步：测试，直接让表记录自增ID达到最大值：
INSERT INTO `tb_id_incr` (`id`, `name`) VALUES ('2147483647', '3');

  • 第2步：达到最大值后，再insert一条记录
INSERT INTO `tb_id_incr` (`name`) VALUES ('3');
/* SQL Error (1062): Duplicate entry '2147483647' for key 'PRIMARY' */

  • 第3步：达到最大值后，再insert 多个记录，
INSERT INTO `tb_id_incr` (`name`) VALUES ('3'),('4'),('5'),('6'),('7'),('8'),('9')
/* SQL Error (1062): Duplicate entry '2147483647' for key 'PRIMARY' */

结论：都产生主键冲突错误

场景二：测试自增ID即将达到最大值

  • 第1步：让表记录自增ID 即将达到最大值：
INSERT INTO `tb_id_incr` (`id`, `name`) VALUES ('2147483640', '3');

  • 第2步：还没达到最大值后，再insert 多个记录，
INSERT INTO `tb_id_incr` (`name`) VALUES ('3'),('4'),('5'),('6'),('7'),('8'),('9'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('5'),('6'),('7'),('8'),('9');
/* SQL Error (1467): Failed to read auto-increment value from storage engine */

  • 第3步：查看内部自增ID
    查看show create table的值已达到最大ID，表明批量插入失败，但还是会继续消耗ID，自增到最大后，报错提示读取自增ID失败

总结

自增ID用完了，会发生两种情况：

  1. 如果是已经是最大ID了，那么会报错：主键冲突：
/* SQL Error (1062): Duplicate entry '2147483647' for key 'PRIMARY' */
  1. 如果是即将到达最大值，并且此时批量插入的记录条数大于剩余可用的ID数，会继续消耗ID，自增到最大后，报错提示读取自增ID失败：
/* SQL Error (1467): Failed to read auto-increment value from storage engine */

解决方案

  1. 一般来说表不应该存在这么大，在达到这么大之前就应该优化，例如，做分库分表之类的优化了。
  2. 调整数据类型，修改为BIGINT数据类型，但是要注意数据太多，DDL执行时长问题。（参考文末扩展内容）
  3. 注意上层业务ORM框架的解析也要使用LONG数据类型

扩展

扩展1：修改字段的数据类型，建议使用modify 用法

1.修改字段的数据类型，建议使用modify 用法，更加友好；
modify 和change 区别：参考 https://stackoverflow.com/questions/14767174/modify-column-vs-change-column

# CHANGE
ALTER TABLE 
	`tb_id_incr`
CHANGE 
	COLUMN `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
	
# MODIFY
ALTER TABLE
  `tb_id_incr`
MODIFY
  COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

扩展2：Alter table的底层原理

  1. INPLACE模式和COPY复制临时表模式
    https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
  2. 修改数据类型使用的模式
    https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
    可以看到 修改数据类型，只会使用COPY模式

    修改过程，是会影响读写。

mysql中的自增主键id自增到最大，使用完id会发生什么，怎么办？(代码片段)

文章目录背景测试过程创建表，自增ID场景一：测试自增ID已经达到最大值场景二：测试自增ID即将达到最大值总结解决方案扩展扩展1：修改字段的数据类型，建议使用modify用法扩展2：Altertable的底层原理背... 查看详情

mysql对自增主键id进行重新排序

参考技术A一、不清空数据原理：删除原有的自增ID，重新建立新的自增ID。二、如果曾经的数据都不需要的话，可以直接清空所有数据，并将自增字段恢复从1开始计数 查看详情

mysql自增主键怎么用(代码片段)

导航自增主键怎么设置MySQL是怎么保存自增主键的自增主键的修改机制造成自增主键不连续的几种情形自增主键和UUID及随机ID比较自增主键的好处自增主键的坏处UUID及随机ID参考文章自增主键怎么设置通常，自增主键的设置... 查看详情

mysql自增主键怎么用(代码片段)

导航自增主键怎么设置MySQL是怎么保存自增主键的自增主键的修改机制造成自增主键不连续的几种情形自增主键和UUID及随机ID比较自增主键的好处自增主键的坏处UUID及随机ID参考文章自增主键怎么设置通常，自增主键的设置... 查看详情

mybatis自增主键返回

...自动生成一个自增主键。通过mysql函数获取到刚插入记录的自增主键：LAST_INSERT_ID()是insert之后调用此函数。修改insertUser定义： 非自增主键返回：使用mysql的uuid()函数生成主键，需要修改表中id字段类型为string，长度设置成35... 查看详情

mysql使用onduplicatekeyupdate时导致主键不连续自增

...但是会有一个影响：默认情况下，每次更新都会更新该表的自增主键ID，如果更新频率很快，会导致主键ID自增的很快，过段时间就超过数字类型的的范围了解决这个问题，有两种方式：(实际目前的方式就是把自增主键ID设置为bi... 查看详情

mybatis里使用sqlsessiontemplate怎么获得刚插入的自增主键id

参考技术Ause要查询的数据库goselect*frominformation_schema.table_constraints本回答被提问者采纳 查看详情

业务id生成策略(代码片段)

...巧性的提升。先来介绍普遍的分布式ID生成策略：1.利用DB的自增主键　这里又有两种做法，一种是单独创建一个只有自增主键的表，来负责主键自增，业务表从这里取得自增的主键返回给业务主键生成组件使用。  另外一... 查看详情

android中sqlite的自增主键id重置为1(代码片段)

在重置id之前，需要了解：当SQLite数据库中包含自增列时，会自动建立一个名为sqlite_sequence的表。这个表有name和seq两列，name记录自增列所在的表，seq记录当前序号（下一条记录的编号就是当前序号加1）... 查看详情

技术分享关于mysql自增id的事儿

...于不同的版本它们有如下的区别：计数器的值存储在内存中的，重启后丢弃，下一次将读取最大的一个自增ID往后继续发号。https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization计数器的值将会持... 查看详情

postgresql自增主键的用法以及在mybatis中的使用(代码片段)

前言近期，对产品进行数据库由MySql迁移至PostgreSQL过程中，在MySql自增主键到PostgreSQL自增主键的迁移适配中，历经了一点曲折，最终通过跳坑和出坑的过程也算解决了问题，特此记录，给遇到类似的同学做... 查看详情

关于sql优化，你需要掌握这些

1、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15？(1)如果表的类型是MyISAM，那么是18因为MyISAM表会把自增主键的最大ID记录到数据文件里，... 查看详情

sq数据库中怎样设置自增主键？

就是一个表中的某列设为自增主键...1、打开数据库客户端，点击连接上你的数据库。2、在对应的库上，点击展开表视图。3、右键点击要设置的表，点击‘表设计’。4、打开表设计页面，点击选中主键字段。5、点击后，在下方... 查看详情

使用mybatis插入自增主键id的数据后返回自增的id

...到用户注册的功能需要用到用户ID，但是用户ID是数据库自增生成的，这种情况上网查询后使用下面的方式配置mybatis的insert语句可以解决：1<insertid="insert"keyProperty="id"useGeneratedKeys="true"?parameterType="com.demo.domain.User">?2insertintoUser... 查看详情

mybatis批量插入，怎么返回生成的自增主键

参考技术A我觉得你应该在for循环里执行$this->db->insert()，然后用$this->db->insert_id()获取每一次插入后的id 查看详情

自增主键为什么会不连续

业务上的错误做法:设计依赖于自增主键的连续性.自增主键不连续的情况:测试使用的表结构CREATETABLE`t`(`id`int(11)NOTNULLAUTO_INCREMENT,`c`int(11)DEFAULTNULL,`d`int(11)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`c`(`c`))ENGINE=InnoDB;　　 查看详情

被问懵了：mysql自增主键一定是连续的吗？(代码片段)

...值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。MySQL8.0之后版本在MySQL8.0版本，将自增值的变更记录在了redolog中，重启的时候依靠redolog恢复重启之前的值。可以通过看表详情查看当前自增值，以及... 查看详情

mybatis批量插入，怎么返回生成的自增主键

...Dao中不能使用@param注解。3、Mapper.xml中使用list变量接受Dao中的集合。本回答被提问者采纳 查看详情