如何在MySQL嵌套JSON字段中替换指定字符串（phpMyAdmin环境）

问题背景 #

MySQL表mytable中有一个嵌套JSON类型的列fields，数据结构如下：

{
  "15": {
    "name": "field1",
    "value": "http://randomwebsite.com/wp-content/uploads/wpforms/8231-9dfa327b1cf628635799d85de588101b/test1.pdf",
    "value_raw": [
      {
        "name": "test.pdf",
        "value": "http://randomwebsite.com/wp-content/uploads/wpforms/8231-9dfa327b1cf628635799d85de588101b/test1.pdf",
        "file": "test1.pdf",
        "file_original": "test.pdf",
        "ext": "pdf",
        "attachment_id": 0,
        "id": 15,
        "type": "application/pdf"
      }
    ],
    "id": 15,
    "type": "file-upload",
    "style": "modern"
  }
}

需要将所有出现的http://randomwebsite.com替换为https://rightwebsite.me，但直接用REPLACE语句无效，尝试JSON_EXTRACT时出现#1305 - FUNCTION 111111_wp2.JSON_EXTRACT does not exist错误。

问题原因分析 #

1. REPLACE无效：因为fields是JSON类型字段，直接对JSON类型调用REPLACE函数，MySQL无法正确解析处理，需要先将JSON转为字符串再操作。
2. JSON_EXTRACT报错：你错误地给函数添加了数据库前缀111111_wp2.，正确用法是直接使用JSON_EXTRACT；另外如果你的MySQL版本低于5.7，本身不支持JSON函数，需用字符串替换方案。

解决方案 #

方案一：批量替换所有匹配的域名（简单高效） #

将JSON字段转为字符串，替换后再转回JSON类型，适合批量替换所有出现的旧域名：

-- 先测试替换效果（执行后查看结果是否符合预期）
SELECT CAST(REPLACE(CAST(fields AS CHAR), 'http://randomwebsite.com', 'https://rightwebsite.me') AS JSON) 
FROM mytable 
WHERE fields LIKE '%http://randomwebsite.com%';

-- 确认无误后执行更新
UPDATE mytable
SET fields = CAST(REPLACE(CAST(fields AS CHAR), 'http://randomwebsite.com', 'https://rightwebsite.me') AS JSON)
WHERE fields LIKE '%http://randomwebsite.com%';

方案二：精准更新指定JSON路径（适合需要精确控制的场景） #

如果只需要更新特定路径下的字段（比如$.15.value和$.15.value_raw[0].value），使用JSON函数精准操作（要求MySQL版本≥5.7）：

-- 测试更新效果
SELECT JSON_REPLACE(
    fields,
    '$.15.value',
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(fields, '$.15.value')), 'http://randomwebsite.com', 'https://rightwebsite.me'),
    '$.15.value_raw[0].value',
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(fields, '$.15.value_raw[0].value')), 'http://randomwebsite.com', 'https://rightwebsite.me')
) AS updated_fields
FROM mytable
WHERE JSON_CONTAINS_PATH(fields, 'one', '$.15.value', '$.15.value_raw[0].value');

-- 执行更新
UPDATE mytable
SET fields = JSON_REPLACE(
    fields,
    '$.15.value',
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(fields, '$.15.value')), 'http://randomwebsite.com', 'https://rightwebsite.me'),
    '$.15.value_raw[0].value',
    REPLACE(JSON_UNQUOTE(JSON_EXTRACT(fields, '$.15.value_raw[0].value')), 'http://randomwebsite.com', 'https://rightwebsite.me')
)
WHERE JSON_CONTAINS_PATH(fields, 'one', '$.15.value', '$.15.value_raw[0].value');

phpMyAdmin操作步骤 #

1. 登录phpMyAdmin，选择目标数据库和mytable表；
2. 点击顶部「SQL」标签，输入上述测试用的SELECT语句，点击「执行」确认替换效果；
3. 确认结果正确后，再输入UPDATE语句执行更新（注意：执行UPDATE前建议先备份表数据）。

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