MySQL / 系统课时

主键与自增字段

主键与自增字段

本课解决两件经常同时出现的问题:怎样为表中的每一行建立稳定、唯一的身份标识,以及怎样让这个标识在插入新记录时自动生成。掌握 PRIMARY KEY 与 AUTO_INCREMENT 后,可以设计出易维护的基础表结构。

学习目标

  • 理解主键必须满足的核心性质,并能选择合适的主键列
  • 掌握 AUTO_INCREMENT 的基本用法与插入方式
  • 能验证主键冲突、自增编号生成和手动指定编号的行为

主键解决什么问题

主键用于唯一标识表中的一行记录。一个表只能定义一个主键约束,但主键可以由一列组成,也可以由多列组成。

单列主键通常最容易使用,例如订单表可以把 id 作为主键。主键值必须唯一且不能为 NULL,因此它同时承担了身份标识和完整性约束的作用。

选择主键时,优先考虑稳定、唯一、不会因为业务规则变化而需要修改的字段。手机号、邮箱这类业务字段即使当前看起来唯一,也可能发生变更,因此很多业务表会额外使用独立的数值型主键。

定义自增主键

下面使用常见的 BIGINT 自增主键。插入时不提供 id,由 MySQL 为新行生成编号。

sql
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;

预期结果:

text
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。

sql
DELETE FROM orders
WHERE id = 2;

INSERT INTO orders (customer_name)
VALUES ('Carol');

SELECT id, customer_name
FROM orders
ORDER BY id;

预期结果:

text
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 看作“这个身份编号怎么自动产生”。只要先分清这两个职责,后续索引、外键和业务唯一约束就更容易设计。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

主键与自增字段

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通