MySQL 唯一约束

 唯一约束核心特性

1. 唯一性保证：确保列中所有值都是唯一的

2. NULL值处理：允许NULL值，但只能有一个NULL（因为NULL不等于NULL）

3. 多列约束：一个表可以有多个唯一约束

4. 索引自动创建：MySQL会自动为唯一约束列创建唯一索引

5. 与主键区别：

    主键不允许NULL，唯一约束允许

    每表只能有一个主键，但可有多个唯一约束

    主键通常用于标识记录，唯一约束用于业务唯一性

唯一约束操作详解

创建表时定义唯一约束

单列唯一约束：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

多列组合唯一约束：

CREATE TABLE product_variants (
    product_id INT,
    color VARCHAR(20),
    size VARCHAR(10),
    PRIMARY KEY (product_id, color, size),
    UNIQUE KEY (product_id, color)  -- 特定组合必须唯一
);

修改表添加唯一约束

 添加单列唯一约束

ALTER TABLE employees ADD CONSTRAINT uk_employee_email UNIQUE (email);

 添加多列组合唯一约束

ALTER TABLE orders ADD CONSTRAINT uk_order_customer_date UNIQUE (customer_id, order_date);

删除唯一约束

ALTER TABLE users DROP INDEX uk_user_email;

注意：在MySQL中唯一约束是通过索引实现的，所以使用`DROP INDEX`而非`DROP CONSTRAINT`

唯一约束高级特性

唯一约束与NULL值

 唯一约束允许NULL值

 可以有多个NULL值（因为NULL不等于NULL）

 若需要确保NULL也是唯一的，可添加NOT NULL约束

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_code VARCHAR(10) NOT NULL UNIQUE  -- 不允许NULL
);

条件唯一约束（MySQL 8.0+）

使用函数索引创建条件唯一约束：

 只对非删除记录强制唯一

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    is_deleted TINYINT DEFAULT 0,
    UNIQUE KEY uk_product_name (name, (IF(is_deleted=1, NULL, 1)))
);

唯一约束与外键

唯一约束可以作为外键的引用目标：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);
CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    username VARCHAR(50),
    FOREIGN KEY (username) REFERENCES users(username)
);

唯一约束最佳实践

1. 命名规范：使用`uk_表名_列名`的命名约定

2. 业务逻辑：

    为业务上需要唯一的字段添加约束（如邮箱、手机号）

    避免过度使用，影响插入性能

3. 性能考虑：

    唯一约束会自动创建索引，提高查询速度

    但会降低插入和更新性能（需要检查唯一性）

4. 错误处理：

    捕获并处理`ER_DUP_ENTRY`错误（错误代码1062）

    提供用户友好的重复值提示

实际应用示例

用户管理系统：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE,  -- 允许NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

添加复合唯一约束

ALTER TABLE user_roles ADD CONSTRAINT uk_user_role 
UNIQUE (user_id, role_id);

商品库存系统：

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_product_name_brand (name, brand_id)
);
 
CREATE TABLE inventory (
    warehouse_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (warehouse_id, product_id),
    UNIQUE KEY uk_product_location (product_id, location_code)
);

常见问题解决方案

1. 忽略重复值插入：

INSERT IGNORE INTO users (username, email) VALUES ('john', 'john@example.com');

    或者 

 INSERT INTO users (username, email) 
   VALUES ('john', 'john@example.com')
   ON DUPLICATE KEY UPDATE last_seen = NOW();

2. 替换重复记录：

 REPLACE INTO users (user_id, username, email) 
   VALUES (1, 'john', 'john@example.com');

3. 批量插入处理重复：

  INSERT INTO products (sku, name)
   VALUES ('SKU001', 'Product 1'), ('SKU002', 'Product 2')
   ON DUPLICATE KEY UPDATE name = VALUES(name);