面试中被问，mysql的自增ID用完了该怎么办？

vlambda
2020-09-11

面试中被问，mysql的自增ID用完了该怎么办？

这块知识点想必有很多同学不了解，下面动手尝试下

首先，使用Navicat新建一个最简单的表，包含一个自增的id，然后插入一条数据。

create table t0(id int unsigned auto_increment primary key) ;

insert into t0 values(null);

使用show命令 show create table t;

可以看到表情况

CREATE TABLE `t0` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

可以看到AUTO_INCREMENT 已经自动变成2，这离主键自增完还有很长距离，我们可以计算下当前声明的自增ID最大数是多少，由于这里定义的是 unsigned int ，所以最大可以达到2的32幂次方 - 1 = 4294967295

这里有一个小技巧，就是在创建表的时候，直接声明AUTO_INCREMENT的初始值

create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967295;

insert into t1 values(null);

同样，通过show命令，查看t1的表结构

CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

可以看到，AUTO_INCREMENT已经变成4294967295了，当尝试再插入一条数据时，得到了下面的异常结果

insert into t1 values(null) Error Code: 1062. Duplicate entry '4294967295' for key 'PRIMARY' 0.00054 sec

表明，当第二次次插入时，使用的自增ID还是 4294967295，报主键冲突的错误。

这说明 ID 值达到上限之后，就不会再变化了。下面介绍下一些解决办法

简单版

4294967295，这个数字已经可以满足大部分的业务场景了，如果预测到 ID 值可能会被耗尽，则可以考虑选择 bigint unsigned 类型。

BigInt的值范围如下

面试中被问，mysql的自增ID用完了该怎么办？

就算你每秒1000000条数据，跑了100年，单个表的数据也只有 10000*24*3600*365*100=3153600000000000，这数字距离BigInt的上限还差很多，所以当你将自增ID设为BigInt类型，你是不用考虑自增ID会超过最大值的这个问题！然而，如果你在面试中的回答

你:"这个简单，把自增主键的类型改为BigInt就可以了！"

接着，面试官可能会问你一个更坑的问题！

面试官:"线上你们是怎么修改列的数据类型的？" 你:"what！我还是回去等通知吧！"

怎么改

据我所知，目前业内在线修改表结构的方案一般有如下三种

方式一:使用mysql5.6+提供的在线修改功能

所谓的mysql自己提供的功能也就是mysql自己原生的语句，比如我们要修改原字段名称及类型。

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

那么，在mysql5.5这个版本之前，这是通过临时表拷贝的方式实现的。执行ALTER语句后，会新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。在5.6+开始，mysql支持在线修改数据库表，在修改表的过程中，对绝大部分操作，原表可读，也可以写。

那么，对于修改列的数据类型这种操作，原表还能写么？来来来，我特意去官网找了mysql8.0版本的一张图

如图所示，对于修改数据类型这种操作，是不支持并发的DML操作！也就是说，如果你直接使用ALTER这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(DELETE、UPDATE、DELETE)。因此，直接ALTER是不行滴！

那我们只能用方式二或者方式三

方式二:借助第三方工具

业内有一些第三方工具可以支持在线修改表结构，使用这些第三方工具，能够让你在执行ALTER操作的时候，表不会阻塞！比较出名的有两个 - 1、pt-online-schema-change，简称pt-osc - 2、GitHub正式宣布以开源的方式发布的工具，名为gh-ost

以pt-osc为例，它的原理如下

1、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
2、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。
3、拷贝数据，从源数据表中拷贝数据到新表中。
4、rename源数据表为old表，把新表rename为源表名，并将old表删除。
5、删除触发器。

然而这两个有意(KENG)思(B)的工具，居然。。。居然。。。唉！如果你的表里有触发器和外键，这两个工具是不行滴！如果真碰上了数据库里有触发器和外键，只能硬杠了，请看方式三

方式三:改从库表结构，然后主从切换

此法极其麻烦，需要专业水平的选手进行操作。因为我们的mysql架构一般是读写分离架构，从机是用来读的。我们直接在从库上进行表结构修改，不会阻塞从库的读操作。改完之后，进行主从切换即可。唯一需要注意的是，主从切换过程中可能会有数据丢失的情况！

高深版

其实答完上面的问题后，这篇文章差不多完了。但是，还记得我在开头说的么。这是一个很有意(KENG)思(B)的问题，为什么呢？

假设啊，你的表里的自增字段为有符号的Int类型的，也就是说，你的字段范围为-2147483648到2147483648。一切又那么刚好，你的自增ID是从0开始的，也就是说，现在你的可以用的范围为0～2147483648。我们明确一点，表中真实的数据ID，肯定会出现一些意外，ID不一定是连续的。例如，有如下情形的出现

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;

执行下列SQL

insert into t values(null);
// 插入的行是 (1)
begin;
insert into t values(null);
rolllack;
insert into t values(null);
// 插入的行是 (3)

所以，表中的真实id必然会有不连续的情况。那么你的自增主键id的数据范围为0～2147483648，也就是21亿条单表数据！考虑id会有不连续，实际的数据顶多18亿条吧。老哥，单表都有18亿条了，还不分库分表？一旦你分库分表了，就不能依赖于每个表的自增ID来全局唯一标识这些数据了。此时，我们就需要提供一个全局唯一的ID号生成策略来支持分库分表的环境。