如何在MariaDB 10.5数据表列中替换部分字符串内容

场景分析 #

你的humans表area列存储的是JSON格式的字符串数组，需要将所有数组中的area1精确替换为area 01，以下分两种情况给出解决方案：

情况1：area列是字符串类型（VARCHAR/TEXT） #

你原有的SQL存在误匹配风险（比如会把"area10"中的area1也替换成area 01），优化后可以精确匹配数组中的"area1"元素：

-- 先测试替换结果，确认无误后再执行UPDATE
SELECT area, REPLACE(area, '"area1"', '"area 01"') AS new_area 
FROM humans 
WHERE area LIKE '%"area1"%';

-- 确认正确后执行更新
UPDATE humans
SET area = REPLACE(area, '"area1"', '"area 01"')
WHERE area LIKE '%"area1"%';

• 用"area1"作为匹配目标，确保只替换数组中独立的area1元素，避免误改其他包含area1的字符串。
• 先执行SELECT验证结果，防止意外修改数据。

情况2：area列是JSON类型（MariaDB 10.5原生支持） #

如果area是JSON类型，直接字符串替换可能破坏JSON结构，建议用原生JSON函数精确处理数组元素：

假设表有主键id，执行以下SQL：

-- 先测试替换结果
SELECT 
    h.id,
    h.area,
    JSON_ARRAYAGG(
        CASE WHEN j.value = 'area1' THEN 'area 01' ELSE j.value END
    ) AS new_area
FROM humans h
JOIN JSON_TABLE(
    h.area,
    '$[*]' COLUMNS(value VARCHAR(255) PATH '$')
) j
WHERE JSON_CONTAINS(h.area, '"area1"', '$')
GROUP BY h.id;

-- 确认正确后执行更新
UPDATE humans h
JOIN (
    SELECT 
        h.id,
        JSON_ARRAYAGG(
            CASE WHEN j.value = 'area1' THEN 'area 01' ELSE j.value END
        ) AS new_area
    FROM humans h
    JOIN JSON_TABLE(
        h.area,
        '$[*]' COLUMNS(value VARCHAR(255) PATH '$')
    ) j
    GROUP BY h.id
    WHERE JSON_CONTAINS(h.area, '"area1"', '$')
) jh ON h.id = jh.id
SET h.area = jh.new_area;

• 用JSON_TABLE将数组拆分为单行数据，再通过CASE语句替换目标元素，最后用JSON_ARRAYAGG重新聚合为数组。
• JSON_CONTAINS确保只处理包含area1的行，提升效率。

关键注意事项 #

1. 备份优先：执行UPDATE前务必备份数据，或者在测试环境验证逻辑。
2. 性能优化：WHERE条件可以过滤掉不需要更新的行，避免全表扫描。

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