关于自增id 你可能还不知道

导读：在使用 MySQL 建表时，我们通常会创建一个自增字段(AUTO_INCREMENT)，并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。

注： 本文所讲的都是基于 Innodb 存储引擎。

1.MySQL为什么建议将自增列id设为主键？

InnoDB

综上而言：当我们使用自增列作为主键时，存取效率是最高的。

2.自增列id一定是连续的吗？

自增id是增长的 不一定连续。

我们先来看下MySQL 对自增值的保存策略：

InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。

也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。

在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

造成自增id不连续的情况可能有：

• 1.唯一键冲突
• 2.事务回滚
• 3.insert ... select语句批量申请自增id

3.自增id有上限吗？

自增id是整型字段，我们常用int类型来定义增长id，而int类型有上限 即增长id也是有上限的。

下表列举下 int 与 bigint 字段类型的范围：

从上表可以看出：当自增字段使用int有符号类型时，最大可达2147483647即21亿多；使用int无符号类型时，最大可达4294967295即42亿多。当然bigint能表示的范围更大。

下面我们测试下当自增id达到最大时再次插入数据会怎么样：

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// 成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'
复制代码

从实验可以看出，当自增id达到最大时将无法扩展，第一个 insert 语句插入数据成功后，这个表的AUTO_INCREMENT 没有改变（还是 4294967295），就导致了第二个 insert 语句又拿到相同的自增 id 值，再试图执行插入语句，报主键冲突错误。