事倍功半是蠢蛋40 postgresql/mysql 解决单个字符的转义问题

如果你的数据库里存了很多["\u6b22\u8fce", "\u60a8"]
但是你前端查询输入的是中文['查询'] 就会出现找不到数据的情况
你需要更正数据库中的数据。

这个demo由 cursor代码生成，本人验证过可用：。

第一步创建错误数据

-- 创建测试表
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    tags JSON
);

-- 插入示例数据（注意：这里使用两个反斜杠在SQL中表示一个反斜杠）
INSERT INTO test_table (name, tags) VALUES 
('示例1', '["\u6d4b\u8bd5", "\u793a\u4f8b", "\u6f14\u793a"]'),
('示例2', '["你好", "欢迎您"]'),
('示例3', '["\u4f60\u597d", "\u4e16\u754c", "正常文本"]'),
('示例4', '["\u6b22\u8fce", "\u60a8"]');

-- 查看实际存储的数据（会显示单个反斜杠）
SELECT id, name, tags FROM test_table;

第二步：执行修复
注意：第一步和第二步要分开执行 一块执行不知道为什么navicat里不能一次执行第二步的SET

CREATE OR REPLACE FUNCTION fix_json_unicode_and_backslashes(input_json JSON)
RETURNS JSON AS $$
DECLARE
    result JSON;
    tag_array TEXT[];
    fixed_tag TEXT;
    i INTEGER;
    array_len INTEGER;
BEGIN
    -- 如果输入为空，直接返回
    IF input_json IS NULL THEN
        RETURN NULL;
    END IF;
    
    -- 将JSON数组转换为文本数组
    SELECT array_agg(value::text) INTO tag_array
    FROM json_array_elements_text(input_json);
    
    -- 检查数组长度，如果为NULL则说明是空数组
    array_len := array_length(tag_array, 1);
    
    -- 如果数组为空或者长度为NULL，返回空JSON数组
    IF array_len IS NULL OR array_len = 0 THEN
        RETURN '[]'::JSON;
    END IF;
    
    -- 处理每个标签
    FOR i IN 1..array_len LOOP
        fixed_tag := tag_array[i];
        
        -- 处理Unicode编码
        WHILE fixed_tag ~ '\\u[0-9a-fA-F]{4}' LOOP
            fixed_tag := regexp_replace(
                fixed_tag, 
                '\\u([0-9a-fA-F]{4})', 
                chr(('x' || '\1')::int),
                'g'
            );
        END LOOP;
        
        -- 处理反斜杠（将单个\变成\\，但保护已经转义的）
        fixed_tag := regexp_replace(fixed_tag, '(?<!\\)\\(?!\\)', '\\\\', 'g');
        
        tag_array[i] := fixed_tag;
    END LOOP;
    
    -- 重新构建JSON数组
    SELECT json_agg(tag) INTO result
    FROM unnest(tag_array) AS tag;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;


-- 使用函数修复数据，别忘了改表名字！
UPDATE test_table 
SET tags = fix_json_unicode_and_backslashes(tags)
WHERE tags IS NOT NULL;

-- 查看实际存储的数据（会显示单个反斜杠）
SELECT id, name, tags FROM test_table;