MySQL 自增主键，你以为很简单？这6个坑90%的开发者都踩过！

俊才

发布于 2026-01-07 17:38:29

1740

文章被收录于专栏：数据库干货铺数据库干货铺

主键用自增 ID，简单又高效？小心这些隐藏陷阱！

在使用MySQL的过程中，自增主键（AUTO_INCREMENT）是最常用、也最容易被“想当然”的功能之一。

很多人觉得：“不就是主键自动加1吗？能有什么问题？”

但你是否遇到过这些问题：

事务回滚后，ID 居然“跳号”了？
服务器重启，ID 突然从 10000 开始？
主从复制突然数据不一致？
明明只插了 10 条数据，ID 却到了 1000？

现实使用自增主键背后藏的专业些“暗雷”，轻则数据错乱，重则线上事故！今天我们就来盘点一下AUTO_INCREMENT 的真实机制，并盘点那些让你深夜加班的“经典坑点”。

1. InnoDB中AUTO_INCREMENT的实现机制

1.1 AUTO_INCREMENT 计数器的存储方式演进

版本	存储位置	持久性	并发控制
MySQL 5.7 及以前	内存 + 表定义缓存	非持久（重启后通过 SELECT MAX(id)+1 重建）	表级锁（AUTO-INC Lock）
MySQL 8.0+	redo log + 数据字典表	持久化	轻量级互斥锁（mutex），支持连续/交错模式

MySQL 8.4 延续 8.0 的设计：AUTO_INCREMENT 值在崩溃恢复后不会“回退”，避免了旧版本重启后 ID 重复或跳号的问题。

可以查询建表语句或数据字典看到自增计数值，例如：

mysql> create  table auto_tb(id int primary key auto_increment,c1 varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into auto_tb(c1)  values('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into auto_tb(id,c1)  values(5,'d');
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql> show create table auto_tb;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.tables where table_schema='testdb' and table_name='auto_tb';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION    | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
| def           | testdb       | auto_tb    | BASE TABLE | InnoDB |      10 | Dynamic    |          4 |           4096 |       16384 |               0 |            0 |         0 |              6 | 2026-01-04 18:39:19 | 2026-01-04 18:42:22 | NULL       | utf8mb3_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------------+
1 row in set (0.01 sec)

如图：

1.2 三种插入模式（innodb_autoinc_lock_mode）

该参数控制并发插入时的锁行为，默认值为 1（交错模式）：

mysql> show global  variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

模式	值	行为	并发性	主从安全
传统模式	0	所有INSERT 持有表级AUTO-INC锁直到语句结束	低	安全（语句级确定性）
交错模式（默认）	1	简单插入（如 INSERT ... VALUES）批量预分配；批量插入（如 INSERT ... SELECT）仍用表锁	中	基于行格式（ROW）复制才安全
无锁模式	2	所有插入均不持锁，ID 分配完全交错	高	仅在行格式（ROW）时安全

⚠️ 注意：若使用 STATEMENT 格式的 binlog，innodb_autoinc_lock_mode=2 会导致主从数据不一致！ MySQL 8.0之后默认 binlog_format=ROW，因此默认配置是安全的。

2. 常见“坑点”与原理分析

2.1 事务回滚不会回滚 AUTO_INCREMENT 值

如果在事务中进行插入操作，但是后续事务回滚了，那么数据回滚后，AUTO_INCREMENT不会回滚，而是事务中已分配的最大值的下一个值。

例如：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO auto_tb(c1) VALUES ('tttttt'); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_tb; -- 新增的id是6
+----+--------+
| id | c1     |
+----+--------+
|  1 | a      |
|  2 | b      |
|  3 | c      |
|  5 | d      |
|  6 | tttttt |
+----+--------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from auto_tb; 
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql> INSERT INTO auto_tb(c1) VALUES ('eee'); -- 回滚后下次id是7 而不是6
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  7 | eee  |
+----+------+
5 rows in set (0.00 sec)

原因：为避免死锁和提升并发，InnoDB 在分配 AUTO_INCREMENT 值后立即释放锁，即使事务未提交。该值一旦分配即“消耗”，不可回收
影响：ID 不连续是正常现象，不要依赖 ID 连续性做业务逻辑（如分页、计数）

2.2 数据库重启后ID 可能重复分配重启前已分配的id（旧版本问题，8.0+ 已修复）

在MySQL5.7版本中，数据库重启后自增id的值会根据表里的数据id的最大值+1获取，如果重启前，原先表里的最后几个id(比最终id大的id被删除或回滚的事务用到的id）会重复分配，不过MySQL8.0中已修复此问题，因为两个版本的id恢复方式不一样：

MySQL 5.7：重启后执行 SELECT MAX(id) + 1 重建计数器。若最后几条记录被删除，新 ID 可能重复！例如：

重启前

mysql> select  * from  auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  7 | eee  |
+----+------+
5 rows in set (0.00 sec)
mysql> select  version();
+---------------+
| version()     |
+---------------+
| 5.7.38-41-log |
+---------------+
1 row in set (0.00 sec)
mysql> select  * from  auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  7 | eee  |
+----+------+
5 rows in set (0.00 sec)
mysql> delete from  auto_tb where id=7;
Query OK, 1 row affected (0.01 sec)
mysql> show table  status like 'auto_tb';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| auto_tb | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              8 | 2026-01-04 19:18:48 | 2026-01-04 19:19:58 | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

如图：

重启后：

mysql> select  * from  auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.01 sec)

mysql> select  version();
+---------------+
| version()     |
+---------------+
| 5.7.38-41-log |
+---------------+
1 row in set (0.00 sec)

mysql> show table  status like 'auto_tb';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| auto_tb | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |            0 |         0 |              6 | 2026-01-04 19:18:48 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

MySQL 8.0+：AUTO_INCREMENT 值写入 redo log 并持久化到数据字典，重启后直接恢复，不会重复也不会回退。

例如：

重启前：

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  7 | eee  |
+----+------+
5 rows in set (0.00 sec)

mysql> delete from auto_tb where id=7;
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.00 sec)

mysql> show table status like 'auto_tb'; -- 此处有问题
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| auto_tb | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              6 | 2026-01-04 18:39:19 | 2026-01-04 18:42:22 | NULL       | utf8mb3_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.39    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table auto_tb;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.01 sec)

