含外键的SQL历史表运作机制探讨——用户地址表场景问询

核心需求 #

需完整留存用户曾关联过的所有地址信息，不受主表地址后续变更的影响。

一、历史表结构设计 #

1. address_history 地址快照表 #

用于保存地址的完整历史快照，确保每次地址变更都留存当时的状态：

CREATE TABLE address_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_address_id INT NOT NULL, -- 关联主表Address的ID，用于溯源原地址
    street VARCHAR(255) NOT NULL,
    houseNr VARCHAR(50) NOT NULL,
    postal VARCHAR(20) NOT NULL,
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 快照生成时间
    operation_type ENUM('INSERT', 'UPDATE') NOT NULL -- 记录是新增还是更新操作
);

2. user_history 用户关联历史表 #

用于记录用户关联地址的历史变更，直接绑定地址快照而非主表地址：

CREATE TABLE user_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- 关联主表User的ID
    address_history_id INT NOT NULL, -- 关联address_history的快照ID
    username VARCHAR(255) NOT NULL,
    pwd VARCHAR(255) NOT NULL, -- 需与主表一致加密存储，若无需留存可移除
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 关联变更时间
    operation_type ENUM('INSERT', 'UPDATE', 'ADDRESS_CHANGE') NOT NULL, -- 标记操作类型
    FOREIGN KEY (address_history_id) REFERENCES address_history(id),
    FOREIGN KEY (user_id) REFERENCES User(id)
);

二、落地实现：用触发器自动同步数据 #

通过数据库触发器实现主表变更时自动生成历史快照，无需手动编写业务代码：

1. 地址新增/更新时同步快照 #

地址新增触发器

DELIMITER //
CREATE TRIGGER tr_address_after_insert
AFTER INSERT ON Address
FOR EACH ROW
BEGIN
    INSERT INTO address_history (original_address_id, street, houseNr, postal, operation_type)
    VALUES (NEW.id, NEW.street, NEW.houseNr, NEW.postal, 'INSERT');
END //
DELIMITER ;

地址更新触发器

DELIMITER //
CREATE TRIGGER tr_address_after_update
AFTER UPDATE ON Address
FOR EACH ROW
BEGIN
    INSERT INTO address_history (original_address_id, street, houseNr, postal, operation_type)
    VALUES (NEW.id, NEW.street, NEW.houseNr, NEW.postal, 'UPDATE');
END //
DELIMITER ;

2. 用户关联地址变更时记录历史 #

当用户的addressID发生变更时，自动关联对应地址的最新快照并写入用户历史：

DELIMITER //
CREATE TRIGGER tr_user_after_update_address
AFTER UPDATE ON User
FOR EACH ROW
BEGIN
    -- 仅当地址关联ID变更时触发
    IF OLD.addressID != NEW.addressID THEN
        DECLARE latest_addr_hist_id INT;
        -- 获取新关联地址的最新快照ID
        SELECT id INTO latest_addr_hist_id
        FROM address_history
        WHERE original_address_id = NEW.addressID
        ORDER BY change_time DESC
        LIMIT 1;
        
        -- 写入用户关联历史
        INSERT INTO user_history (user_id, address_history_id, username, pwd, operation_type)
        VALUES (NEW.id, latest_addr_hist_id, NEW.username, NEW.pwd, 'ADDRESS_CHANGE');
    END IF;
END //
DELIMITER ;

三、查询用户历史关联地址示例 #

通过关联两张历史表，可快速获取指定用户的所有历史地址：

SELECT 
    uh.change_time AS 地址关联时间,
    ah.street AS 街道,
    ah.houseNr AS 门牌号,
    ah.postal AS 邮编,
    ah.operation_type AS 地址操作类型
FROM user_history uh
JOIN address_history ah ON uh.address_history_id = ah.id
WHERE uh.user_id = 123 -- 替换为目标用户ID
ORDER BY uh.change_time DESC;

注意事项 #

• 密码存储：若在user_history中留存密码，必须与主表采用相同的加密方式，禁止明文存储。
• 性能优化：若数据量极大，触发器可能影响写入性能，可考虑用异步消息队列替代触发器处理历史记录生成。
• 数据清理：定期归档或清理过旧的历史数据，避免主库存储膨胀。

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