MySQL使用ON DUPLICATE KEY UPDATE时如何局部更新JSON对象指定键值

问题核心原因 #

你之前的写法核心是混淆了SQL表/字段别名和JSON内部路径的用法，同时JSON_SET的传参规则也有错误：

1. JSON路径中不能直接写SQL层的表/字段别名，路径只对应JSON对象内部的键结构
2. JSON_SET的第三个参数是你要写入的实际值，不能直接写路径字符串，除非你要把路径本身当值存储
3. ON DUPLICATE KEY UPDATE场景下，修改的是原有已存在行的JSON字段，要明确区分「原有行的字段值」和「新插入行的字段值」

常见错误写法问题说明 #

• 第一种写法：'$.attributes.el'是错误路径，你要操作的JSON对象本身就是attributes字段的值，不需要在路径里再补attributes前缀；同时第三个参数传字符串'$.attributes.el'会直接把这个字符串写入对应键，不会读取路径对应的值
• 第二种写法：'$.t.el'中的t是SQL层的表别名，不属于JSON内部结构，路径完全匹配不到对应键，所以更新无效
• 第三种写法：'$.t[2]'路径不存在，JSON_REPLACE会直接返回null，导致整个JSON字段被覆盖为null

正确实现方式 #

前提：你的表中name字段已设置唯一索引，否则ON DUPLICATE KEY UPDATE不会触发。
如果需要在重复时，仅更新原有JSON中的stock字段为新插入行的stock值，写法如下：

INSERT INTO your_table (name, attributes) VALUES
("Sarah", JSON_OBJECT('profile', "F", "el", "[4, 5, 6]", "stock", 100)),
("John",  JSON_OBJECT('profile', "M", "el", "[10]", "stock", 200))
AS new_row
ON DUPLICATE KEY UPDATE
  -- JSON_SET(原有行的JSON字段, 要修改的JSON内部路径, 要写入的新值)
  attributes = JSON_SET(your_table.attributes, '$.stock', new_row.attributes->>'$.stock');

语法说明：

• new_row.attributes->>'$.stock'是JSON_UNQUOTE(JSON_EXTRACT(new_row.attributes, '$.stock'))的简写，用于读取新插入行的attributes中stock键的实际值
• 如果你希望仅当stock键已存在时才更新，不需要自动新增该键，把JSON_SET换成JSON_REPLACE即可
• 要修改其他JSON键，只需要把'$.stock'换成对应键的路径即可，比如修改el就写'$.el'

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