记一次MySQL自增ID用完了怎么办

记一次MySQL自增ID用尽后引发的错误

MySQL自增ID用完了一般分两种情况，第一种情况是MySQL表有主键；第二种情况是MySQL表没有主键。

1. 有主键

通常，我们设置主键的的时候一般都会把主键设置为自增。

MySQL里的int类型是4个字节，有符号位的话就是-2^31 ，
2 ^ 31-1如果没有符号位的话最大值就是2^32-1也就是2194967295。

环境准备：

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4;

测试：

插入一条数据之后达到最大值。

INSERT INTO t_user(username) VALUES('张三');

可以插入成功。这样表里就有了一条有符号位的最大值上限的数据。

当再次执行，会报主键冲突。

INSERT INTO t_user(username) VALUES('张三');

INSERT INTO t_user(username) VALUES('张三')
> 1062 - Duplicate entry '2147483647' for key 't_user.PRIMARY'
> 时间: 0.001s

也就是说，如果设置了自增主键，当达到自增主键的上限的话，会报主键冲突的。

解决方案：MySQL自增主键类型设置为bigint类型，也就是8个字节。

2. 没有主键

如果没有设置主键的话，InnoDB会自动帮我们创建一个6字节的row_id作为主键。row_id是没有符号位的，所以最大值为2^48 -1。

当row_id超出最大限制，会覆盖之前的数据。这是我们不可以接受的。

总结

当MySQL自增ID用完之后，分两种情况：

• 当设置了主键，将会报主键冲突
• 当没有设置主键，数据库会帮我们自动生成一个全局的row_id，新数据会覆盖老数据

解决方案

• 设置主键的情况，修改自增主键的类型为bigint，因为int类型占4个字节，最大到2^32-1，大约是21亿，bigint占8字节, 大约2^64。
• 没有设置主键的情况，row_id虽然可以达到281万亿，但是会覆盖老的数据，不建议使用。