MySQL 8 INSERT ... ON DUPLICATE KEY UPDATE ... 'VALUES function' is deprecated and will be removed in a future release.
insert into pre_daily_role_active (`role_id`, `log_date`, `level`, `occupation`)
select `role_id`, '2024-05-28' as log_date, level, occupation
from (
select role_id, logtime, level, `occupation`, row_number() over (partition by role_id order by logtime desc) as row_num
from
(
select roleid as role_id, logtime, lev as level, occupation from `log_rolelogin` where logdate = '2024-05-28'
union all
select roleid as role_id, logtime, lev as level, occupation from `log_rolelogout` where logdate = '2024-05-28'
union all
select roleid as role_id, logtime, lev as level, occupation from `log_levelup` where logdate = '2024-05-28'
) as role_info
) as subquery
where row_num = 1
on duplicate key update level = values(level), occupation = values(occupation);
多次尝试无法设置别名,后来查找blog修改为:
on duplicate key update level = subquery.level, occupation = subquery.occupation;
执行结果似乎是正确的,但是看起来又哪里不对劲,有大佬帮忙解答一下这种方式是否正确,或者说结果正确的原因?谢谢
其实这种问题最好解决,因为正常的软件或者语言迭代过程中都会无可避免的遗弃一些特性、语法。
而这些改动一般都是被视为对用户影响较大的,所以都会有完整的文档来说明为什么遗弃以及有哪些替代方法,就这个问题而言,其实搜一下就能找到 官方提供的说明 了。