ON DUPLICATE KEY UPDATE语句未更新反而插入新记录的问题排查

我有一张名为tbl_visitors的表，结构如下：

我需要将该表中的记录按月份、年份、site_id分组后，写入tbl_visitors_monthly表，目标表结构如下：

tbl_visitors_monthly的主键是id和vis_site_id，但我写的SQL始终只会插入新记录，无法实现「存在对应站点、月份、年份的记录则更新，否则插入」的需求。我的SQL语句如下：

INSERT INTO tbl_sys_sites_visitors_monthly (vis_month, vis_year, vis_total_visitors, vis_site_id) 
SELECT MONTH(vis_at) AS vis_month,YEAR(vis_at) AS vis_year,COUNT(*) AS total_visitors,vis_site_id 
FROM tbl_sys_sites_visitors 
GROUP BY vis_month, vis_year, vis_site_id 
ON DUPLICATE KEY UPDATE vis_total_visitors=VALUES(vis_total_visitors)

请问我哪里出错了？该如何修复？

错误原因 #

INSERT...ON DUPLICATE KEY UPDATE触发更新的核心是插入记录与表中已有记录发生主键或唯一键冲突。你当前的tbl_visitors_monthly主键是(id, vis_site_id)，但id是唯一标识（通常是自增），每次插入的新记录id都是全新值，不会和已有记录的主键组合冲突，自然只会一直插入新数据。而你需要判断重复的维度是vis_month、vis_year、vis_site_id，当前的主键约束覆盖不到这个维度。

另外你的SQL还存在两处细节错误：

• 表名不一致：目标表是tbl_visitors_monthly，但SQL里写的是tbl_sys_sites_visitors_monthly
• 字段名不匹配：目标表统计字段是total_visitors，SQL里用的是vis_total_visitors

修复步骤 #

1. 添加唯一复合索引
   在tbl_visitors_monthly上创建覆盖vis_month、vis_year、vis_site_id的唯一索引，这样相同维度的记录插入时会触发冲突，进而执行更新：

   ALTER TABLE tbl_visitors_monthly 
   ADD UNIQUE INDEX idx_month_year_site (vis_month, vis_year, vis_site_id);
   

   注：不需要修改原主键(id, vis_site_id)，保留原主键的同时新增该唯一索引即可。

2. 修正SQL语句
   统一表名和字段名，确保逻辑匹配：

   INSERT INTO tbl_visitors_monthly (vis_month, vis_year, total_visitors, vis_site_id) 
   SELECT 
       MONTH(vis_at) AS vis_month,
       YEAR(vis_at) AS vis_year,
       COUNT(*) AS total_visitors,
       vis_site_id 
   FROM tbl_visitors 
   GROUP BY vis_month, vis_year, vis_site_id 
   ON DUPLICATE KEY UPDATE total_visitors = VALUES(total_visitors);
   

补充说明 #

如果tbl_visitors_monthly的id是自增字段，插入时无需手动指定，数据库会自动生成，不会影响唯一索引的冲突检测逻辑。

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