使用JSON_SET更新JSON列时变量赋值致字段为null问题

问题描述 #

尝试更新JSON类型列时，QUERY1可正常执行并完成更新，但使用变量的QUERY2却将amount字段更新为null。已尝试用CAST转换类型，问题依旧，确定根源在变量使用方式。

QUERY1（正常执行） #

SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f'; 
update `json` 
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", 1504.55) where `uuid` = @uuid;

（注：原QUERY1的WHERE子句中uuid未加@属于笔误，因字段名与变量名冲突时MySQL优先取字段值，恰好匹配才执行成功，正确写法需加@）

QUERY2（执行后amount为null） #

SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f'; 
SET @in_amount = 1504.55;
update `json` 
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", in_amount) where `uuid` = uuid;

问题原因 #

QUERY2存在两处变量引用错误：

• JSON_SET的第三个参数使用in_amount，但MySQL用户变量必须以@开头，此处实际引用了不存在的列名，导致传入值为null
• WHERE子句中的uuid未加@，会被解析为表的uuid字段而非用户变量@uuid，属于错误写法

修复后的QUERY2 #

SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f'; 
SET @in_amount = 1504.55;
update `json` 
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", @in_amount) where `uuid` = @uuid;

验证说明 #

修正后，JSON_SET会正确读取用户变量@in_amount的数值，WHERE子句也能精准匹配指定uuid，即可正常更新amount字段，不会再出现null值。

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