MySQL主键ID耗尽时会发生什么

说起流行的关系型数据库，MySQL一定会被提及。在使用MySQL的过程中，创建表时需要指定主键id，其类型一般为int或者bigint类型。但是，有这么一个问题，不知道你可曾想过：MySQL主键id耗尽时会发生什么？

为什么会想到这个问题呢？缘起于，笔者在某家公司偶然发现一张表竟然有30多亿条数据(2年多时间)。
表字段不多，数据+索引占用磁盘空间就将近1000G。
抛开数据同步以及数据恢复时会遇到的问题不说，仅仅是表主键id就是很大的消耗。

MySQL整型类型与取值范围

MySQL 支持 SQL 标准的整型类型：INTEGER（或 INT）和 SMALLINT。作为标准的扩展，MySQL 也支持这些整型类型：TINYINT、MEDIUMINT 和 BIGINT。以下表格展示了每种整型类型所需的存储空间和取值范围。

超出取值范围与溢出，MySQL 的处理方式

当 MySQL 向数值类型的列存储超出列数据类型允许范围的值时，处理结果取决于当时的 SQL 模式。
如果启用 SQL 严格模式，MySQL 则按照 SQL 标准处理——以报错的方式拒绝超出范围的值，即插入失败。
如果未启用任何限制模式，MySQL 会将值裁剪为列数据类型取值范围内的相应端点，并作为结果值存储。
当为整型列插入一个超出范围的值时，MySQL 会存储为列数据类型取值范围内的相应端点。
当为浮点型和定点型列插入一个超过指定（或默认）M 和 D 所表示的范围时，MySQL 会存储该取值范围内的相应端点的值。

假设表 t1 定义如下：

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

在启用 SQL 严格模式下，会报超出范围的错误：

mysql> SET sql_mode = 'TRADITIONAL';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

当未启用 SQL 严格模式，会进行裁剪并产生警告（warning）：

mysql> SET sql_mode = '';
mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
+---------+------+---------------------------------------------+
mysql> SELECT * FROM t1;
+------+------+
| i1   | i2   |
+------+------+
|  127 |  255 |
+------+------+

如果未启用 SQL 严格模式，列赋值转换（column-assignment conversion）会因裁剪而发生，并会产生警告（warning），以上这些会发生在 ALTER TABLE、LOAD DATA、UPDATE 和多行 INSERT 语句中。
在严格模式下，这些语句均会执行失败，并且某些、甚至所有值都不会被插入或更改，这取决于该表是否为事务表或其他一些因素。

主键id耗尽时会发生什么?

创建 t1 测试表，自增主键名称为 id，其类型为有符号 TINYINT (最大值 127)。
为了模拟主键耗尽，建表时指定主键 id 从126开始。即：按照理论最多只能插入2条数据。

思考一下：为什么使用 TINYINT 作为主键id

CREATE TABLE `t1` (
  `id` TINYINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=utf8mb4;

依次插入3次，每次1条数据到 t1 表中。

mysql> -- 查看下一个可用自增主键id 为 126
mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> select * from t1;
Empty set (0.00 sec)

mysql> -- 第一次插入一条数据
mysql> insert into t1 (name) values("hello");
Query OK, 1 row affected (0.00 sec)

mysql> -- 插入的数据主键 id 为 126
mysql> select * from t1;
+-----+-------+
| id  | name  |
+-----+-------+
| 126 | hello |
+-----+-------+
1 row in set (0.00 sec)

mysql> -- 查看下一个可用自增主键id 为 127，即：AUTO_INCREMENT=127
mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)


mysql> -- 第二次插入一条数据
mysql> insert into t1 (name) values("world");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-----+-------+
| id  | name  |
+-----+-------+
| 126 | hello |
| 127 | world |
+-----+-------+
2 rows in set (0.00 sec)

mysql> -- 查看表结构发现，下一个可用的主键 id 为 127
mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> -- 第三次插入数据，报错：主键id值 (127) 发生重复
mysql> insert into t1 (name) values("hello2");
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'


mysql> -- 查看表结构发现，下一个可用的主键 id 还是为 127
mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> -- 查询表中数据
mysql> select * from t1;
+-----+-------+
| id  | name  |
+-----+-------+
| 126 | hello |
| 127 | world |
+-----+-------+
2 rows in set (0.00 sec)

mysql> delete from t1 where id=126;
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set name="hello1" where id=127;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

通过上面操作可以发现：
1.主键id在每次插入数据之后会自动加1 (插入数据时没有指定主键id的值)

插入数据中若包含主键id值。下一个可用主键id为当前数据主键id值加1后得到的值

2.若当前主键id值加1超过该类型最大值后，则下一个可用主键id值一直为该类型的最大值

总结

综上所述，对于文中 主键id耗尽时会发生什么? 这个问题，归纳如下。
1.主键id耗尽时，无法继续插入(insert)新的数据
2.主键id耗尽时，不影响对现有数据的读取(select)、删除(delete)、更新(update)操作
3.主键id耗尽时，当前表中下一个可用的主键id一直为主键id类型的最大值并保持不变

拓展思考题：
redis 作为内存型数据库，在内存耗尽时会发生什么?

参考 & 引用

• MySQL官方文档 - 超出取值范围与溢出处理
  https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html

• 【中文翻译】- 超出取值范围与溢出处理
  https://jchehe.gitbook.io/mysql_data_types_cn/shu-zhi-shu-ju-lei-xing