使用UPDATE JOIN JSON_TABLE无法为可空字段更新NULL值的问题

问题描述 #

我创建了包含product_value字段（类型为decimal(11,2) NULLABLE）的TABLE_A表，尝试通过JSON_TABLE关联更新语句将该字段设为NULL：

UPDATE TABLE_A src 
JOIN JSON_TABLE("[{\"id\":1,\"product_value\":null}]",'$[*]' COLUMNS 
(id int(11) PATH '$.["id"]',
product_value decimal(11,2) PATH '$.["product_value"]' NULL ON EMPTY)) as target 
ON src.id = target.id 

SET 
src.product_value = null
where src.id = 1;

执行后报错：

Invalid JSON value for CAST to DECIMAL from column product_value

但使用普通表关联的更新语句可以成功将该字段设为NULL：

UPDATE TABLE_A src 
JOIN  TABLE_B as target ON src.u_id = target.u_id 
SET src.product_value = null
WHERE src.id = 1

疑问：为何使用JSON_TABLE关联时无法更新NULL值？如何解决？

原因分析 #

你在JSON_TABLE中只指定了NULL ON EMPTY，这个选项仅处理JSON字段不存在或者字段值为空字符串的场景，会将其转换为SQL NULL。但你的JSON数据里明确存在product_value: null（JSON类型的null），这不属于EMPTY的范畴，MySQL会尝试将JSON null直接转换为decimal(11,2)类型，而JSON null无法直接被CAST为数值类型，因此触发报错。

而普通表关联时，目标表的字段本身就是SQL NULL，不存在类型转换的问题，所以可以正常执行。

解决建议 #

在JSON_TABLE的product_value字段定义中添加NULL ON NULL选项，让MySQL将JSON类型的null转换为SQL NULL，修改后的SQL语句如下：

UPDATE TABLE_A src 
JOIN JSON_TABLE("[{\"id\":1,\"product_value\":null}]",'$[*]' COLUMNS 
(id int(11) PATH '$.["id"]',
product_value decimal(11,2) PATH '$.["product_value"]' NULL ON EMPTY NULL ON NULL)) as target 
ON src.id = target.id 

SET 
src.product_value = null
where src.id = 1;

如果需要同时覆盖字段不存在、空值、JSON null三种场景，NULL ON EMPTY NULL ON NULL是最全面的写法；如果只需要处理JSON null的情况，单独添加NULL ON NULL也可以解决当前问题。

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