这里只记录 innodb 引擎的实现逻辑

MySQL ≤ 5.7#

自增值保存在内存中，在第一次打开表时，会找到 max(id)，会将 max(id) + 1 作为这个表当前的自增值。所以，如果 mysql 重启，可能会导致自增值变化。举例 table x id 如下，当前最大 id 为10，如果立马删除 id = 10 的数据，然后重启 mysql 实例，重启后，table x 的自增值会重新变为10（max(id) + 1 = 9 + 1）。

table x

id
1
2
…
9
10

MySQL ≥ 8.0#

自增值会在 redo log 中持久化，mysql 重启后会从 redo log 中恢复自增值

MySQL 主键自增值修改机制#

id_increament_test 初始情况

1
mysql> show create table id_increament_test;
2
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3
| Table              | Create Table                                                                                                                                                                       |
4
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5
| id_increament_test | CREATE TABLE `id_increament_test` (
6
  `id` int NOT NULL AUTO_INCREMENT,
7
  PRIMARY KEY (`id`)
8
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
9
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

插入时，id = 0 或者 nul，主键id会按照当前自增值来赋值

1
insert into id_increament_test values (0),(null)

1
mysql> select * from id_increament_test;
2
+-----+
3
| id  |
4
+-----+
5
|   1 |
6
|   2 |
7
+-----+
8

9
mysql> show create table id_increament_test;
10
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11
| Table              | Create Table                                                                                                                                                                       |
12
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13
| id_increament_test | CREATE TABLE `id_increament_test` (
14
  `id` int NOT NULL AUTO_INCREMENT,
15
  PRIMARY KEY (`id`)
16
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
17
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

假设当前自增值为 Y，插入时，如果指定了id值 X（X ≠ 0，并且 X值不与数据库中的任何值冲突），分为两种情况:
1. X < Y，插入数据 id = X，自增值不变

1
insert into id_increament_test values (-1)

1
mysql> select * from id_increament_test;
2
+-----+
3
| id  |
4
+-----+
5
|  -1 |
6
|   1 |
7
|   2 |
8
+-----+
9

10
mysql> show create table id_increament_test;
11
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12
| Table              | Create Table                                                                                                                                                                       |
13
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14
| id_increament_test | CREATE TABLE `id_increament_test` (
15
  `id` int NOT NULL AUTO_INCREMENT,
16
  PRIMARY KEY (`id`)
17
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
18
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1
2. X ≥ Y，插入数据 id = X，自增值变为 Y = X+(自增步长)

1
insert into id_increament_test values (100)

1
mysql> select * from id_increament_test;
2
+-----+
3
| id  |
4
+-----+
5
|  -1 |
6
|   1 |
7
|   2 |
8
| 100 |
9
+-----+
10

11
mysql> show create table id_increament_test;
12
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13
| Table              | Create Table                                                                                                                                                                       |
14
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15
| id_increament_test | CREATE TABLE `id_increament_test` (
16
  `id` int NOT NULL AUTO_INCREMENT,
17
  PRIMARY KEY (`id`)
18
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
19
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

📎 参考文章#

https://javaguide.cn/database/mysql/mysql-auto-increment-primary-key-continuous.html

MySQL主键自增

MySQL ≤ 5.7#

MySQL ≥ 8.0#

MySQL 主键自增值修改机制#

📎 参考文章#

文章分享