前言

• 面试中，遇到面试官，问出自增id达到上限之后，新插入的数据会怎么样？一脸懵，虽然经常用的自增id ，但是从来没仔细想过这个问题，所以当场尴尬了。今天就在本地环境测试一下，也增加一些自己知识。

关于自增id

• MySQL中有很多的自增id，在创建表时，每个自增id都定义了初始值，一般情况下都是1，然后在插入数据时，就会往上加步长。虽然数字是可以无限大的，但是在计算机的存储中，在创建字段之后，就定义了字段的存储字节，也就是有上限，比如常见的 int, long, 但是在实际的开发过程中，几乎不会达到上限，因为即使是无符号的int，最大也可以表示，2的32次方-1，即4294967295，其实当单表数量达到十亿级别，早就需要分库分表了，还没等到超，但是为了搞清楚超了之后会怎么样，还是做一个小实验吧。

自增id的创建(建议)

• 给大家一个自增id建议的创建语句，其中 unsigned 是常常被忽略，所以一定要加上的。

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键'

自增id的步长

• 在使用的过程中，我们经常就认为自增id每次都是+1，但是不是这样的，他是可以配置的，详细命令如下：

show variables like '%auto_inc%';

• auto_increment_increment 参数 可以看出当前的步长为1
• auto_increment_offset 参数 可以看出起始为1。

表定义了自增id

大部分表都会有自增id字段，且设置为主键，为了便于复现问题，采用 int unsigned 作为实验的自增id字段类型。

相关表的创建语句及SQL执行如下：

> create table testid2 (
    -> `id` int unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '主键'
    -> )ENGINE=InnoDB AUTO_INCREMENT=4294967295;
    
> insert into testid2 values (null);

> show create table testid2;
 
 /*
  CREATE TABLE `testid2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 
*/

>  insert into testid2 values (null);
// ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

• 可以从sql执行中看出，第一个insert语句插入数据成功之后，当前表的AUTO_INCREMENT值没有变化，没有按照预想的+1，还是保持了原值（4294967295），这就导致了第二个insert语句又是相同的自增id，插入之后就会报主键冲突的错误。
• 为了避免留下隐藏的坑，同时为了技术性和安全性考虑，bigint(20) unsigned 是完全足够的。

InnoDB系统自增row_id

• 上面提到了创建表时，指定了自增主键，但是如果没有指定主键呢？那么InnoDB会给你创建一个不可见，长度为6个字节的row_id, 同时，这个row_id是全局的，是所有无主键的InnoDB表共同的。当这些无主键的InnoDB表，每插入一行数据，就会向InnoDB申请一个row_id, 然后再把dict_sys.row_id的值加 1;

• 既然出现了自增的id，那么他的长度是多少呢？长度是无符号的8字节，但是在InnoDB内部设计的时候，只给row_id留了6个字节长度，所以最后可用的就只有6字节了，对于row_id有2个特征:

1、row_id 写入表中的值范围，是从 0 - 2的48次方 - 1

2、当dict_sys.row_id = 2的48次方时，如果再有插入的数据，需要获取新的row_id时，此时会返回0，相当于是循环的，到达最大后，又从0开始

• 当然，2的48次方-1这个值本身已经足够大了，但是面对运行很多年的业务，MySQL实例跑的足够久的情况下，还是有可能超过的，所以尽量手动指定主键。当然这里还有一个问题就是，申请到了已有的row_id（假设是N）会怎么样呢，答案是：InnoDB会用新数据覆盖已有的行。
• 验证这个结论
• 手动修改row_id，方便复现问题，当然，这种方式只适合在测试环境使用，切勿在生产环境修改不清楚的环境变量。

mysql>  create table testid3(a int) engine=innodb;

> gdb -p <mysql pid> -ex 'p dict_sys.row_id=1'

mysql> insert into values (1);

> gdb -p <mysql pid> -ex 'p dict_sys.row_id=281474976710656'

mysql> insert into testid3 values(2);

mysql> insert into testid3 values(3);

最后可以看到，表里面只有 2 ，3 两行数据，也就是 2 覆盖1的位置。

两种自增id的对比

• 两种自增id其实在生产环境短期都是不会有任何问题的，但是在运行的足够久的MySQL实例中还是会出现的。主动指定自增id为主键，当自增id达到上限后，会报错，主键冲突。当使用MySQL内置的row_id时，当row_id达到上限会导致数据行被覆盖的问题。数据行被覆盖会导致数据丢失，而报错只会导致业务失败，一般情况下，还是数据完整性和可靠性高于可用性，因为你不知道你被覆盖的数据有多重要。