如何设计留存历史数据的数据库：ERD建模与MySQL实现方法

两种主流ERD设计方案 #

方案1：主表+历史表分离模式 #

这种模式将当前数据和历史数据分开存储，适合高频更新、需要快速查询当前数据的场景：

• 主表：仅存储最新的业务数据，包含业务主键、核心业务字段、最后更新信息。
• 历史表：结构与主表高度一致，额外增加历史记录专属字段（操作类型、操作时间、操作人等），通过业务主键与主表建立一对多关联。

ERD关系：主表的每条记录对应历史表的多条版本记录（新增、更新、删除各一条或多条）。

方案2：单表版本化模式 #

这种模式将所有版本数据存储在同一张表中，通过标记字段区分当前有效版本，适合更新频率较低、业务逻辑简单的场景：

• 单表结构：包含业务主键、核心业务字段，额外增加version（版本号，递增）、is_current（布尔值，标记当前有效）、操作时间、操作人字段。
• 同一业务主键对应多条记录，仅一条记录的is_current为true。

MySQL具体实现 #

方案1：主表+历史表实现 #

1. 创建表结构

-- 主表：存储最新用户信息
CREATE TABLE user_profile (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    last_update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    operator VARCHAR(50) COMMENT '操作人'
);

-- 历史表：存储所有版本的用户信息
CREATE TABLE user_profile_history (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL COMMENT '操作类型',
    operation_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    operator VARCHAR(50) COMMENT '操作人',
    FOREIGN KEY (user_id) REFERENCES user_profile(user_id) ON DELETE CASCADE
);

2. 用触发器自动同步历史数据

通过触发器实现主表数据变更时自动写入历史表，无需手动维护：

-- 插入数据时记录历史
DELIMITER //
CREATE TRIGGER trg_user_profile_after_insert
AFTER INSERT ON user_profile
FOR EACH ROW
BEGIN
    INSERT INTO user_profile_history (user_id, username, email, phone, operation_type, operator)
    VALUES (NEW.user_id, NEW.username, NEW.email, NEW.phone, 'INSERT', NEW.operator);
END //
DELIMITER ;

-- 更新数据时记录历史（保存旧版本数据）
DELIMITER //
CREATE TRIGGER trg_user_profile_after_update
AFTER UPDATE ON user_profile
FOR EACH ROW
BEGIN
    INSERT INTO user_profile_history (user_id, username, email, phone, operation_type, operator)
    VALUES (OLD.user_id, OLD.username, OLD.email, OLD.phone, 'UPDATE', NEW.operator);
END //
DELIMITER ;

-- 删除数据时记录历史
DELIMITER //
CREATE TRIGGER trg_user_profile_after_delete
AFTER DELETE ON user_profile
FOR EACH ROW
BEGIN
    INSERT INTO user_profile_history (user_id, username, email, phone, operation_type)
    VALUES (OLD.user_id, OLD.username, OLD.email, OLD.phone, 'DELETE');
END //
DELIMITER ;

3. 数据查询示例

-- 查询用户当前最新信息
SELECT * FROM user_profile WHERE user_id = 1;

-- 查询用户所有历史版本（按时间倒序）
SELECT * FROM user_profile_history WHERE user_id = 1 ORDER BY operation_time DESC;

方案2：单表版本化实现 #

1. 创建版本化表结构

CREATE TABLE user_profile (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL COMMENT '业务主键：用户ID',
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    version INT NOT NULL DEFAULT 1 COMMENT '版本号，每次更新递增',
    is_current BOOLEAN NOT NULL DEFAULT 1 COMMENT '是否为当前有效版本',
    operation_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    operator VARCHAR(50) COMMENT '操作人',
    -- 约束：同一用户只能有一个当前版本
    UNIQUE KEY uk_user_current (user_id, is_current)
);

2. 更新数据的原子操作

更新时需先将旧版本标记为无效，再插入新版本，必须在事务中执行保证原子性：

START TRANSACTION;
-- 标记旧版本为非当前
UPDATE user_profile SET is_current = 0 WHERE user_id = 1 AND is_current = 1;
-- 插入新版本（版本号自动+1）
INSERT INTO user_profile (user_id, username, email, phone, version, operator)
SELECT user_id, 'new_john_doe', 'new_john@example.com', '13800138000', version + 1, 'admin'
FROM user_profile WHERE user_id = 1 AND is_current = 0 ORDER BY version DESC LIMIT 1;
COMMIT;

3. 数据查询示例

-- 查询用户当前有效信息
SELECT * FROM user_profile WHERE user_id = 1 AND is_current = 1;

-- 查询用户所有历史版本（按版本号倒序）
SELECT * FROM user_profile WHERE user_id = 1 ORDER BY version DESC;

方案选择建议 #

• 主表+历史表：优先用于高并发、高频更新场景，主表查询性能不受历史数据影响；缺点是需要维护两张表，触发器可能带来轻微性能损耗（高并发场景可替换为程序逻辑或CDC工具同步）。
• 单表版本化：适合更新频率低、业务逻辑简单的场景，结构简洁；缺点是单表数据量增长快，需注意索引优化（给user_id+is_current加联合索引）。

内容的提问来源于stack exchange，提问作者TTC1