【建议收藏】MySQL中的自增id超出上限的问题
共 2822字,需浏览 6分钟
·
2022-05-28 04:24
在mysql中有多种自增id,除了我们日常开发中经常使用的自增主键外,还有一些其他的自增id,主要是mysql内部为了辅助其正常运行而使用的。
在mysql中,对于不同的自增id值达到上限后,对应的处理方式是不同的。下面我们就对mysql中,几个比较重要的自增id进行分析一下。
为了方便下文描述,我们建立如下的表结构:
#来源公众号:【码农编程进阶笔记】
CREATE TABLE `increment_id_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;
一、自定义自增主键
自定义自增主键,是工作中最经常使用到的一种自增id,对于自增主键的一些细节问题可以参考你真的懂自增主键。
在表 increment_id_test 中,字段id是自增的,而且被定义成主键。id的数据类型为int,可表示的最大数值是2^32-1,也就是4294967295。为了能够让id字段快速达到这个上限,这里有两种方法:来源公众号:【码农编程进阶笔记】
1.直接插入一个id=4294967295 的数据行。
insert into increment_id_test values(4294967295);
2.设置autoincrement的值。
alter table increment_id_test auto_increment=4294967295;
当然也可以在建表的时候,就指定auto_increment的值为 4294967295。
两种方式的差异在于,设置auto_increment的方式,表示下次插入的数据行的id是 4294967295。
这里我们使用第一种方式做实验,相比第二种方式,可以减少一次数据插入,就可以让id达到上限。实验步骤如下:
1.插入一条id为 4294967295 的数据行
#来源公众号:【码农编程进阶笔记】
insert into increment_id_test values(4294967295);
2.在插入一条id为null的数据行,当自增主键为null时,插入的主键id值auto_increment
insert values(null);
返回信息如下:
Duplicate entry '4294967295' for key 'increment_id_test.PRIMARY'
当把主键id的数据类型设置为int时,我们需要考虑表未来的数据量大小,毕竟 4294967295 并不是一个很大的值,对于一个每秒插入100行的业务,不到500天,就可以达到主键id上限。
其实在建表时,无论主键id是否设置为可自增,当id值大小超过这个上限后,都是会报错的。主键自增的情况下,报错信息为:唯一键冲突:
Duplicate entry '4294967295' for key 'increment_id_test.PRIMARY'
对于普通字段的情况,报错信息为:插入数据超出数据类型范围:
Data truncation: Out of range value for column 'id' at row 1。
二、row_id
其实row_id的值在存储时,数据长度为8字节,只不过Innodb只使用后6个字节。那么row_id的值,写到数据表中时就有一下两个特点:
1.row_id写入表中的值范围,是从0-2^48-1。
2.当row_id的值为2^48时,再进行数据插入,那么row_id的后6个字节的值,就全部为0了。
因为row_id是Innodb维护的,Innodb没有暴露出修改该值的接口和命令,要想验证上述情况,只能通过调试的方式修改变量的值,因为涉及到gdb调试的相关内容,这里不再深究。
总结
从上面 Innodb对row_id重复情况下的处理机制来看,在设计表时,最好还是使用自定义主键,而不要使用Innodb的默认主键,至少在自定义主键的场景下,当自增id达到上限时,插入数据,系统会提示报错信息,而不是覆盖数据,因为数据覆盖意味着数据丢失,影响的是数据可靠性,而插入失败产生的报错,影响是可用性。在数据业务中,可靠性通常是优先于可用性的。
往期精选文章