印刻数据库

外观

MySQL 唯一约束与检查约束 ​

唯一约束 ​

基本概念 ​

  • 唯一约束确保列或列组合中的值是唯一的
  • 允许NULL值，但NULL值不被视为重复
  • 可以在单个列或多个列上创建
  • 自动创建索引以实现唯一性

创建唯一约束 ​

列级唯一约束 ​

sql
-- 在创建表时添加唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- 为现有列添加唯一约束
ALTER TABLE users
ADD UNIQUE (email);

表级唯一约束 ​

sql
-- 创建表时添加表级唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    username VARCHAR(50),
    UNIQUE (email),
    UNIQUE (username)
);

-- 添加多列唯一约束
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id),
    UNIQUE (order_id, product_id)
);

命名唯一约束 ​

sql
-- 创建命名唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE (email)
);

-- 为现有表添加命名唯一约束
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

管理唯一约束 ​

查看唯一约束 ​

sql
-- 查看表的唯一约束
SHOW INDEX FROM users WHERE Non_unique = 0;

-- 通过信息模式查看
SELECT * FROM information_schema.table_constraints 
WHERE table_schema = 'database_name' 
AND table_name = 'users' 
AND constraint_type = 'UNIQUE';

删除唯一约束 ​

sql
-- 删除命名唯一约束
ALTER TABLE users
DROP CONSTRAINT unique_email;

-- 删除未命名唯一约束
ALTER TABLE users
DROP INDEX email;

修改唯一约束 ​

sql
-- 先删除再添加
ALTER TABLE users
DROP INDEX email,
ADD UNIQUE (email);

唯一约束与性能 ​

优势 ​

  • 提高查询性能（通过索引）
  • 确保数据完整性
  • 减少应用层验证

劣势 ​

  • 写入操作性能开销
  • 索引维护成本
  • 可能导致死锁

性能优化 ​

  • 合理设计唯一约束
  • 避免在频繁更新的列上创建唯一约束
  • 考虑使用部分唯一索引（MySQL 8.0+）

检查约束 ​

基本概念 ​

  • 检查约束确保列中的值满足指定条件
  • MySQL 8.0.16+ 支持标准检查约束
  • 之前版本使用触发器实现类似功能
  • 可以在单个列或表级创建

创建检查约束 ​

列级检查约束 ​

sql
-- 在创建表时添加检查约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

-- 为现有列添加检查约束
ALTER TABLE products
ADD CHECK (price > 0);

表级检查约束 ​

sql
-- 创建表时添加表级检查约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    CHECK (quantity > 0),
    CHECK (price > 0)
);

-- 添加多列检查约束
CREATE TABLE employees (
    id INT PRIMARY KEY,
    hire_date DATE,
    birth_date DATE,
    CHECK (hire_date > birth_date)
);

命名检查约束 ​

sql
-- 创建命名检查约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    CONSTRAINT check_price_positive CHECK (price > 0)
);

-- 为现有表添加命名检查约束
ALTER TABLE products
ADD CONSTRAINT check_stock_non_negative CHECK (stock >= 0);

管理检查约束 ​

查看检查约束 ​

sql
-- 通过信息模式查看检查约束
SELECT * FROM information_schema.check_constraints 
WHERE constraint_schema = 'database_name' 
AND table_name = 'products';

-- 查看约束详情
SELECT * FROM information_schema.table_constraints 
WHERE table_schema = 'database_name' 
AND table_name = 'products' 
AND constraint_type = 'CHECK';

删除检查约束 ​

sql
-- 删除命名检查约束
ALTER TABLE products
DROP CONSTRAINT check_price_positive;

-- MySQL 8.0.19+ 支持
ALTER TABLE products
DROP CHECK check_price_positive;

修改检查约束 ​

sql
-- 先删除再添加
ALTER TABLE products
DROP CONSTRAINT check_price_positive,
ADD CONSTRAINT check_price_positive CHECK (price >= 0);

检查约束与触发器 ​

兼容旧版本 ​

sql
-- 在 MySQL 8.0.16 之前使用触发器
DELIMITER //
CREATE TRIGGER before_product_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '价格必须大于0';
    END IF;
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不能为负数';
    END IF;
END//
DELIMITER ;

CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '价格必须大于0';
    END IF;
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不能为负数';
    END IF;
END//
DELIMITER ;

唯一约束与检查约束的最佳实践 ​

