MySQL插入新预约记录时自动更新同一患者历史visit_count字段方案咨询

针对分析型数据库反范式设计下的同patient_id历史visit_count同步更新需求，共有3类可行方案，可根据你使用的数据库特性、写入链路逻辑选择：

方案1：触发器实现（无需修改业务写入逻辑，适用于支持行级触发器的分析库） #

适用于PostgreSQL系分析库、支持触发器的ClickHouse版本等场景，数据库侧自动触发更新，业务侧无需调整INSERT逻辑。

实现代码： #

-- 1. 创建插入后触发的触发器
DELIMITER //
CREATE TRIGGER sync_patient_visit_count AFTER INSERT ON patients
FOR EACH ROW
BEGIN
    -- 同步更新当前患者所有历史记录的visit_count为新插入的最新值
    UPDATE patients 
    SET visit_count = NEW.visit_count 
    WHERE patient_id = NEW.patient_id;
END //
DELIMITER ;

注意：如果你的数据库支持批量插入触发器，需要调整逻辑处理单次插入多条同patient_id记录的场景，取最大/最新的visit_count值更新即可。

方案2：存储过程/自定义函数封装（兼容性最高，全类型分析库通用） #

如果你的数据库不支持触发器，可将插入+更新逻辑封装为存储过程，业务侧所有插入操作改为调用该存储过程即可。

实现代码： #

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE add_patient_booking(
    IN p_booking_code VARCHAR(64),
    IN p_patient_id INT(5),
    IN p_visit_count INT(5)
)
BEGIN
    -- 先更新该患者所有历史记录的visit_count
    UPDATE patients 
    SET visit_count = p_visit_count 
    WHERE patient_id = p_patient_id;
    -- 再插入新预约记录
    INSERT INTO patients (booking_code, patient_id, visit_count)
    VALUES (p_booking_code, p_patient_id, p_visit_count);
END //
DELIMITER ;

-- 调用示例
CALL add_patient_booking('AB999', 4, 3);

方案3：离线批量补全（适用于纯离线T+1分析场景） #

如果是纯离线数仓，无需实时更新，可在每次增量数据插入完成后，执行一次批量更新任务同步历史值：

-- 先拿到本次新增的所有患者最新的visit_count值
WITH latest_visit AS (
    SELECT patient_id, MAX(visit_count) as latest_count
    FROM patients 
    -- 过滤本次增量插入的记录，可按插入时间、s_no范围过滤，示例为取s_no大于上次同步最大值
    WHERE s_no > [上次同步最大s_no]
    GROUP BY patient_id
)
UPDATE patients p
JOIN latest_visit lv ON p.patient_id = lv.patient_id
SET p.visit_count = lv.latest_count;

• 实时写入场景优先选方案1，无需改业务代码
• 实时写入但数据库不支持触发器选方案2，仅需调整写入调用逻辑
• 离线分析场景选方案3，对写入链路无侵入，性能最优

内容的提问来源于stack exchange，提问作者shachi g chandra