如何在MySQL中更新存储JSON数据的TEXT类型字段

错误原因 #

• 语法不合法：JSON_REPLACE 第一个参数要求传入JSON类型值，你直接传入未包裹的SELECT子句不符合函数参数规则，即使要使用子查询也需要用括号将SELECT语句包裹，且确保子查询仅返回单行单列结果。
• 类型不匹配：你用于存储JSON的字段是TEXT类型，MySQL的JSON处理函数无法直接识别TEXT类型的JSON文本，需要先通过CAST函数将TEXT值转换为JSON类型。
• 用法错误：更新表内数据需要使用UPDATE语句结构，直接调用JSON_REPLACE函数无法完成表数据的修改操作。

正确更新语句 #

UPDATE payment_options 
SET text_column = JSON_REPLACE(
    CAST(text_column AS JSON), 
    '$.card_number', 
    'update to this'
)
WHERE id = 1;

可选优化方案 #

如果业务长期需要存储JSON数据，建议直接将字段类型修改为JSON类型，后续不需要每次手动转换类型，还能获得MySQL自带的JSON格式合法性校验能力，修改字段的语句如下：

ALTER TABLE payment_options MODIFY COLUMN text_column JSON;

修改完字段类型后，更新语句可以简化为：

UPDATE payment_options 
SET text_column = JSON_REPLACE(text_column, '$.card_number', 'update to this')
WHERE id = 1;

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