MySQL 主键 自增 ID 会用完吗?

首先我们一般创建 MySQL 数据表的时候,大部分情况下会创建一个自增主键ID 的字段,可能你的建表语句如下:

CREATE TABLE IF NOT EXISTS `tb`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `body` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

可以看到 ID 为 int 类型,所谓 int 类型,就会有长度限制,那么我们去看下 他的长度限制是多少 打开 MYSQL 官方网站,我们看下 INT 数据类型会有多少的范围 https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

file

可以看到 INT 类型的取值范围是 -2147483648 - 4294967295。所以 在 MySQL 中 自增 ID 是会用完的。那么问题来了,加入他的 ID 用完会发生什么事呢?

我们来验证下。在 MySQL 命令行中依次输入以下建表、插入语句看看会发生什么

DROP TABLE IF EXISTS `tb`;

CREATE TABLE IF NOT EXISTS `tb`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO tb VALUES(null);

INSERT INTO tb VALUES(null);

file

得出的结论是 主键冲突,当然这条数据也不会插入进去了。

file

所以在以后的建表过程中要衡量你的业务会不会超出这个量级。如果会那么久需要创建 8 字节的 INT 类型了,他的值最大是 2^64-1

那么问题又来了,你说 我有些业务是不需要主键 、不需要自增编号,我不创建这个字段,就好了,这样想恭喜你 回答错误.

如果你创建的 InnoDB 表没有指定主键,那么 InnoDB 会给你创建一个不可见的,长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值,所有无主键的 InnoDB 表,每插入一行数据,都将当前的 dict_sys.row_id 值作为要插入数据的 row_id,然后把 dict_sys.row_id 的值加 1。

实际上,在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是,InnoDB 在设计时,给 row_id 留的只是 6 个字节的长度,这样写到数据表中时只放了最后 6 个字节,所以 row_id 能写到数据表中的值,就有两个特征:

  • row_id 写入表中的值范围,是从 0 到 2^48-1
  • dict_sys.row_id=2^48时,如果再有插入数据的行为要来申请 row_id,拿到以后再取最后 6 个字节的话就是 0。 也就是说,写入表的 row_id 是从 0 开始到 2^48-1。达到上限后,下一个值就是 0,然后继续循环。

当然,2^48-1 这个值本身已经很大了,但是如果一个 MySQL 实例跑得足够久的话,还是可能达到这个上限的。在 InnoDB 逻辑里,申请到 row_id=N 后,就将这行数据写入表中;如果表中已经存在 row_id=N 的行,新写入的行就会覆盖原有的行。

总结: 自增 ID 用完 会报主键冲突、数据插入失败。

不指定主键、默认创建的 row_id 会 覆盖原有的数据。

暂无回复
0 / 180
网站公告

LaravelCode 正式上线啦

--->>>体验新站

今日诗词
正在加载今日诗词....
标签云
PHP Laravel API 随想 Node.js Linux Yii2.0 Redis 队列 JavaScript Vue Java MySQL 微信小程序 Python Hyperf 转载