【建议收藏】MySQL中的自增id超出上限的问题

在mysql中有多种自增id，除了我们日常开发中经常使用的自增主键外，还有一些其他的自增id，主要是mysql内部为了辅助其正常运行而使用的。

这些自增id，都是定义了初始值，然后不停的累加步长。对于每一种自增id，在mysql中都会定义其数据类型，以及这个数据类型所占用的字节长度，也就是说每个自增id，都是有上限的，只不过上限的大小不尽相同而已，既然自增id有上限，那么就有可能被用完，那问题来了，如果自增id用完了，会发生什么呢？

在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'
  

这里产生唯一键冲突的错误，说明执行第二条插入语句时，表increment_id_test的auto_increment的值和表中已有的主键id值 4294967295相同，也即表明：当auto_incement达到上限后，再次申请下一个id时，得到的值保持不变。

当把主键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。
  

所以在建表时，通常我们都会将主键id设置为8字节的bigint unsigned，bigint的数据长度为 2^64次方，一个很大很大的数值。理论上，在并发够大，时间够长的情况下，还是有可能达到其上限的，但是这个也仅仅是理论上，因为到目前为止，还没有一个mysql实例超过这个上限。

其实对于bigint 类型id超出上限的问题，我们可以换个角度想一下，如果表的自增主键达到了这个上限，那么表中的数据量也是一个天文数据了，那么在这个表的业务操作，也基本上操作不动了，绝大部分业务，都会在表数据量达到这个上限前，进行表的切分。因此说，达到这个上限，只可能在理论上，实际的业务场景，基本上不存在的。

二、row_id

我们都知道，使用InnoDB存储引擎时，如果数据表没有设置主键，那么Innodb会给该表设置一个不可见，长度为6字节的默认主键 row_id。Innodb维护了一个全局的dict_sys.row_id值，这个值，被所有无主键的数据表共同使用，每个无主键的数据表，插入一行数据，都会是当前的dict_sys.row_id的值增加1.来源公众号：【码农编程进阶笔记】

其实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的值到了2^48次方-1后，再次插入数据，下一个值就是0，然后开始循环。不过和自定义主键不同的是，row_id标识的主键，没有唯一性约束，当插入数据的row_id值，在表中已经存在的话，那么写入的数据会"悄无声息"覆盖已存在的数据。来源公众号：【码农编程进阶笔记】

因为row_id是Innodb维护的，Innodb没有暴露出修改该值的接口和命令，要想验证上述情况，只能通过调试的方式修改变量的值，因为涉及到gdb调试的相关内容，这里不再深究。

总结

从上面 Innodb对row_id重复情况下的处理机制来看，在设计表时，最好还是使用自定义主键，而不要使用Innodb的默认主键，至少在自定义主键的场景下，当自增id达到上限时，插入数据，系统会提示报错信息，而不是覆盖数据，因为数据覆盖意味着数据丢失，影响的是数据可靠性，而插入失败产生的报错，影响是可用性。在数据业务中，可靠性通常是优先于可用性的。

往期精选文章

【收藏并分享】详解mysql的for update

MySQL的B+树如何存储主键和数据？

为什么MySQL不建议使用NULL作为列默认值？

100G内存下，MySQL查询200G大表会OOM么？

MySQL常见面试题：什么是主从延时？如何降低主从延时？