【MySQL】表的关联关系-CSDN博客

本文链接：https://blog.csdn.net/Go_ahead1025/article/details/147965432

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

文章目录

1. 关联关系概述
- 1.1 关联关系的作用
- 1.2 MySQL中的关联关系类型
2. 一对一关系 (One-to-One)
3. 一对多关系 (One-to-Many)
4. 多对多关系 (Many-to-Many)
5. 自引用关系 (Self-Referencing)
6. 关系设计原则与最佳实践
7. 典型关联模式与示例

正文

1. 关联关系概述

表的关联关系是关系型数据库设计中的核心概念，用于描述不同表之间的逻辑连接。MySQL中的表关联使数据能够分散存储在多个表中，同时保持数据之间的联系。

1.1 关联关系的作用

减少数据冗余
提高数据一致性
优化查询性能
实现复杂的业务逻辑
简化数据库维护

1.2 MySQL中的关联关系类型

MySQL支持以下几种主要的表关联关系：

2. 一对一关系 (One-to-One)

2.1 一对一关系的特点

一个表中的记录最多只能与另一个表中的一条记录相关联
两表中的记录存在一一对应的关系
通常用于将不常用的信息分离到单独的表中
实现方式包括共享主键和外键唯一约束

2.2 实现一对一关系

2.2.1 使用共享主键

-- 创建用户基本信息表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户详细信息表（共享主键）
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    gender ENUM('Male', 'Female', 'Other'),
    phone VARCHAR(20),
    address TEXT,
    bio TEXT,
    profile_image VARCHAR(255),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

2.2.2 使用唯一外键

-- 创建员工表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL
);

-- 创建员工安全证书表（唯一外键）
CREATE TABLE employee_security_credentials (
    credential_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT UNIQUE,
    access_card_number VARCHAR(50) NOT NULL,
    security_clearance_level ENUM('Level1', 'Level2', 'Level3') DEFAULT 'Level1',
    issue_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE,
    CHECK (expiry_date > issue_date)
);

2.3 一对一关系查询

-- 联结查询用户及其详细信息
SELECT u.user_id, u.username, u.email, 
       p.first_name, p.last_name, p.phone, p.address
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id;

-- 使用WHERE子句查询特定用户信息
SELECT u.username, u.email, p.first_name, p.last_name, p.phone
FROM users u
JOIN user_profiles p ON u.user_id = p.user_id
WHERE u.username = 'johndoe';

3. 一对多关系 (One-to-Many)

3.1 一对多关系的特点

主表（"一"方）中的一条记录可以关联从表（"多"方）中的多条记录
从表中的一条记录只能关联主表中的一条记录
这是最常见的关联关系类型
通过在"多"的一方添加外键实现

3.2 实现一对多关系

-- 创建部门表（"一"方）
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    budget DECIMAL(15, 2) CHECK (budget > 0),
    manager_id INT
);

-- 创建员工表（"多"方）
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL
);

-- 更新部门表添加管理者外键（延迟添加以避免循环依赖）
ALTER TABLE departments
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ON DELETE SET NULL;

3.2.1 更多一对多关系示例

-- 客户和订单（一对多）
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    status ENUM('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
    shipping_address TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 文章和评论（一对多）
CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    published_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id)
);

CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    commented_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

3.3 一对多关系查询

-- 查询部门及其所有员工
SELECT d.department_name, e.first_name, e.last_name, e.email
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_name, e.last_name, e.first_name;

-- 查询特定部门的员工数量
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id
ORDER BY employee_count DESC;

-- 查询客户的所有订单
SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.total_amount, o.status
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 123
ORDER BY o.order_date DESC;

4. 多对多关系 (Many-to-Many)

4.1 多对多关系的特点

表A中的一条记录可以关联表B中的多条记录
表B中的一条记录也可以关联表A中的多条记录
需要通过第三个中间表（关联表/连接表）实现
中间表通常包含两个外键，分别指向两个主表

4.2 实现多对多关系

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE,
    enrollment_date DATE DEFAULT (CURRENT_DATE)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    credit_hours INT NOT NULL CHECK (credit_hours > 0),
    department VARCHAR(50)
);

-- 学生课程关联表（中间表）
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade VARCHAR(2),
    completion_status ENUM('In Progress', 'Completed', 'Withdrawn') DEFAULT 'In Progress',
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

4.2.1 更多多对多关系示例

-- 产品和标签（多对多）
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0)
);

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(50) NOT NULL UNIQUE,
    tag_category VARCHAR(50)
);

CREATE TABLE product_tags (
    product_id INT,
    tag_id INT,
    PRIMARY KEY (product_id, tag_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE
);

-- 员工和项目（多对多带附加信息）
CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    status ENUM('Planned', 'In Progress', 'On Hold', 'Completed', 'Cancelled') DEFAULT 'Planned',
    budget DECIMAL(15, 2),
    CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50) NOT NULL,
    assigned_date DATE DEFAULT (CURRENT_DATE),
    hours_per_week DECIMAL(5, 2) DEFAULT 0,
    is_project_manager BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

