MySQL使用on duplicate key update时的注意事项

insert into … select … from … where … on duplicate key update … values 不生效？预期结果不一致？

背景

为了分析数据快捷方便，需要做一些宽表出来，某些数据汇总十分耗时。这里模拟一下实际中的的核心场景。

测试MySQL版本为 v8.0.25

官方文档相关地址：MySQL :: MySQL 8.0 Reference Manual :: 13.2.7.2 INSERT … ON DUPLICATE KEY UPDATE Statement

实验

test表结构: id、user_id、month、views、last_month_views、status（user_id和month为联合唯一索引）

目标1： 将5月份状态为0的 views 数据复制一份到6月份并将状态标记为1

insert into test(user_id,month,views,status)
  select user_id,'202306',views,1 
  from test 
  where month='202305' and status=0
on duplicate key update views=values(views),status=1

以上语句完美执行达到预期。

目标2：将5月份状态为0的 views 数据复制一份到6月份的字段为 last_month_views 并将状态标记为1

insert into test(user_id,month,last_month_views,status)
  select user_id,'202306',views,1 
  from test 
  where month='202305' and status=0
on duplicate key update last_month_views=values(views),status=1

由于官方文档并没有详尽给出我们这里相关场景的示例，试运行以上 sql 后并没有报错，但并没有出现预期结果。原因出在了 values() 函数的取值问题上。 values 并不会取子查询中的字段值，而是取 insert into table 中出现的字段值，由于 views 字段并没有出现在 insert into table 中导致数据全部默认为0

正确语句修改为下面后完美解决问题。

insert into test(user_id,month,last_month_views,status)
  select user_id,'202306',views,1 
  from test 
  where month='202305' and status=0
on duplicate key update last_month_views=values(last_month_views),status=1

---

以下为GPT交互的一些问题点：

问：使用 insert into … select … from … where … on duplicate key update … values 时发现当存在重复数据时，并不会更新是为什么

答：如果你在执行这个语句时发现没有更新操作被执行，可能有以下几个原因需要考虑：

1. 未指定要更新的列：确保在on duplicate key update子句中明确指定了要更新的列。如果你没有在这个子句中提供任何列名，那么在发生冲突时，将不执行任何更新。
2. 没有真实的重复数据：请检查目标表中的唯一键或主键以确定是否实际上有重复数据。有时候看起来相同的行并不是真正的重复数据，而是具有不同的唯一标识值。
3. 更新值与已有值相同：如果要更新的列中的值与目标表中已有的值相同，则MySQL认为没有必要执行更新操作。只有在新值与旧值不同时，才会触发更新。

请检查以上条件并确认你的语句是否正确设置了要更新的列和符合预期的数据。

问：帮我找出问题所在。insert into test(user_id,month,last_month_views,status) select user_id,’202306′,views,1 from test where month=’202305′ and status=0 on duplicate key update last_month_views=values(views),status=1

答：下面是你提供的SQL语句的正确写法：

INSERT INTO test (user_id, month, last_month_views, status)
SELECT user_id, '202306', views, 1 
FROM test 
WHERE month = '202305' AND status = 0
ON DUPLICATE KEY UPDATE 
    last_month_views = VALUES(last_month_views),
    status = VALUES(status);

在这个修正后的语句中，我们使用了VALUES()函数来引用插入操作中的列名称所关联的结果数据集的值。通过这种方式，当发生唯一键冲突时，将正确地更新相关的列。

请确保目标表 test 中的列名拼写正确，并且适当设置了唯一键或主键约束来标识记录是否重复。如果问题仍然存在，请提供任何出现的错误消息或实际数据示例，以便进一步帮助排查问题。