on duplicate key update

on duplicate key update是mysql的特殊用法，当insert操作出现主键冲突时执行更新操作，样例如下：

insert into zhi_test.bd_user (id, user_code, user_name, create_time, update_time) values 
(1677751738004, 'zhangsan',  '张三', '2023-03-02 18:09:02', '2023-03-07 16:53:51'),
(1677751850422, 'lisi', '李四', '2023-03-02 18:10:52', '2023-03-07 16:53:51')
on duplicate key update
user_code = values(user_code), user_name = values(user_name);

 

mysql版本升级后（测试版本为8.0.32），使用上面的语句会出现警告：

'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

说的是values函数已经过期，我们可以用新的用法：

insert into zhi_test.bd_user (id, user_code, user_name, create_time, update_time) values 
(1677751738004, 'zhangsan', '张三', '2023-03-02 18:09:02', '2023-03-07 16:53:51'),
(1677751850422, 'lisi', '李四', '2023-03-02 18:10:52', '2023-03-07 16:53:51') as alias
on duplicate key update
user_code=alias.user_code, user_name = alias.user_name;