设计建议 ​

  • 唯一约束：

    • 用于确保业务唯一性（如邮箱、用户名）
    • 考虑组合唯一约束（如订单号+产品ID）
    • 避免在大型表的宽列上创建唯一约束

  • 检查约束：

    • 用于简单的业务规则验证
    • 避免复杂的检查逻辑
    • 考虑性能影响

性能考虑 ​

  • 索引使用：唯一约束自动创建索引，检查约束不创建索引
  • 写入开销：约束会增加写入操作的开销
  • 查询优化：唯一约束可以提高查询性能
  • 死锁风险：唯一约束可能增加死锁风险

常见用例 ​

唯一约束用例 ​

  • 用户邮箱唯一性
  • 订单号唯一性
  • 产品SKU唯一性
  • 组合业务键唯一性

检查约束用例 ​

  • 价格必须为正数
  • 库存不能为负数
  • 日期范围验证
  • 状态值验证

约束与数据迁移 ​

迁移策略 ​

  • 添加约束：

    • 先验证现有数据
    • 分批添加约束
    • 考虑使用 ALTER TABLE ... ADD CONSTRAINT ... ALGORITHM=INPLACE, LOCK=NONE

  • 修改约束：

    • 评估影响范围
    • 测试修改后的行为
    • 计划维护窗口

数据验证 ​

sql
-- 添加约束前验证数据
SELECT COUNT(*) FROM products WHERE price <= 0;
SELECT COUNT(*) FROM users WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;

-- 修复无效数据
UPDATE products SET price = 0.01 WHERE price <= 0;

不同版本的支持 ​

MySQL 5.7 ​

  • 唯一约束完全支持
  • 检查约束语法支持但不生效
  • 需要使用触发器实现检查约束

MySQL 8.0 ​

  • 8.0.16+：完全支持检查约束
  • 8.0.19+：支持 DROP CHECK 语句
  • 增强了约束管理功能

MariaDB ​

  • 支持唯一约束
  • 支持检查约束
  • 提供额外的约束功能

约束与分区表 ​

分区表中的约束 ​

  • 唯一约束必须包含分区键
  • 检查约束可以在分区表中使用
  • 约束在每个分区内生效

示例 ​

sql
-- 分区表中的唯一约束
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    PRIMARY KEY (order_id, order_date),
    UNIQUE (order_id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

约束与复制 ​

主从复制 ​

  • 约束在主库和从库上都生效
  • 确保数据一致性
  • 复制延迟可能导致暂时的约束违反

复制注意事项 ​

  • 确保主从库版本兼容
  • 检查约束在从库上可能有不同行为
  • 测试复制环境中的约束行为

常见问题（FAQ） ​

Q1: 唯一约束与主键约束的区别是什么？ ​

A1: 主要区别：

  • 每个表只能有一个主键，但可以有多个唯一约束
  • 主键列不允许NULL值，唯一约束允许NULL值
  • 主键通常用于标识行，唯一约束用于确保业务唯一性

Q2: 检查约束在 MySQL 5.7 中不生效怎么办？ ​

A2: 可以使用触发器实现类似功能：

  • 创建 BEFORE INSERT 和 BEFORE UPDATE 触发器
  • 在触发器中添加验证逻辑
  • 使用 SIGNAL 语句抛出错误

Q3: 如何处理唯一约束冲突？ ​

A3: 可以使用以下方法：

  • 使用 INSERT IGNORE 忽略冲突
  • 使用 INSERT ... ON DUPLICATE KEY UPDATE 更新现有记录
  • 使用 REPLACE INTO 替换现有记录
  • 在应用层处理冲突

Q4: 唯一约束对性能有什么影响？ ​

A4: 唯一约束对性能的影响：

  • 提高查询性能（通过索引）
  • 降低写入性能（需要检查唯一性）
  • 增加索引维护成本
  • 可能导致死锁

Q5: 如何在大型表上添加唯一约束而不影响性能？ ​

A5: 可以使用以下方法：

  • 使用 ALTER TABLE ... ADD CONSTRAINT ... ALGORITHM=INPLACE, LOCK=NONE
  • 在低峰期执行
  • 先验证现有数据
  • 考虑使用在线模式变更工具

Q6: 检查约束的条件可以有多复杂？ ​

A6: 检查约束的条件应该：

  • 保持简单
  • 避免子查询
  • 避免函数调用
  • 避免依赖其他表的数据
  • 考虑性能影响

Q7: 唯一约束可以包含NULL值吗？ ​

A7: 是的，唯一约束可以包含NULL值：

  • NULL值不被视为重复
  • 可以有多个NULL值
  • 但组合唯一约束中，只有当所有列都为NULL时才视为重复