如何更新MySQL表JSON字段中的指定内容：替换view_1及调整database值

现有MySQL表及数据 #

CREATE TABLE my_tbl(
  id INT,
  dataset_query longtext
);
INSERT INTO my_tbl(id, dataset_query) VALUES (1, '{"database":1,"native":{"query":"SELECT * FROM view_1.device","template-tags":{}},"type":"native"}');
INSERT INTO my_tbl(id, dataset_query) VALUES (2, '{"database":1,"native":{"query":"SELECT id, name FROM view_1.request","template-tags":{}},"type":"native"}');
INSERT INTO my_tbl(id, dataset_query) VALUES (3, '{"database":3,"native":{"query":"SELECT id, name, age FROM view_3.person","template-tags":{}},"type":"native"}');

需要完成的修改 #

• 将"database":1修改为"database":2
• 将所有view_1替换为view_2

已完成的操作 #

已通过以下SQL完成database值的更新：

UPDATE
    my_tbl
SET
    dataset_query = JSON_SET(dataset_query, "$.database", 2) 
WHERE 
    json_extract(dataset_query, '$.database') = 1;

提问 #

如何更新my_tbl表的dataset_query列，将所有view_1替换为view_2？

预期结果 #

解决方案 #

要替换JSON结构中native.query字段里的view_1，可以结合JSON_SET和REPLACE函数，先提取目标字符串做替换，再写回JSON：

UPDATE my_tbl
SET dataset_query = JSON_SET(
    dataset_query,
    "$.native.query",
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(dataset_query, "$.native.query")), 'view_1', 'view_2')
)
WHERE JSON_UNQUOTE(JSON_EXTRACT(dataset_query, "$.native.query")) LIKE '%view_1%';

逻辑说明 #

1. JSON_EXTRACT(dataset_query, "$.native.query")：提取JSON中native对象下的query内容，此时结果带双引号，需用JSON_UNQUOTE去除
2. REPLACE(..., 'view_1', 'view_2')：对提取出的查询语句执行字符串替换
3. JSON_SET(...)：将替换后的内容重新写入JSON的对应位置
4. WHERE子句：仅更新包含view_1的记录，避免无意义操作

如果要把两项更新合并为一次执行，可使用：

UPDATE my_tbl
SET dataset_query = JSON_SET(
    JSON_SET(dataset_query, "$.database", 2),
    "$.native.query",
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(dataset_query, "$.native.query")), 'view_1', 'view_2')
)
WHERE json_extract(dataset_query, '$.database') = 1;

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