MariaDB 教程 / 使用 PRIMARY KEY、AUTO_INCREMENT 和 UNIQUE 标识行

PRIMARY KEY 唯一标识一行,UNIQUE 限制其他不能重复的值或列组合,AUTO_INCREMENT 则生成整数编号。自动生成不能替代键定义,也不保证编号连续无缺口。应根据行的身份与业务唯一规则,将这些功能有意组合,而不是把它们当成同一个概念。

区分行标识与业务唯一规则

不同客户可以同名,所以姓名通常不适合作为行标识。本例用生成的客户编号作为主键,再把电子邮件唯一性作为单独规则。表结构定义教程说明了这些声明的位置;NULL 与输入约束教程则负责解释必填和取值条件。唯一并不自动等于必填,也不证明数据本身真实有效。

功能 职责 重要条件
PRIMARY KEY 标识一行 每表一个主键定义;组成列不能为 NULL,组合不能重复
UNIQUE 拒绝重复值或重复列组合 每表可以有多个定义;可空唯一列可以有多个 NULL
AUTO_INCREMENT 省略编号时生成整数 每表最多一个自增列,必须被索引,可能存在编号空缺

“一个主键”不等于“主键只能有一列”,复合主键是由多列构成的一个定义。AUTO_INCREMENT 并非在所有设计中都必须是主键;对于本篇入门例子,单个整数主键是一种清楚的起点。键的选择仍应结合业务标识是否稳定,以及以后其他表如何引用它。

运行客户与键的完整示例

通过命令行连接与 SQL 文件教程,用 course_user 连接练习服务器,确认 shopcourse 可用。在客户端内执行:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

selected_database 应为 shopcourse。本练习使用 customers_key_lab、product_codes_key_lab 和 auto_gap_lab 三张独立表,先确认名称没有被要保留的数据占用。将下面的完整正常示例保存为 UTF-8 SQL 文件,使用 SOURCE 与实际本机路径运行;故意报错的语句另行执行。

客户表允许邮箱暂时未知,因此 email 可以为 NULL。主键列明确声明 NOT NULL,但插入客户时省略它,让服务器生成值。以下初始编号预期基于新表,以及 auto_increment_increment、auto_increment_offset 均为 1;特殊复制环境可能使用其他设置。

USE shopcourse;
CREATE TABLE customers_key_lab (
  customer_id INT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(254) NULL,
  PRIMARY KEY (customer_id),
  CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
  ('Alex Chen', 'alex@example.com'),
  ('Sam Patel', NULL),
  ('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
  supplier_id INT NOT NULL,
  supplier_code VARCHAR(30) NOT NULL,
  label VARCHAR(100) NOT NULL,
  PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
  (1, 'N001', 'Notebook'),
  (2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;
customer_id customer_name email
1 Alex Chen alex@example.com
2 Sam Patel NULL
3 Jordan Lee NULL

新表中编号为 1、2、3。UNIQUE(email) 允许两条 NULL 邮箱记录,并不要求客户必须提供邮箱。如果业务要求必填,还需合适的 NOT NULL 规则;对旧表增加必填约束之前,先检查并处理现有 NULL 数据。

完整示例还创建供应商商品代码的复合主键表,以及观察自增空缺的独立表。英文标签保留为练习数据,example.com 邮箱也只是示例字符串,不用于实际发送邮件。定义可查看 AUTO_INCREMENT 官方参考与 PRIMARY KEY 和 UNIQUE 规则。

分别测试重复邮箱和重复主键

正常文件运行后,将下面两条预期失败语句分别执行。第一条重复已有邮箱:

INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');

第二条显式重复客户编号 1:

INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');

观察错误中指出的键名:邮箱唯一规则与 PRIMARY 并不是同一个键。任一失败后,执行 SELECT COUNT(*) FROM customers_key_lab;,应仍为三行。不要立即改成 INSERT IGNORE 或 REPLACE 来掩盖冲突;REPLACE 可能替换现有冲突行,并不是无副作用地跳过错误。

邮箱列的排序规则会影响重复判断。utf8mb4_unicode_ci 下,将已有邮箱改写为 ALEX@example.com,仍可能被视为重复。不同保存拼写不保证能通过 UNIQUE,应明确应用的标准化与比较策略。字符串与排序规则比较提供了这一差异的背景。

理解多列组成的一个主键

两个供应商都可以使用 N001 作为自己的商品代码。如果只把 supplier_code 设成主键,第二个供应商的正常数据也会被拒绝。因此完整示例将 (supplier_id, supplier_code) 作为主键:

supplier_id supplier_code 结果
1 N001 供应商 1 的代码
2 N001 允许,因为组合不同
1 再次使用 N001 拒绝,因为组合相同

单独运行下面的重复组合测试,应出现主键重复错误:

INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');

复合键保证组合唯一,不要求每个组成列各自唯一。同一供应商可以有多个代码,不同供应商也可以共享一个代码。查询与外键引用可能需要完整组合。如果业务代码会变更,也可以评估稳定的生成标识加独立 UNIQUE 组合,而不是仅图方便就不断扩展主键。

接受自动编号出现空缺

auto_gap_lab 部分先在事务中插入一行,执行 ROLLBACK,再保存另一行。在本例新 InnoDB 表和默认自增步长下,最后保存的记录编号为 2,label 为 Saved row。第一条 Rolled-back row 没有保存,但它已预留的编号不会因此退回序列。

删除、失败插入和并发操作也可能造成空缺。AUTO_INCREMENT 不表示记录数量,也不保证发票等业务编号连续无缺口。要统计行数使用 COUNT(*);MAX(id) 只表示当前可见的最大编号。例如本例 auto_gap_lab 最后只有一行,但最高编号为 2。

要取得生成值,应在相关 INSERT 成功后立即在同一连接调用 LAST_INSERT_ID()。多行插入时,它通常返回该语句成功生成的第一个编号,不是最后一行编号;另一连接的插入不会变成当前连接的结果。前一语句失败后,也不能用读到的值证明插入成功。具体规则见 LAST_INSERT_ID 官方说明。

自己计算 MAX(id)+1,可能让并发客户端选中相同的下一个数,不能替代可靠的编号生成。若业务确实要求不同性质的连续编号,需要单独设计并发与失败处理规则,不应误把自增列的含义扩大。

检查实际键并仅重置独立练习表

SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;

确认 PRIMARY 与 uq_customers_key_email 是两个独立键,并核对供应商表的主键按预期包含两列。DESCRIBE 的 Extra 字段与 SHOW INDEX 回答不同问题:生成值属性和唯一性不是同一功能。

重做前用 SELECT DATABASE(),并分别对三张实际目标表执行 SHOW CREATE TABLE,确认它们确实是本文可丢弃的对象。要保留的数据先备份,确认后仅删除这三张表:

DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;

DROP TABLE 会移除这些表及其行,普通 ROLLBACK 不能恢复。之后可重新执行完整正常示例。首次商品练习中的 products 表不属于此次清理范围,其他客户或订单表也不应随之修改。