MySQL JSON字段更新后格式不符致脚本报错求助

问题原因 #

你的JSON列中原有数据并非直接存储JSON对象，而是将JSON字符串进行二次转义后以JSON字符串类型存储（即列内实际是带外层双引号、内部引号转义的字符串值，而非原生JSON对象）。虽然列类型定义为JSON，但MySQL允许存储字符串类型的JSON值，这就导致你直接存入原生JSON对象时，格式和原有数据完全不同，触发脚本报错。

解决方案 #

使用MySQL内置的JSON_QUOTE()函数，将你要存入的JSON对象字符串转换为带外层双引号、内部引号自动转义的格式，和原有数据保持一致。

修正后的UPDATE语句 #

UPDATE case_inputs 
SET data = JSON_QUOTE('{"inv_start_time": "2022-02-01 00:00:00", ...}')
WHERE case_id = 45 AND data_type = 1;

验证方法 #

执行以下语句查看数据类型，确认原有数据和新数据的类型统一：

SELECT data, JSON_TYPE(data) FROM case_inputs WHERE case_id = 45 AND data_type = 1;

原有数据的JSON_TYPE结果应为STRING，使用JSON_QUOTE()更新后，新数据的JSON_TYPE也会是STRING，格式完全匹配原有数据。

补充说明 #

如果后续希望将所有数据统一为原生JSON对象格式（更符合JSON列的设计初衷），可以批量更新原有数据：

UPDATE case_inputs 
SET data = JSON_UNQUOTE(data)
WHERE JSON_TYPE(data) = 'STRING';

这条语句会将存储为字符串的JSON值转换为原生JSON对象，之后更新时直接存入JSON对象字符串即可。

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