4.3 多对多关系查询

-- 查询学生及其所有课程
SELECT s.first_name, s.last_name, c.course_code, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.student_id = 123
ORDER BY c.course_name;

-- 查询课程及其所有学生
SELECT c.course_code, c.course_name, 
       COUNT(sc.student_id) AS enrolled_students,
       AVG(CASE WHEN sc.grade IS NOT NULL THEN CONVERT(sc.grade, DECIMAL) ELSE NULL END) AS average_grade
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id
ORDER BY enrolled_students DESC;

-- 查询带有特定标签的所有产品
SELECT p.product_name, p.price, t.tag_name
FROM products p
JOIN product_tags pt ON p.product_id = pt.product_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE t.tag_name = 'Organic'
ORDER BY p.price;

5. 自引用关系 (Self-Referencing)

5.1 自引用关系的特点

表中的记录引用同一表中的其他记录
用于表示层次结构、上下级关系或递归关系
可以实现一对一、一对多或多对多的自引用
使用外键指向同一表的主键实现

5.2 实现自引用关系

-- 员工层级关系（一对多自引用）
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ON DELETE SET NULL
);

-- 类别表（树形结构）
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_category_id INT,
    level INT NOT NULL DEFAULT 1,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);

-- 好友关系（多对多自引用）
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    profile_info TEXT
);

CREATE TABLE friendships (
    user_id INT,
    friend_id INT,
    friendship_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('Pending', 'Accepted', 'Blocked') DEFAULT 'Pending',
    PRIMARY KEY (user_id, friend_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (friend_id) REFERENCES users(user_id) ON DELETE CASCADE,
    CHECK (user_id != friend_id)
);

5.3 自引用关系查询

-- 查询员工及其直接下属
SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
       m.employee_id AS subordinate_id, CONCAT(m.first_name, ' ', m.last_name) AS subordinate_name
FROM employees e
LEFT JOIN employees m ON e.employee_id = m.manager_id
ORDER BY e.employee_id;

-- 查询特定员工的所有上级
WITH RECURSIVE emp_hierarchy AS (
    -- 基本情况：选择初始员工
    SELECT employee_id, first_name, last_name, manager_id, 1 AS level
    FROM employees
    WHERE employee_id = 123
    
    UNION ALL
    
    -- 递归：查找当前员工的经理
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.employee_id = h.manager_id
)
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS employee_name, level
FROM emp_hierarchy
ORDER BY level;

-- 获取类别及其子类别
WITH RECURSIVE category_hierarchy AS (
    -- 基本情况：顶级类别（没有父类别）
    SELECT category_id, category_name, parent_category_id, 0 AS depth
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    -- 递归：查找当前类别的子类别
    SELECT c.category_id, c.category_name, c.parent_category_id, ch.depth + 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_category_id = ch.category_id
)
SELECT 
    CONCAT(REPEAT('    ', depth), category_name) AS category_tree,
    category_id,
    parent_category_id,
    depth
FROM category_hierarchy
ORDER BY depth, category_name;

6. 关系设计原则与最佳实践

6.1 数据库范式

关系型数据库设计中的范式是一系列规则，用于减少冗余和提高数据完整性：

范式	描述
第一范式 (1NF)	表中的每一列都是不可分割的原子数据项
第二范式 (2NF)	满足1NF，且所有非主键列完全依赖于主键
第三范式 (3NF)	满足2NF，且所有非主键列只依赖于主键
BC范式 (BCNF)	所有决定因素都是候选键
第四范式 (4NF)	表中不存在多值依赖
第五范式 (5NF)	表中不存在联合依赖

6.2 表关系设计策略

6.3 外键约束策略

约束行为	作用	使用场景
CASCADE	当主表记录删除/更新时，自动删除/更新从表中的关联记录	强依赖关系，如订单-订单项
SET NULL	当主表记录删除/更新时，将从表中的外键列设为NULL	弱依赖关系，记录仍有独立价值
RESTRICT	阻止删除/更新主表中被引用的记录	保证数据完整性最重要的场景
NO ACTION	与RESTRICT类似，但检查的时间不同	默认行为，通常与RESTRICT功能相同
SET DEFAULT	当主表记录删除/更新时，将从表中的外键列设为默认值	MySQL不支持，但概念上存在

6.4 索引与关联性能

表关联时的索引策略：

-- 为关联列创建索引以提高JOIN性能
CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_student_id ON student_courses(student_id);
CREATE INDEX idx_course_id ON student_courses(course_id);

6.5 关联查询优化

优化关联查询的策略：

只选择需要的列以减少数据传输
使用适当的JOIN类型（INNER、LEFT、RIGHT）
确保关联列上有适当的索引
在WHERE子句中使用关联表的过滤条件
考虑子查询和JOIN的性能差异
使用EXPLAIN分析查询执行计划

-- 使用EXPLAIN分析关联查询
EXPLAIN SELECT c.customer_id, c.first_name, c.last_name, 
         COUNT(o.order_id) AS order_count, 
         SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2023-01-01'
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 10;

7. 典型关联模式与示例

7.1 主从表模式

主从表模式是一对多关系的典型应用：

-- 主表
CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    status ENUM('Draft', 'Sent', 'Paid', 'Overdue', 'Cancelled') DEFAULT 'Draft',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CHECK (due_date >= invoice_date)
);

