碎碎片片

0%

MySQL中表自增id用完了是否还能插入数据？

发表于 分类于
虽然MySQL中表自增id一般来说够用了，但是如果万一用完了，还能不能继续插入新数据呢？

从未考虑过自增id能用完这个问题，最近看到相关文章，那么我也来做个实验，看看具体情况。

一、实验环境

  • 1、MySQL版本

    1
    2
    3
    4
    5
    6
    7
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.18    |
    +-----------+
    1 row in set (0.01 sec)

  • 2、实验表建表语句

    1
    2
    3
    4
    CREATE TABLE `t` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4294967294 DEFAULT CHARSET=utf8

二、相关内容

  • 1、自增id

在MySQL的InnoDB引擎中，一般来说，建表时都建议定义一个自增id作为其主键值，其优点在于：
1.自增id的有序递增；
2.有序递增的主键使得插入数据时，不会导致数据页分裂次多过多，造成数据页碎片化严重；
3.聚簇索引查询时，根据有序递增的顺序查询更快速；
4.数据页碎片化不严重，就不会浪费更多的磁盘空间。

实验表中，我设置实验表t默认自增从4294967294开始，这是因为int值占4个字节，其unsigned的取值范围为0~4294967295，可以看下正常插入数据到顶点时，会出现什么情况。

三、实验开始

1、插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+

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

可以看到，在插入第二条数据时，id值已经到达了int值的取值范围顶点，此时继续插入数据，则会报错提示：主键重复
那么也就可以得出结论：
当自增主键用完时，将不能再继续插入新数据。MySQL底层在自增主键取值到达顶点时，依旧使用最大值作为主键值插入，所以会报错为主键重复

四、总结

实验很简单的做完了，也得出了疑问中的结论。
MySQL中表自增id用完了，就无法继续成功插入新数据，所以说在生产上，应尽量考虑到这种情况，以免生产环境出现问题时手忙脚乱。
但是，一般来说int unsigned在业务中是足够用的，不需要考虑的太多，如果真的不够用了，那么可以用bigint unsigned（8个字节）来作为自增主键值。

还有一种情况是，当表未指定主键id时：
当MySQL InnoDB数据表未指定主键id时，InnoDB会自动给该表创建一个不可见序列值（记为row_id）作为该表的主键id。
需要注意的是，InnoDB在全局中维护了一个dictsys.row_id，所有未定义主键的表共用该row_id配置，
也就是说，每插入一条数据都将row_id作为主键，然后将全局row_id +1，然后其他表继续使用。

该row_id长度为6字节，使用bigint unsigned类型（使用了该类型，却只用6字节的数据），
这样就会存在一个问题：row_id递增到 2的48次方减1 时row_id再加1就会变为0，存在风险。
而且，前面介绍了主键逐个递增的好处，所以说，最好的方法还是定义一个主键，而不是使用InnoDB创建的自动主键。