如何创建自动更新的列统计另一表中各值的出现次数？

先修复脏数据 #

首先你的habit_values表中habit_value=3的habit_text存在乱码，先执行以下语句修复：

UPDATE habit_values 
SET habit_text = 'OK' 
WHERE habit_value = 3;

方案一：使用视图（推荐） #

视图是实时计算的虚拟表，无需维护冗余数据，每次查询都会返回最新的统计结果，完全满足图表生成需求。

创建统计视图：

CREATE VIEW habit_value_stats AS
SELECT 
    hv.habit_value,
    hv.habit_text,
    COUNT(hl.daily_habit_value) AS occurrence_count
FROM habit_values hv
LEFT JOIN habit_log hl ON hv.habit_value = hl.daily_habit_value
GROUP BY hv.habit_value, hv.habit_text;

使用时直接查询视图即可：

SELECT * FROM habit_value_stats;

• 优势：无需手动维护数据，永远和habit_log同步，不会出现数据不一致问题
• 适用场景：对查询性能要求不高，需要实时统计的场景

方案二：使用触发器维护存储列 #

如果需要将统计结果存储在表中（比如追求查询性能），可以给habit_values新增列，通过触发器自动更新计数。

步骤1：新增统计列 #

ALTER TABLE habit_values ADD COLUMN occurrence_count INT DEFAULT 0;

步骤2：初始化现有数据的统计值 #

UPDATE habit_values hv
SET occurrence_count = (
    SELECT COUNT(*) 
    FROM habit_log hl 
    WHERE hl.daily_habit_value = hv.habit_value
);

步骤3：创建INSERT触发器（新增日志时更新计数） #

DELIMITER //
CREATE TRIGGER update_habit_count_after_insert
AFTER INSERT ON habit_log
FOR EACH ROW
BEGIN
    UPDATE habit_values
    SET occurrence_count = occurrence_count + 1
    WHERE habit_value = NEW.daily_habit_value;
END //
DELIMITER ;

可选：处理删除和更新日志的情况 #

如果存在删除日志或修改日志中daily_habit_value的场景，需要补充以下触发器：

删除日志时的触发器

DELIMITER //
CREATE TRIGGER update_habit_count_after_delete
AFTER DELETE ON habit_log
FOR EACH ROW
BEGIN
    UPDATE habit_values
    SET occurrence_count = occurrence_count - 1
    WHERE habit_value = OLD.daily_habit_value;
END //
DELIMITER ;

修改日志中评级值时的触发器

DELIMITER //
CREATE TRIGGER update_habit_count_after_update
AFTER UPDATE ON habit_log
FOR EACH ROW
BEGIN
    -- 旧评级的计数减1
    UPDATE habit_values
    SET occurrence_count = occurrence_count - 1
    WHERE habit_value = OLD.daily_habit_value;
    -- 新评级的计数加1
    UPDATE habit_values
    SET occurrence_count = occurrence_count + 1
    WHERE habit_value = NEW.daily_habit_value;
END //
DELIMITER ;

• 优势：查询统计结果时无需计算，速度更快
• 注意事项：需要维护多个触发器，若直接修改habit_values的occurrence_count列，会导致数据与habit_log不一致

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