-- 从表
CREATE TABLE invoice_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
    discount_percent DECIMAL(5, 2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
    line_total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price * (1 - discount_percent/100)) STORED,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 更新发票总金额的触发器
DELIMITER //
CREATE TRIGGER update_invoice_total AFTER INSERT ON invoice_items
FOR EACH ROW
BEGIN
    UPDATE invoices 
    SET total_amount = (
        SELECT SUM(line_total) 
        FROM invoice_items 
        WHERE invoice_id = NEW.invoice_id
    )
    WHERE invoice_id = NEW.invoice_id;
END//
DELIMITER ;

7.2 树形结构模式

树形结构是自引用关系的常见应用：

-- 组织架构表
CREATE TABLE organization_units (
    unit_id INT PRIMARY KEY AUTO_INCREMENT,
    unit_name VARCHAR(100) NOT NULL,
    parent_unit_id INT,
    manager_id INT,
    level INT GENERATED ALWAYS AS (
        CASE
            WHEN parent_unit_id IS NULL THEN 1
            ELSE (SELECT level + 1 FROM organization_units WHERE unit_id = parent_unit_id)
        END
    ) VIRTUAL,
    FOREIGN KEY (parent_unit_id) REFERENCES organization_units(unit_id) ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ON DELETE SET NULL
);

-- 递归查询获取完整组织结构
WITH RECURSIVE org_hierarchy AS (
    -- 基本情况：顶级部门
    SELECT unit_id, unit_name, parent_unit_id, 0 AS depth, CAST(unit_name AS CHAR(1000)) AS path
    FROM organization_units
    WHERE parent_unit_id IS NULL
    
    UNION ALL
    
    -- 递归：查找子部门
    SELECT ou.unit_id, ou.unit_name, ou.parent_unit_id, oh.depth + 1, 
           CONCAT(oh.path, ' > ', ou.unit_name)
    FROM organization_units ou
    JOIN org_hierarchy oh ON ou.parent_unit_id = oh.unit_id
)
SELECT 
    unit_id,
    CONCAT(REPEAT('    ', depth), unit_name) AS unit_tree,
    path,
    depth
FROM org_hierarchy
ORDER BY path;

7.3 多态关联模式

多态关联允许一个表关联到多个不同类型的表：

-- 创建不同类型的实体表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    customer_type ENUM('Individual', 'Company') NOT NULL,
    -- 其他客户字段
);

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    -- 其他供应商字段
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    -- 其他员工字段
);

-- 使用多态关联的通用地址表
CREATE TABLE addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    entity_type ENUM('customer', 'supplier', 'employee') NOT NULL,
    entity_id INT NOT NULL,
    address_type ENUM('Billing', 'Shipping', 'Home', 'Work', 'Other') NOT NULL,
    street VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    -- 复合索引以提高查询性能
    INDEX idx_entity (entity_type, entity_id),
    -- 强制每个实体类型和ID组合只能有一个主要地址
    UNIQUE KEY uk_primary_address (entity_type, entity_id, is_primary),
    CHECK (is_primary IN (0, 1))  -- 确保布尔值只能是0或1
);

7.4 历史记录模式

历史记录模式用于跟踪表中数据的变更历史：

-- 主表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 历史记录表
CREATE TABLE product_history (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL,
    is_active BOOLEAN,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(100),
    change_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

-- 创建触发器记录产品变更
DELIMITER //

-- 插入触发器
CREATE TRIGGER product_after_insert AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_history (
        product_id, product_name, description, price, 
        stock_quantity, is_active, change_type, changed_by
    )
    VALUES (
        NEW.product_id, NEW.product_name, NEW.description, NEW.price,
        NEW.stock_quantity, NEW.is_active, 'INSERT', CURRENT_USER()
    );
END//

-- 更新触发器
CREATE TRIGGER product_after_update AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_history (
        product_id, product_name, description, price, 
        stock_quantity, is_active, change_type, changed_by
    )
    VALUES (
        NEW.product_id, NEW.product_name, NEW.description, NEW.price,
        NEW.stock_quantity, NEW.is_active, 'UPDATE', CURRENT_USER()
    );
END//

-- 删除触发器
CREATE TRIGGER product_before_delete BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_history (
        product_id, product_name, description, price, 
        stock_quantity, is_active, change_type, changed_by
    )
    VALUES (
        OLD.product_id, OLD.product_name, OLD.description, OLD.price,
        OLD.stock_quantity, OLD.is_active, 'DELETE', CURRENT_USER()
    );
END//

DELIMITER ;