MySQL中批量更新JSON列内嵌套URL的域名

针对你的需求——修改JSON字段formats中所有嵌套对象的url域名，保留文件名，这里提供两种实用方案：

方案一：针对已知嵌套键的直接更新 #

如果明确知道formats中只会出现large、small、medium、thumbnail这些键，可以直接用JSON_REPLACE结合字符串替换函数批量更新：

-- 替换指定键的url域名，替换前请确认表名和域名
UPDATE 你的表名
SET formats = JSON_REPLACE(
    formats,
    '$.large.url', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, '$.large.url')), 'https://test.s3.amazonaws.com/', 'https://example.com/'),
    '$.small.url', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, '$.small.url')), 'https://test.s3.amazonaws.com/', 'https://example.com/'),
    '$.medium.url', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, '$.medium.url')), 'https://test.s3.amazonaws.com/', 'https://example.com/'),
    '$.thumbnail.url', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, '$.thumbnail.url')), 'https://test.s3.amazonaws.com/', 'https://example.com/')
)
-- 过滤掉没有对应url的行，避免无效更新
WHERE JSON_CONTAINS_PATH(formats, 'one', '$.large.url', '$.small.url', '$.medium.url', '$.thumbnail.url');

代码说明： #

• JSON_EXTRACT(formats, '$.large.url')：取出large对象下的url值（带JSON引号）
• JSON_UNQUOTE(...)：去掉url值的引号，转换成普通字符串
• REPLACE(...)：替换域名部分
• JSON_REPLACE(...)：把替换后的url放回原JSON字段

方案二：动态处理所有未知嵌套键 #

如果formats中的嵌套键不固定（比如部分数据只有thumbnail），可以用存储过程自动识别所有键并批量更新：

-- 创建存储过程，替换前请修改表名和域名
DELIMITER //
CREATE PROCEDURE UpdateAllImageDomains()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE keyName VARCHAR(255);
    -- 提取formats字段中所有的嵌套键
    DECLARE cur CURSOR FOR SELECT DISTINCT j.key_name FROM 你的表名, JSON_TABLE(JSON_KEYS(formats), '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')) AS j;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO keyName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 动态生成更新语句
        SET @sql = CONCAT(
            'UPDATE 你的表名 SET formats = JSON_REPLACE(formats, ''$.', keyName, '.url'', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, ''$.', keyName, '.url'')), ''https://test.s3.amazonaws.com/'', ''https://example.com/'')) WHERE JSON_CONTAINS_PATH(formats, ''one'', ''$.', keyName, '.url'')'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程执行更新
CALL UpdateAllImageDomains();

注意事项： #

1. 操作前必须备份数据：

   -- 备份表结构和数据
   CREATE TABLE 你的表名_backup LIKE 你的表名;
   INSERT INTO 你的表名_backup SELECT * FROM 你的表名;
   

2. 先测试再执行：可以先选一行数据验证替换效果：

   SELECT JSON_REPLACE(formats, '$.large.url', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(formats, '$.large.url')), 'https://test.s3.amazonaws.com/', 'https://example.com/')) FROM 你的表名 LIMIT 1;
   

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