MySQL自增字段的自增值超过字段声明的范围怎么办

问题来源

面试遇到一个问题，设置自增主键的数据类型为int(10)，范围设置的比较小，如果自增超过这个范围会怎么样？当时一脸懵逼，确实没考虑过这个情况。

思路与猜测

首先是知道，字段类型声明为int(10)，后面的长度10只是zerofill的长度，不影响数据的存储，也就是自增超过的范围应该是int类型字段的最大值。

类型	存储占用字节	最小值	最大值
无符号int	4	0	4294967295(2^32-1)
有符号int	4	-2147483648(-2^31)	2147483647(2^31-1)

对于无符号的自增主键，也就是超过2^32-1=4294967295。由于不知道会发生什么，有几种猜测：

1. 重新从0开始？
2. 直接返回错误码？

验证与结果

1. 新建一个表用于测试自增；为了验证问题，把自增值设置到了最大值-2，用于测试。

CREATE TABLE `auto_inc `  (
  `id` int(1) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4294967294 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2. 先插入一条数据，能够插入成功，自增值上升1；

mysql> insert into auto_inc(name) values ('');
Query OK, 1 row affected (0.04 sec)
mysql> select * from auto_inc;
+------------+------+
| id         | name |
+------------+------+
| 4294967294 |      |
+------------+------+
1 row in set (0.03 sec)

3. 然后继续插入两条数据；发现第二条插入数据报错了，主键冲突，而此时表里最大的id是4294967295

mysql> insert into auto_inc(name) values ('');
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into auto_inc(name) values ('');
1062 - Duplicate entry '4294967295' for key 'PRIMARY'
mysql> select * from auto_inc;
+------------+------+
| id         | name |
+------------+------+
| 4294967294 |      |
| 4294967295 |      |
+------------+------+
2 rows in set (0.04 sec)

所以可以得出结论：自增主键超过数据类型的最大值后，会继续使用最大值作为下一次的递增值，如果最大值记录在表中已经存在，会报主键冲突错误；
这个问题用smallint和tinyint做自增键也可以复现，而且不需要设置自增值。

问题的意义

没有意义。面试的时候可以考你一下。
如果是smallint或tinyint类型的自增主键溢出，那只能说表设计的不合理，用范围这么小的类型做主键字段；
如果是int类型的自增主键溢出，int范围都21亿了，再加上主键一般无符号，42亿的取值范围不够用？即便插入报错之类的导致主键不连续，那30多亿有效主键总有吧，MySQL一个表30亿数据，想啥呢老哥，还不考虑分库分表？
至于bigint……
可能MySQL的开发人员也觉得这样的情况应该由开发者决定怎么处理，所以就在溢出后仍使用最大值？

为什么移除后自增值要保持最大值？

TODO