在数据库设计中，为每一行数据分配一个唯一标识符是最常见的需求。MySQL 提供了 AUTO_INCREMENT 属性来实现自增主键，这本质上是数据库内置的一种序列生成器。虽然 MySQL 没有像 Oracle、PostgreSQL 那样的独立序列对象，但自增列足以覆盖绝大多数业务场景。理解它的工作机制和边界，能帮你避开很多坑。
10.4.1 AUTO_INCREMENT 的基本用法
在创建表时，只需要给整数类型的主键列加上 AUTO_INCREMENT 关键字，就可以在插入数据时自动生成递增值。
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);插入数据时，可以不指定自增列的值：
INSERT INTO users (username) VALUES ('alice'), ('bob');MySQL 会自动为第一行分配 id = 1，第二行为 id = 2。自增值的初始值默认从 1 开始，步长可通过 auto_increment_increment 和 auto_increment_offset 动态调整，但在单机场景下极少需要修改。
你也可以主动指定自增列的值：
INSERT INTO users (id, username) VALUES (100, 'charlie');这时自增序列会被强制提升到 100。如果下一条插入语句不指定 id，则分配 101。
10.4.2 获取最后插入的自增值
在插入一条记录后，应用端通常需要立即拿到生成的主键值，用于后续逻辑。MySQL 提供了 LAST_INSERT_ID() 函数，这个函数基于当前连接，返回最近一次 INSERT 操作生成的第一个自增值（如果一次插入多行，返回的是第一个新插入的 id）。
INSERT INTO users (username) VALUES ('dave');
SELECT LAST_INSERT_ID(); -- 返回 dave 的 id 值这个函数是连接安全的，不同连接之间不会互相干扰。在使用连接池时需要注意，一定要在同一个连接内、事务提交前调用，否则可能拿到错误的值。大多数 ORM 框架（如 Hibernate、MyBatis）都在插入后自动帮你调用它，你只需关心框架返回的实体 id。
10.4.3 自增值的生成与锁机制
MySQL 为 AUTO_INCREMENT 值的分配设计了精巧的锁机制，以保证并发插入时值不会重复，同时尽量不影响写入性能。这些行为由系统变量 innodb_autoinc_lock_mode 控制，有三种模式：
- 0 – 传统模式：每次 INSERT 都会持有一个表级的
AUTO-INC锁，直到语句执行完毕才释放。这保证了一条语句中生成的值是连续的，但并发写入能力很差，多个 INSERT 会严重串行化。
- 1 – 连续模式（默认值，5.7 和 8.0 均为默认）：对于“简单插入”（能事先确定插入行数的 INSERT，如 INSERT INTO ... VALUES (...), (...)，不带子查询），InnoDB 会先分配好所需数量的一段连续自增值，然后立即释放锁，其他事务可以同时获得另一段连续值。对于“批量插入”（无法事先知道行数的 INSERT，如 INSERT ... SELECT、LOAD DATA），则会退化到类似传统模式，持有表级锁直到语句完成，以保证主从复制时的自增值一致。
- 2 – 交叉模式：所有 INSERT 都不会使用表级 AUTO-INC 锁，而是以更轻量的互斥量按需分配单个值。这带来了最高的并发性能，但会导致一条 INSERT 多行语句生成的值可能不连续，且在主从基于语句复制时可能出现问题，因此只推荐在基于行复制（ROW）且不在意自增空洞的场景下使用。
对于绝大多数部署，保持默认的 innodb_autoinc_lock_mode = 1 是最稳妥的选择，因为它兼顾了并发和自增值的连续性。如果你的业务有极端的写入压力且复制方式是 ROW 模式，可以评估调整为 2 来获得更强的并发插入能力。
10.4.4 自增主键“空洞”问题
自增值并不是严格连续的，以下情况会造成“空洞”，即某些数值被永久跳过：
- INSERT 失败或回滚的事务：事务获取了一段自增值，但因为后续违反约束或应用程序主动 ROLLBACK，这些已分配的值不会回收。
- INSERT IGNORE、INSERT ... ON DUPLICATE KEY UPDATE：这些语句可能先分配了自增值，但最终未插入新行。
- 删除操作：
DELETE或TRUNCATE不会重置自增值（TRUNCATE如果用的是重置的方式会重置，但具体看存储引擎和 SQL 模式）。即使你把某个 id 的记录删掉，这个 id 也不会被重新使用。
空洞在设计上是被接受的，因为回收已用的自增值会带来巨大的并发复杂度和历史数据混乱。因此，不要将自增主键视作“连续的编号”，它只是一个唯一标识符，可以断号但绝不能重复。
如果业务场景必须保证无空洞的连续序列号（比如发票号），自增列就不适合，需要在应用层或通过其他机制（如 Redis 原子递增、独立的序列表）来生成。
10.4.5 自增值的上限与耗尽
自增值的上限取决于你选择的整数类型：
| 类型 | 有符号最大值 | 无符号最大值 |
|------|-------------|-------------|
| TINYINT | 127 | 255 |
| SMALLINT | 32,767 | 65,535 |
| MEDIUMINT | 8,388,607 | 16,777,215 |
| INT | 2,147,483,647 | 4,294,967,295 |
| BIGINT | 9,223,372,036,854,775,807 | 18,446,744,073,709,551,615 |
当自增值达到上限后，再次插入会报错：Duplicate entry 'xxx' for key 'PRIMARY'，因为无法生成新的值。这个问题在实际生产环境中并不罕见，尤其在快速增长的业务里用 INT 作为主键的旧表。
建议从一开始就优先使用 BIGINT UNSIGNED 作为主键类型。对于绝大多数应用，BIGINT 的上限几乎不可能被用完，你不需要在这上面留技术债。如果已经用了 INT 并且即将耗尽，需要尽早规划数据迁移（如改为 BIGINT）或者进行分表、分库，这不是一个能临时救急的问题。
10.4.6 查看与修改自增值
通过 SHOW CREATE TABLE 或 INFORMATION_SCHEMA.TABLES 可以查看当前表的自增值：
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';手动修改自增值的常见方式：
ALTER TABLE users AUTO_INCREMENT = 10000;这会将下一个分配的 ID 提升到 10000（如果当前已有 ID 大于等于 10000，则自动调整到现有最大值 +1）。但要注意，ALTER TABLE 操作会重建整张表，大表慎用。
10.4.7 自增主键的设计建议
最后总结一些实用法则：
- 主键尽量用自增整数，配合聚簇索引，插入数据顺序追加，减少页分裂，写入性能最高。
- 避免使用随机的 UUID 或长字符串作为主键，会导致大量随机 I/O 和页分裂（除非有特殊分布式诉求，但这需要权衡）。
- 永远不要将自增主键作为业务含义，它只负责唯一标识一行数据。如果需要业务编号（如订单号），另建一个唯一索引列，业务编号可以用更复杂的规则生成，避免暴露自增 ID。
- 使用 BIGINT UNSIGNED，避免将来为容量上限发愁。
- 在分布式场景下，自增 ID 会成为一个问题（多个主库可能产生相同 ID），这时需要借助全局 ID 生成器，如 Snowflake 算法、数据库号段模式或者分库分表中间件的全局序列方案。MySQL 本身的自增只能保证单机唯一。
自增主键是 MySQL 中一项看似简单实则设计精巧的特性，理解它的行为，能让你在表设计和写入逻辑上做出更好的决策。