8.3 唯一约束(UNIQUE)不允许重复

      在现实业务中，除了主键之外，我们常常需要对其他列也施加“唯一性”要求。例如，用户的邮箱不能重复、商品的编码必须唯一、身份证号不能相同。这时，就需要使用唯一约束（UNIQUE）。它保证指定列（或列组合）的所有值在表中是唯一的，不能重复出现。

一、什么是唯一约束？

唯一约束 的作用是确保一列（或多列）的值在整个表中没有重复。它与主键非常相似，但有以下重要区别：

理解 NULL 的处理：在 MySQL 中，唯一约束允许列包含 NULL 值，并且认为多个 NULL 是不重复的（即允许存在多行 NULL）。这与 SQL 标准一致。

二、唯一约束的创建方式

可以在建表时定义，也可以后期添加。

1. 列级唯一约束

直接在列定义后面加 UNIQUE 关键字：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,      -- 用户名不能重复
    email VARCHAR(100) UNIQUE,        -- 邮箱不能重复
    phone VARCHAR(20)
);

2. 表级唯一约束

在列定义之后，使用 [CONSTRAINT 约束名] UNIQUE (列名) 单独声明。这种方式可以为一组列创建组合唯一约束，也可以为约束命名。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    UNIQUE (username),                 -- 未命名唯一约束
    CONSTRAINT uk_users_email UNIQUE (email),  -- 命名唯一约束
    UNIQUE (phone)                     -- 再添加一个
);

3. 多列组合唯一约束

当需要保证多个列的组合值唯一时，使用表级语法列出所有列。

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    CONSTRAINT uk_order_product UNIQUE (order_id, product_id)  -- 同一订单中不能重复录入同一商品
);

此时，可以插入 (1, 100) 和 (1, 101)，但不能插入两个 (1, 100)。

4. 建表后添加唯一约束

使用 ALTER TABLE 添加：

ALTER TABLE users ADD UNIQUE (email);
-- 或带约束名
ALTER TABLE users ADD CONSTRAINT uk_users_phone UNIQUE (phone);

5. 删除唯一约束

唯一约束的名字通常和索引名相关，可以通过 SHOW INDEX 查看约束名，然后删除对应的索引。

-- 查看表的索引（包括唯一约束创建的索引）
SHOW INDEX FROM users;

-- 删除唯一约束（实际就是删除索引）
ALTER TABLE users DROP INDEX username;  -- 假设约束名为 username
ALTER TABLE users DROP INDEX uk_users_email;

三、唯一约束的实际应用场景

1、用户表：用户名、邮箱、手机号通常需要唯一。

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE
);

2、商品表：商品编码（SKU）必须唯一。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200),
    price DECIMAL(10,2)
);

3、关联表：保证两个外键的组合不重复。

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),  -- 主键本身就是唯一约束
    -- 如果不用主键，也可以用 UNIQUE 约束
    UNIQUE (user_id, role_id)
);

四、唯一约束的注意事项

1、NULL 值的处理：唯一约束允许 NULL，且多个 NULL 视为不同，可以共存。如果你需要禁止 NULL，可以同时添加 NOT NULL 约束。

email VARCHAR(100) UNIQUE NOT NULL   -- 既唯一又不能为空

2、性能：唯一约束会创建索引，所以查询很快，但插入/更新时需要检查唯一性，会略微降低写入速度。对于高并发场景，需合理设计。

3、与主键的区别：主键强制非空且唯一，唯一约束允许 NULL。一个表可以有多个唯一约束，但只能有一个主键。

4、字符串长度限制：对于长文本列（如 TEXT, BLOB），MySQL 不允许直接添加唯一约束（因为无法为完整内容建立索引）。如果需要保证长文本唯一，可以考虑存储其哈希值并加唯一约束。

5、字符集与排序规则：唯一约束依赖于列的字符集和排序规则。例如，utf8mb4_general_ci 不区分大小写，那么 'abc' 和 'ABC' 会被视为相同，违反唯一性。请根据业务选择适当的排序规则。

五、综合练习

设计一个“员工管理系统”的员工表，包含以下要求：

• 工号（employee_no）必须唯一且不能为空。

• 身份证号（id_card）必须唯一，但允许为空（有些外籍员工可能没有）。

• 邮箱（email）必须唯一且不能为空。

• 手机号（phone）不能重复，且不为空。

• 姓名、部门、入职日期等字段。

请写出建表语句，并尝试插入几条测试数据验证唯一性。

参考答案：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_no VARCHAR(20) NOT NULL UNIQUE,
    id_card VARCHAR(18) UNIQUE,  -- 允许 NULL
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    hire_date DATE
);

-- 测试插入
INSERT INTO employees (employee_no, id_card, email, phone, name, department, hire_date)
VALUES 
('E001', '110101199001011234', 'zhang@company.com', '13800138001', '张三', '技术部', '2023-01-10'),
('E002', NULL, 'li@company.com', '13800138002', '李四', '市场部', '2023-02-15'),
('E003', '110101199001011235', 'wang@company.com', '13800138003', '王五', '技术部', '2023-03-20');

-- 尝试插入重复的工号（应失败）
INSERT INTO employees (employee_no, email, phone, name) VALUES ('E001', 'test@company.com', '13999999999', '测试'); -- 工号重复

-- 尝试插入重复的手机号（应失败）
INSERT INTO employees (employee_no, email, phone, name) VALUES ('E004', 'test2@company.com', '13800138001', '测试'); -- 手机号重复

-- 尝试插入两个 NULL 的身份证号（应成功，因为 NULL 不重复）
INSERT INTO employees (employee_no, email, phone, name, id_card) VALUES 
('E005', 'zhao@company.com', '13800138005', '赵六', NULL),
('E006', 'qian@company.com', '13800138006', '钱七', NULL);

六、总结

• 唯一约束 保证列（或列组合）的值在整个表中唯一，但允许 NULL。

• 它和主键的区别在于：唯一约束可以有多个，且允许 NULL；主键只能有一个，且不允许 NULL。

• 创建方式灵活，可以在列级、表级定义，也可以后期添加。

• 唯一约束会创建索引，提升查询效率，但也会带来写入时的检查开销。

• 实际业务中，常用于用户名、邮箱、手机号、身份证号、编码等需要唯一性的字段。