重启后：

mysql> restart;
Query OK, 0 rows affected (0.03 sec)

mysql> use testdb;
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

Database changed
mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+
4 rows in set (0.01 sec)

mysql> show create table auto_tb;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show table status like 'auto_tb'; --此处还没更新，但是插入的值没问题，因此又回到了前文说的统计信息问题
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| auto_tb | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              6 | 2026-01-04 18:39:19 | 2026-01-04 18:42:22 | NULL       | utf8mb3_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO auto_tb(c1) VALUES ('eee');
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_tb;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
|  8 | eee  |
+----+------+
5 rows in set (0.00 sec)

注意，以上统计信息值不准确，但是MySQL8.0中可以插入时分配的值是正常的。问题排查可以参考前文进行分析

SHOW TABLE STATUS显示行数为0但SHOW INDEX却显示几十万？--MySQL InnoDB 统计信息源码解析

2.3 显式插入大值会重置计数器


mysql> INSERT INTO auto_tb(id, c1) VALUES (1000, 'tt');
Query OK, 1 row affected (0.00 sec)

-- 此后自动插入的 id 将从 1001 开始

mysql> INSERT INTO auto_tb( c1) VALUES ( 'ttt');
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_tb;
+------+------+
| id   | c1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    5 | d    |
| 1000 | tt   |
| 1001 | ttt  |
+------+------+
6 rows in set (0.00 sec)

原因：这是设计行为，AUTO_INCREMENT 计数器始终为 MAX(当前最大ID, 显式插入值) + 1。
风险：若误插极大值（如 999999999），可能导致后续 ID 快速耗尽（对 INT 类型）。

2.4 批量插入导致 ID 不连续（尤其在 lock_mode=1/2）

INSERT  INTO users (name) 
SELECT name FROM temp_users; -- 假设返回 1000 行

InnoDB 会预分配一段 ID 范围（如 100~1100），即使部分插入失败，已分配的 ID 也不会回收。若中途失败，后续插入从 1101 开始，中间出现“空洞”。

✅ 这是性能与一致性权衡的结果，无法避免。

2.5 主从复制中的 ID 冲突（当配置不当）

若主库使用 innodb_autoinc_lock_mode=2 且 binlog_format=STATEMENT：

主库并发插入顺序不确定 → 从库重放时分配不同 ID → 主从数据不一致！

✅ 解决方案：

强制使用 binlog_format=ROW（MySQL 8.0后默认）

或保持 innodb_autoinc_lock_mode=1（默认）

2.6 ALTER TABLE AUTO_INCREMENT 可以重置自增id?

ALTER TABLE auto_tb AUTO_INCREMENT = 1;

该语句仅当指定值 > 当前最大 ID 时生效。

若表中有 id=1000，执行 AUTO_INCREMENT=1 无效，下次插入仍为 1001。

例如：


mysql> select * from auto_tb;
+------+------+
| id   | c1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    5 | d    |
| 1000 | tt   |
| 1001 | ttt  |
+------+------+
6 rows in set (0.00 sec)

mysql> show create table auto_tb;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE auto_tb AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table auto_tb;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

✅ 正确清空并重置的方法如下：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table auto_tb' at line 1
mysql> delete  from  auto_tb;
Query OK, 6 rows affected (0.01 sec)

mysql> show create table auto_tb;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE auto_tb AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table auto_tb;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_tb | CREATE TABLE `auto_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

或者直接truncate table的方式

TRUNCATE TABLE auto_tb ; -- 会重置 AUTO_INCREMENT 为 1

💡 TRUNCATE 是 DDL，会重建表并重置计数器；DELETE 是 DML，不会。

3. 小结

AUTO_INCREMENT 虽简单易用，但其背后涉及并发控制、持久化、复制一致性等复杂机制。在 MySQL 8.0 之后的版本中，得益于持久化计数器和默认 ROW 格式 binlog，大多数历史坑点已被规避。但开发者仍需理解其“非连续性”、“不可回滚”、“预分配”等本质特性，才能在高可用、高并发系统中安全使用。

关于自增AUTO_INCREMENT也有其他不少的案例（例如id用尽如何处理等有不少其他优秀作者写过，本文不再赘述）。例如

MySQL自增ID用完会怎样？

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-01-04，如有侵权请联系 cloudcommunity@tencent.com 删除

auto