SQL语言如何实现数据版本控制 SQL语言在历史记录追踪中的设计方案

products

product_audit

users

CREATE TABLE users (
    user_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL,
    version_id INT NOT NULL DEFAULT 1, -- 版本号
    valid_from DATETIME NOT NULL, -- 有效起始时间
    valid_to DATETIME, -- 有效结束时间 (NULL表示当前最新版本)
    is_current_version BOOLEAN NOT NULL DEFAULT TRUE, -- 是否当前版本
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, version_id) -- 联合主键
);

-- 插入一条初始数据
INSERT INTO users (user_id, email, status, valid_from, is_current_version)
VALUES (101, 'old_email@example.com', 'active', NOW(), TRUE);

-- 更新用户邮箱和状态的逻辑
-- 假设用户ID为101，新邮箱是'new_email@example.com'，新状态是'inactive'
-- 这是一个简化的存储过程或事务逻辑
BEGIN;
    -- 1. 找到当前最新版本，并结束其有效期
    UPDATE users
    SET
        valid_to = NOW(),
        is_current_version = FALSE,
        updated_at = NOW()
    WHERE
        user_id = 101 AND is_current_version = TRUE;

    -- 2. 插入新版本
    INSERT INTO users (user_id, email, status, version_id, valid_from, is_current_version)
    SELECT
        user_id,
        'new_email@example.com', -- 新邮箱
        'inactive', -- 新状态
        (SELECT COALESCE(MAX(version_id), 0) + 1 FROM users WHERE user_id = 101), -- 新版本号
        NOW(),
        TRUE
    FROM users
    WHERE user_id = 101 AND is_current_version = FALSE -- 确保是基于刚刚更新的旧版本信息
    LIMIT 1; -- 避免多行插入
COMMIT;

-- 查询当前最新版本
SELECT * FROM users WHERE user_id = 101 AND is_current_version = TRUE;

-- 查询某个时间点的历史版本 (例如，在某个时间点之前的数据)
SELECT * FROM users WHERE user_id = 101 AND valid_from <= '2023-10-26 10:00:00' AND (valid_to IS NULL OR valid_to > '2023-10-26 10:00:00');

products

product_audit_log

-- 主表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    stock INT
);

-- 审计日志表
CREATE TABLE product_audit_log (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_name VARCHAR(255),
    old_price DECIMAL(10, 2),
    old_stock INT,
    change_type VARCHAR(10), -- 'INSERT', 'UPDATE', 'DELETE'
    changed_by VARCHAR(50), -- 假设有用户ID或名称
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO products (product_id, name, price, stock) VALUES (1, 'Laptop', 1200.00, 50);

-- 创建一个AFTER UPDATE触发器
DELIMITER //
CREATE TRIGGER trg_products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit_log (
        product_id, old_name, old_price, old_stock, change_type, changed_by
    ) VALUES (
        OLD.product_id, OLD.name, OLD.price, OLD.stock, 'UPDATE', USER() -- USER()获取当前数据库用户
    );
END;
//
DELIMITER ;

-- 创建一个AFTER DELETE触发器
DELIMITER //
CREATE TRIGGER trg_products_after_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit_log (
        product_id, old_name, old_price, old_stock, change_type, changed_by
    ) VALUES (
        OLD.product_id, OLD.name, OLD.price, OLD.stock, 'DELETE', USER()
    );
END;
//
DELIMITER ;

-- 更新数据，触发审计日志
UPDATE products SET price = 1150.00, stock = 45 WHERE product_id = 1;

-- 查询审计日志
SELECT * FROM product_audit_log WHERE product_id = 1;