低于8.0版本MySQL如何将指定字符替换为列表随机字符实现数据匿名化

核心实现逻辑 #

利用MySQL原生的ELT()+RAND()组合实现随机取值，不需要正则支持，兼容所有5.x以上版本。

• RAND()：生成0~1之间的随机浮点数
• FLOOR(RAND()*N +1)：生成1~N之间的随机整数，对应候选值的下标
• ELT(下标, 候选1, 候选2, ..., 候选N)：根据下标返回对应位置的候选值

方案1：封装自定义函数（推荐，适合批量/多次使用） #

如果有数据库函数创建权限，直接封装通用随机替换函数，一次创建随时调用：

-- 开启函数创建权限（如果创建报错可以先执行这行）
SET GLOBAL log_bin_trust_function_creators = 1;
-- 创建通用随机替换函数
DELIMITER //
CREATE FUNCTION RANDOM_REPLACE(
    origin_str VARCHAR(2000), 
    target_char VARCHAR(10), 
    candidates VARCHAR(200)
) RETURNS VARCHAR(2000) CHARSET utf8mb4
DETERMINISTIC
BEGIN
    DECLARE len INT DEFAULT CHAR_LENGTH(origin_str);
    DECLARE i INT DEFAULT 1;
    DECLARE result_str VARCHAR(2000) DEFAULT '';
    DECLARE current_char VARCHAR(10);
    DECLARE cand_len INT DEFAULT CHAR_LENGTH(candidates);
    WHILE i <= len DO
        SET current_char = SUBSTRING(origin_str, i, 1);
        IF current_char = target_char THEN
            -- 随机取候选字符，最多支持20个候选，可按需补全参数
            SET result_str = CONCAT(result_str, ELT(FLOOR(RAND()*cand_len + 1), 
                SUBSTRING(candidates,1,1), SUBSTRING(candidates,2,1), SUBSTRING(candidates,3,1), SUBSTRING(candidates,4,1), SUBSTRING(candidates,5,1),
                SUBSTRING(candidates,6,1), SUBSTRING(candidates,7,1), SUBSTRING(candidates,8,1), SUBSTRING(candidates,9,1), SUBSTRING(candidates,10,1),
                SUBSTRING(candidates,11,1), SUBSTRING(candidates,12,1), SUBSTRING(candidates,13,1), SUBSTRING(candidates,14,1), SUBSTRING(candidates,15,1),
                SUBSTRING(candidates,16,1), SUBSTRING(candidates,17,1), SUBSTRING(candidates,18,1), SUBSTRING(candidates,19,1), SUBSTRING(candidates,20,1)
            ));
        ELSE
            SET result_str = CONCAT(result_str, current_char);
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN result_str;
END //
DELIMITER ;

调用示例 #

1. 将products表description列的所有u随机替换为a/e/i/f/k中的任意一个

UPDATE products 
SET productDescription = RANDOM_REPLACE(productDescription, 'u', 'aeifk');

2. 同时替换多个字符，比如同时替换u和e为指定候选集

UPDATE products 
SET productDescription = RANDOM_REPLACE(
    RANDOM_REPLACE(productDescription, 'u', 'aeifk'), 
    'e', 'aeifk'
);

3. 手机号匿名化：将手机号列所有3随机替换为0~9的任意数字

UPDATE 表名 
SET 手机号列 = RANDOM_REPLACE(手机号列, '3', '0123456789');

4. 中文字符（姓氏）匿名化：比如将所有李姓随机替换为王/张/刘/陈

UPDATE 用户表 
SET 姓氏 = RANDOM_REPLACE(姓氏, '李', '王张刘陈');

方案2：无函数权限的临时写法 #

如果没有创建函数的权限，针对单字符替换可以直接写UPDATE语句，适合少量数据场景：

-- 示例：替换description列的u为aeifk随机值
UPDATE products 
SET productDescription = REPLACE(
    productDescription, 
    'u', 
    ELT(FLOOR(RAND()*5 +1), 'a','e','i','f','k')
);

注意事项 #

• 执行任何UPDATE前务必先备份数据，或者先执行SELECT测试替换效果：SELECT RANDOM_REPLACE(列名, '目标字符', '候选值') FROM 表名 LIMIT 10;
• 中文字符替换需要确保表、列、函数的字符集统一为utf8/utf8mb4，避免乱码
• 如果需要替换的候选值超过20个，补全自定义函数中ELT的参数即可
• 如果要处理的字符串长度超过2000，修改函数中origin_str和result_str的长度上限即可

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