MySQL / 系统课时
主键与自增字段
主键与自增字段
本课解决两件经常同时出现的问题:怎样为表中的每一行建立稳定、唯一的身份标识,以及怎样让这个标识在插入新记录时自动生成。掌握 PRIMARY KEY 与 AUTO_INCREMENT 后,可以设计出易维护的基础表结构。
学习目标
- 理解主键必须满足的核心性质,并能选择合适的主键列
- 掌握
AUTO_INCREMENT的基本用法与插入方式 - 能验证主键冲突、自增编号生成和手动指定编号的行为
主键解决什么问题
主键用于唯一标识表中的一行记录。一个表只能定义一个主键约束,但主键可以由一列组成,也可以由多列组成。
单列主键通常最容易使用,例如订单表可以把 id 作为主键。主键值必须唯一且不能为 NULL,因此它同时承担了身份标识和完整性约束的作用。
选择主键时,优先考虑稳定、唯一、不会因为业务规则变化而需要修改的字段。手机号、邮箱这类业务字段即使当前看起来唯一,也可能发生变更,因此很多业务表会额外使用独立的数值型主键。
定义自增主键
下面使用常见的 BIGINT 自增主键。插入时不提供 id,由 MySQL 为新行生成编号。
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO orders (customer_name)
VALUES ('Alice'), ('Bob');
SELECT id, customer_name
FROM orders
ORDER BY id;预期结果:
Query OK, 0 rows affected
Query OK, 2 rows affected
+----+---------------+
| id | customer_name |
+----+---------------+
| 1 | Alice |
| 2 | Bob |
+----+---------------+AUTO_INCREMENT 的职责是生成新的编号;真正保证编号不能重复、不能为 NULL 的,是 PRIMARY KEY。两者经常一起出现,但概念上并不是同一个约束。
自增值与插入方式
最常见的写法是省略自增列。也可以显式指定一个合法的 id,但这会改变后续编号分配时的状态,因此业务代码通常不应随意手工写入自增主键。
删除某一行也不会意味着后续插入一定会重新使用这个编号。自增字段的目的不是保证编号连续,而是方便生成新的标识。因此不要把“没有空号”当成主键设计目标。
例如,先删除编号为 2 的记录,再插入新记录,业务上应接受出现新编号而不是强求重新使用 2。
DELETE FROM orders
WHERE id = 2;
INSERT INTO orders (customer_name)
VALUES ('Carol');
SELECT id, customer_name
FROM orders
ORDER BY id;预期结果:
Query OK, 1 row affected
Query OK, 1 row affected
+----+---------------+
| id | customer_name |
+----+---------------+
| 1 | Alice |
| 3 | Carol |
+----+---------------+实际新编号可能受已有数据、自增计数状态以及插入方式影响,因此不能依赖“编号连续”来表达业务顺序。
主键设计中的常见取舍
数值型自增主键的优点是结构简单、生成成本低、使用方式直观。缺点是它本身只表达数据库内部身份,不携带业务含义,因此查询时通常还需要其他业务唯一字段。
如果业务本身存在天然稳定的唯一键,也可以讨论使用业务字段作为主键;但只要该字段可能修改、长度较大,或者会出现在大量关联表中,就需要额外评估维护成本。
复合主键适合“多个字段组合起来才唯一”的场景,例如明细表中的 (order_id, product_id)。它解决的是组合唯一性,但会让外键和 SQL 条件更复杂,因此不能为了少建一个字段而随意采用。
设计基础表时,可以把 PRIMARY KEY 看作“这一行是谁”,把 AUTO_INCREMENT 看作“这个身份编号怎么自动产生”。只要先分清这两个职责,后续索引、外键和业务唯一约束就更容易设计。