如何编写SQL语句按照特定业务规则对存量数据实施脱敏更新

脱敏更新前必须先备份表、确认字段NULL/默认值、检查外键或触发器依赖；不同场景需匹配对应SQL函数；务必加WHERE条件，否则将批量污染全表。

脱敏更新前必须确认的三件事

直接执行 UPDATE 脱敏语句极大概率导致数据不可逆损坏。先做这三件事：备份目标表（用 CREATE TABLE backup_table AS SELECT * FROM original_table）；确认字段是否允许 NULL 或有默认值（影响 REPLACE 类函数行为）；检查是否有外键或触发器依赖该字段（比如手机号变更会触发短信验证逻辑）。

常见脱敏场景对应的SQL写法

不同业务规则对应不同函数组合，不能套用统一模板：

• 身份证号保留前6位+后4位：UPDATE users SET id_card = CONCAT(LEFT(id_card, 6), '****', RIGHT(id_card, 4)) WHERE id_card IS NOT NULL AND LENGTH(id_card) = 18
• 手机号中间4位替换为星号：UPDATE users SET phone = INSERT(phone, 4, 4, '****') WHERE phone REGEXP '^1[3-9]d{9}$'
• 邮箱用户名部分哈希化（避免重复暴露）：UPDATE users SET email = CONCAT(SUBSTRING_INDEX(email, '@', 1), '_hash_', MD5(SUBSTRING_INDEX(email, '@', 1)), '@', SUBSTRING_INDEX(email, '@', -1)) WHERE email LIKE '%@%'

WHERE条件不加会批量污染全表

漏写 WHERE 是最常踩的坑，尤其在测试环境没报错就直接上线。真实案例：某次脱敏只针对“测试用户”，但忘了加 WHERE env = 'test'，结果生产环境所有用户邮箱被改成了哈希串。

安全写法分两步：

• 先用 SELECT COUNT(*) 验证范围：SELECT COUNT(*) FROM users WHERE status = 'active' AND created_at
• 再把 SELECT 改成 UPDATE，且必须带相同 WHERE
• 涉及多条件时用括号明确优先级，例如：WHERE (type = 'personal' OR type = 'temp') AND is_deleted = 0

大表脱敏要分批执行避免锁表

单次更新超10万行容易触发MySQL锁等待超时或主从延迟。用主键ID分片是最稳妥方式：

UPDATE users SET phone = INSERT(phone, 4, 4, '****') WHERE id BETWEEN 10000 AND 20000 AND phone IS NOT NULL;

后续按 id 区间递增执行，每次控制在5000–10000行。注意避开高峰期，且每批执行后查一下 SHOW PROCESSLIST 确认没有长时间运行的 UPDATE 进程。

真正麻烦的是字段类型不一致——比如想用 REPLACE 处理 TEXT 字段，但 MySQL 5.7 默认严格模式下会截断；或者目标列定义为 VARCHAR(11) 却塞入带星号的11字符，导致隐式截断丢失末尾字符。这些细节不测就翻车。