如何记录INSERT ON DUPLICATE KEY及UPDATE后的订阅者数据变更？

数据老陈

2026-5-20

没问题，我来帮你梳理怎么记录这些订阅者数据的变更日志。针对你的场景——保留取消订阅用户数据（仅修改eligibility为false），同时追踪所有更新操作，咱们可以从结构化日志条目和数据库落地实现两个层面来做，既清晰又方便后续审计回溯。

一、结构化变更日志条目示例

每条日志需要包含核心信息，确保能完整还原操作场景。你可以用表格或者列表来记录，这里给你用列表+明细的方式呈现：

操作1：职位从'doctor'改为'colonel'
- 操作时间：2024-XX-XX HH:MM:SS（精确到秒）
- 操作类型：字段更新
- 用户标识：[这里填该用户的唯一ID/邮箱/姓名，比如“John Doe”或“sub_1001”]
- 变更字段：title
- 旧值：doctor
- 新值：colonel
- 操作人：[系统/具体操作员工账号，比如“admin_001”]
操作2：律师的eligibility设为false
- 操作时间：2024-XX-XX HH:MM:SS
- 操作类型：订阅状态变更（取消订阅）
- 用户标识：[律师的唯一ID/标识，比如“Jane Smith”或“sub_1002”]
- 变更字段：eligibility
- 旧值：true
- 新值：false
- 操作人：[系统/操作员工账号]
操作3：'RN'职位改为'BSN'并更新电话号码
（因为同时修改了两个字段，建议拆分两条日志，或者合并记录多个字段变更，这里推荐拆分更清晰）
- 子操作3.1：职位更新
  - 操作时间：2024-XX-XX HH:MM:SS
  - 操作类型：字段更新
  - 用户标识：[RN用户的唯一ID/标识，比如“Mike Lee”或“sub_1003”]
  - 变更字段：title
  - 旧值：RN
  - 新值：BSN
  - 操作人：[系统/操作员工账号]
- 子操作3.2：电话号码更新
  - 操作时间：2024-XX-XX HH:MM:SS
  - 操作类型：字段更新
  - 用户标识：[同上，sub_1003]
  - 变更字段：phone_number
  - 旧值：444-5555
  - 新值：444-1234（你写的“444-123...”我补全了，实际按真实值填）
  - 操作人：[系统/操作员工账号]

二、数据库层面的落地建议

如果要让日志自动或规范落地，建议创建一个专门的变更日志表，比如subscriber_change_log，然后通过应用层操作时写入或者数据库触发器自动生成两种方式来记录。

1. 先创建日志表（SQL示例）

CREATE TABLE subscriber_change_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    operation_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    subscriber_unique_id VARCHAR(50) NOT NULL, -- 关联订阅者表的唯一ID
    operation_type VARCHAR(20) NOT NULL, -- 比如UPDATE/STATUS_CHANGE
    field_name VARCHAR(50) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    operated_by VARCHAR(50) -- 记录操作人，可留空表示系统自动操作
);

2. 对应操作的SQL及日志写入

针对你提到的三个操作，执行数据更新后，同步插入日志：

操作1：修改doctor为colonel

-- 先更新用户数据（建议用唯一ID筛选，比用title更精准）
UPDATE subscribers 
SET title = 'colonel' 
WHERE subscriber_id = 'sub_1001';

-- 插入日志
INSERT INTO subscriber_change_log 
(subscriber_unique_id, operation_type, field_name, old_value, new_value, operated_by)
VALUES ('sub_1001', 'UPDATE', 'title', 'doctor', 'colonel', 'admin_001');

操作2：律师设为取消订阅（eligibility=false）

UPDATE subscribers 
SET eligibility = false 
WHERE subscriber_id = 'sub_1002';

INSERT INTO subscriber_change_log 
(subscriber_unique_id, operation_type, field_name, old_value, new_value, operated_by)
VALUES ('sub_1002', 'STATUS_CHANGE', 'eligibility', 'true', 'false', 'system');

操作3：修改RN的职位和电话

-- 更新用户数据
UPDATE subscribers 
SET title = 'BSN', phone_number = '444-1234' 
WHERE subscriber_id = 'sub_1003';

-- 插入两条日志（对应两个字段变更）
INSERT INTO subscriber_change_log 
(subscriber_unique_id, operation_type, field_name, old_value, new_value, operated_by)
VALUES ('sub_1003', 'UPDATE', 'title', 'RN', 'BSN', 'admin_001');

INSERT INTO subscriber_change_log 
(subscriber_unique_id, operation_type, field_name, old_value, new_value, operated_by)
VALUES ('sub_1003', 'UPDATE', 'phone_number', '444-5555', '444-1234', 'admin_001');

3. 进阶：用触发器自动生成日志

如果不想在应用层每次手动写日志，可以给subscribers表创建UPDATE触发器，当字段变更时自动插入日志：

DELIMITER //
CREATE TRIGGER after_subscriber_update
AFTER UPDATE ON subscribers
FOR EACH ROW
BEGIN
    -- 检查title字段是否变更
    IF OLD.title != NEW.title THEN
        INSERT INTO subscriber_change_log 
        (subscriber_unique_id, operation_type, field_name, old_value, new_value)
        VALUES (OLD.subscriber_id, 'UPDATE', 'title', OLD.title, NEW.title);
    END IF;
    
    -- 检查eligibility字段是否变更
    IF OLD.eligibility != NEW.eligibility THEN
        INSERT INTO subscriber_change_log 
        (subscriber_unique_id, operation_type, field_name, old_value, new_value)
        VALUES (OLD.subscriber_id, 'STATUS_CHANGE', 'eligibility', OLD.eligibility, NEW.eligibility);
    END IF;
    
    -- 检查phone_number字段是否变更
    IF OLD.phone_number != NEW.phone_number THEN
        INSERT INTO subscriber_change_log 
        (subscriber_unique_id, operation_type, field_name, old_value, new_value)
        VALUES (OLD.subscriber_id, 'UPDATE', 'phone_number', OLD.phone_number, NEW.phone_number);
    END IF;
END //
DELIMITER ;

这样只要更新subscribers表的对应字段，日志就会自动写入，省去手动操作的麻烦。

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