MySQL8新特性：自增主键的持久化详解

前言

自增主键没有持久化是个比较早的bug，这点从其在官方bug网站的id号也可看出（https://bugs.Mysql.com/bug.PHP?id=199）。由Peter Zaitsev（现Percona CEO）于2003年提出。历史悠久且臭名昭著。

首先，直观的重现下。

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

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

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
+----+
rows in set (0.01 sec)