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

ivansli 2022/03/23 354℃ 0

说起流行的关系型数据库,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 作为内存型数据库,在内存耗尽时会发生什么?

参考 & 引用

数据库

评论啦~