如何通过SQL在不新建表的前提下对已有表敏感列执行数据掩码

核心约束 #

• 不创建新表、不生成临时表，所有更新操作直接在已存在的Empsal表上执行
• 需掩码的字段为name（姓名）、手机号字段、ID（证件号），覆盖表内全量存量数据

前置提醒 #

执行更新操作前请务必完成全表数据备份，避免掩码后原始数据无法恢复

通用掩码规则 #

• 姓名：保留首字符，剩余字符全部用*替换
• 手机号：保留前3位、后4位，中间4位用*替换
• ID：保留前2位、后2位，中间所有字符用*替换

可直接执行的SQL语句 #

MySQL 环境 #

注意字段名带空格时需用反引号包裹，如果实际手机号字段名为无空格的mobileno，直接替换反引号内的字段名即可，语句如下：

UPDATE Empsal
SET
  `name` = CONCAT(LEFT(`name`, 1), REPEAT('*', CHAR_LENGTH(`name`) - 1)),
  `mobile no` = CONCAT(LEFT(`mobile no`, 3), '****', RIGHT(`mobile no`, 4)),
  `ID` = CONCAT(LEFT(`ID`, 2), REPEAT('*', CHAR_LENGTH(`ID`) - 4), RIGHT(`ID`, 2));

SQL Server 环境 #

字段名带空格或为关键字时需用方括号包裹，语句如下：

UPDATE Empsal
SET
  [Name] = CONCAT(LEFT([Name], 1), REPLICATE('*', LEN([Name]) - 1)),
  [mobileno] = CONCAT(LEFT([mobileno], 3), '****', RIGHT([mobileno], 4)),
  [ID] = CONCAT(LEFT([ID], 2), REPLICATE('*', LEN([ID]) - 4), RIGHT([ID], 2));

效果验证 #

执行完更新后，可抽样查询10条数据确认掩码结果符合预期：

• MySQL执行：SELECT * FROM Empsal LIMIT 10;
• SQL Server执行：SELECT TOP 10 * FROM Empsal;

若有特殊的掩码合规要求，可自行调整字符保留长度、替换占位符，核心更新逻辑不变即可。

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