MySQL数据库的约束与设计解读

一、数据库约束

1. 主键约束 (Primary Key)

-- 创建表时定义主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 或使用约束语法
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

2. 外键约束 (Foreign Key)

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE  -- 级联删除
    ON UPDATE CASCADE  -- 级联更新
);

-- 其他引用操作
-- ON DELETE RESTRICT    -- 禁止删除（默认）
-- ON DELETE SET NULL   -- 设置为NULL
-- ON DELETE NO ACTION  -- 无操作

3. 唯一约束 (Unique)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE(user_id, role_id)
);

4. 非空约束 (NOT NULL)

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT  -- 可为空
);

5. 检查约束 (CHECK)

-- MySQL 8.0+ 支持
CREATE TABLE students (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150),
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100)
);

6. 默认值约束 (DEFAULT)

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('draft', 'published') DEFAULT 'draft',
    views INT DEFAULT 0
);

二、数据库设计原则

1. 范式化设计

• 每个列都是原子的（不可再分）
• 每个表都有主键

-- 不符合1NF
CREATE TABLE bad_design (
    id INT PRIMARY KEY,
    tags VARCHAR(200)  -- 存储多个标签：'java,mysql,database'
);

-- 符合1NF
CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(200)
);

CREATE TABLE post_tags (
    post_id INT,
    tag VARCHAR(50),
    PRIMARY KEY (post_id, tag)
);

• 满足1NF
• 所有非主键列完全依赖于主键

• 满足2NF
• 消除传递依赖

2. 反范式化设计

在某些情况下为了性能，可以有意识地违反范式：

-- 反范式设计：存储冗余数据提高查询性能
CREATE TABLE orders_with_total (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2),  -- 冗余字段，可避免JOIN计算
    created_at TIMESTAMP
);

三、实体关系设计

1. 一对一关系

-- 用户基本信息表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 用户扩展信息表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    avatar_url VARCHAR(200),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

2. 一对多关系

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

3. 多对多关系

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

-- 关联表
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade DECIMAL(4,2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

四、索引设计

1. 索引类型

-- 单列索引
CREATE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_name_dob ON users(last_name, first_name, date_of_birth);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_phone ON customers(phone);

-- 全文索引（适用于文本搜索）
CREATE FULLTEXT INDEX idx_content ON articles(content);

2. 索引设计原则

• 为WHERE、JOIN、ORDER BY经常用到的列创建索引
• 索引列的选择性要高（不同值多）
• 避免在频繁更新的列上创建过多索引
• 复合索引要注意列的顺序

五、最佳实践示例

1. 完整的数据库设计示例

-- 用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 博客文章表
CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content LONGTEXT,
    summary TEXT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    view_count INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status),
    INDEX idx_published (published_at),
    FULLTEXT INDEX idx_fulltext (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 评论表
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    user_id INT NULL,
    parent_id INT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_approved BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(comment_id) ON DELETE CASCADE,
    INDEX idx_article (article_id),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 数据完整性检查

-- 添加约束检查
ALTER TABLE products 
ADD CONSTRAINT chk_price 
CHECK (price >= 0 AND price <= 1000000);

-- 添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_user 
FOREIGN KEY (user_id) 
REFERENCES users(user_id);

-- 查看约束信息
SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'your_database';

六、性能优化设计

1. 分区表

-- 按范围分区
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2. 垂直分表

-- 将大字段分离
CREATE TABLE user_basic (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE user_details (
    user_id INT PRIMARY KEY,
    bio TEXT,
    preferences JSON,
    metadata JSON,
    FOREIGN KEY (user_id) REFERENCES user_basic(user_id)
);

总结要点

约束确保数据完整性

• 主键保证唯一标识
• 外键维护引用完整性
• 检查约束保证业务规则

设计平衡范式与性能

• 通常遵循3NF
• 适当反范式化优化查询
• 考虑读写比例

索引是双刃剑

• 提高查询性能
• 增加写操作开销
• 需要定期维护

考虑未来发展

• 预留扩展字段
• 设计可扩展的表结构
• 考虑数据迁移需求

文档化设计决策

• 记录设计原理
• 说明反范式化原因
• 记录性能优化措施