在涉及用户信息的应用系统里，手机号、身份证号、银行卡号这类敏感字段几乎无处不在。如果接口或报表直接把原始数据返回给前端，一旦日志泄露或权限失控，损失会非常严重。与其在应用层逐条处理，不如在SQL查询阶段就完成脱敏，让敏感数据在离开数据库之前就已经变成掩码形式。本文将详细讲解如何利用CASE表达式与嵌套子查询实现灵活的数据脱敏。
一、为什么选择在SQL层面做数据脱敏
数据脱敏可以在多个层面实施：应用层、中间件层、数据库层。应用层脱敏需要在每个业务代码路径上都写一遍掩码逻辑，一旦某个新接口忘记处理，就会造成数据裸奔。而SQL层脱敏只需维护一份查询逻辑，所有调用该SQL的接口天然获得脱敏能力。
在SQL层做脱敏的另一个好处是集中可控。可以把脱敏规则封装到视图里，也可以封装到一段固定的子查询中，DBA审查一次规则，所有下游都能复用。同时，脱敏后的数据仍然可以参与统计，例如按手机号前缀做地域分布分析，掩码不影响聚合结果。
需要注意的一点是，SQL层脱敏并不替代权限控制。它是一种防御纵深手段：即使某个只读账号被误授权，看到的也只是掩码后的数据。真正的完整数据访问，仍然应该由严格的账号权限体系来保障。
二、基础掩码：字符串函数的组合运用
最常见的脱敏需求是保留首尾几位，中间用星号替代。以MySQL为例，可以使用SUBSTRING截取片段，再用CONCAT拼接星号：
-- 手机号脱敏：138****5678
SELECT
CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, 8, 4)) AS masked_phone
FROM t_user;
-- 身份证脱敏：保留前6位和后4位
SELECT
CONCAT(SUBSTRING(id_card, 1, 6), '********', SUBSTRING(id_card, 15, 4)) AS masked_id
FROM t_user;
这种写法简单直接，但有一个明显缺点：长度不同的数据会被硬编码的截取位置坑到。如果某个手机号只有10位，截取结果就不对了。更稳妥的做法是使用REPLACE配合SUBSTRING动态生成星号，或者先校验长度再处理。
对于邮箱脱敏，还可以借助LOCATE定位@符号的位置，动态保留邮箱名首字符和域名部分：
-- 邮箱脱敏：z***@domain.com
SELECT
CONCAT(
LEFT(email, 1),
REPEAT('*', GREATEST(LOCATE('@', email) - 2, 1)),
SUBSTRING(email, LOCATE('@', email))
) AS masked_email
FROM t_user;
REPEAT根据实际长度生成对应数量的星号，GREATEST保证邮箱名只有一个字符时不会生成负数个星号。这种动态掩码的适应性比硬编码强得多。
三、CASE表达式实现条件化脱敏
现实中经常有这样的需求：普通客服只能看到脱敏数据，风控人员需要看到完整手机号。这时就需要CASE表达式根据当前查询者的角色，动态决定返回原始值还是掩码值。
-- 根据查询角色决定脱敏粒度
SELECT
u.user_id,
u.real_name,
CASE
WHEN :current_role = 'admin' THEN u.phone
WHEN :current_role = 'audit' THEN CONCAT(LEFT(u.phone, 3), '****', RIGHT(u.phone, 2))
ELSE CONCAT(LEFT(u.phone, 3), '****', RIGHT(u.phone, 4))
END AS phone
FROM t_user u;
上面的示例中，不同角色看到不同粒度的数据：admin看到完整号码，audit只多看到末尾两位，普通角色则使用标准掩码。参数:current_role由应用层传入，数据库本身不存储会话信息。
CASE表达式的另一个典型场景是处理空值和异常格式。脱敏函数遇到NULL或格式非法的字段时容易出错，可以用CASE先做防御性判断：
SELECT
CASE
WHEN phone IS NULL OR CHAR_LENGTH(phone) != 11
THEN phone -- 异常数据原样返回，便于排查
ELSE CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, 8, 4))
END AS masked_phone
FROM t_user;
这种写法把非法数据与正常数据区别对待，避免了把垃圾数据也掩码成看似合法的格式，反而干扰问题排查。
四、嵌套子查询：只对最终输出脱敏
当查询逻辑比较复杂时，直接在主查询里塞满掩码函数会让SQL难以维护。更优雅的做法是使用嵌套子查询分层处理：内层子查询完成业务过滤和关联，外层查询只负责脱敏输出。
-- 内层查询处理业务逻辑，外层统一脱敏
SELECT
user_id,
real_name,
CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone,
CONCAT(LEFT(id_card, 6), '********', RIGHT(id_card, 4)) AS id_card,
order_count
FROM (
SELECT
u.user_id,
u.real_name,
u.phone,
u.id_card,
COUNT(o.order_id) AS order_count
FROM t_user u
LEFT JOIN t_order o ON o.user_id = u.user_id
WHERE u.status = 1
GROUP BY u.user_id, u.real_name, u.phone, u.id_card
) AS t;
这种结构的好处是职责清晰：内层子查询专注业务语义，外层查询专注数据安全。如果脱敏规则变更，只需要修改外层的几个表达式，不用在复杂的JOIN和GROUP BY中间小心翼翼地改字段。
嵌套子查询还带来一个隐性安全优势。由于掩码发生在最外层，内层计算所使用的都是原始数据，聚合统计、去重、关联条件都不会受掩码影响。例如统计每个手机号的下单次数，如果先脱敏再分组，不同用户的掩码值可能碰撞导致计数错误，而先分组后脱敏就没有这个问题。
五、用视图封装脱敏规则
把嵌套查询固化成视图，是复用脱敏规则的推荐方式。创建一个只暴露脱敏字段的视图，普通业务账号只授权访问视图而不授权访问基表：
-- 创建脱敏视图
CREATE VIEW v_user_masked AS
SELECT
user_id,
real_name,
CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone,
CONCAT(LEFT(id_card, 6), '********', RIGHT(id_card, 4)) AS id_card,
email
FROM t_user;
-- 授权：普通账号只能查视图
GRANT SELECT ON mydb.v_user_masked TO 'app_read'@'%';
视图方案的优势在于一次定义处处生效，业务方写查询时完全感知不到脱敏的存在，写的就是普通的SELECT * FROM v_user_masked。缺点是视图无法携带角色参数，做不到前文提到的按角色差异化脱敏，除非借助数据库的会话变量：
-- MySQL中利用会话变量实现视图内的条件脱敏
CREATE VIEW v_user_masked AS
SELECT
user_id,
CASE
WHEN @mask_level = 'full' THEN phone
ELSE CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4))
END AS phone
FROM t_user;
-- 使用前先设置会话变量
SET @mask_level = 'masked';
SELECT * FROM v_user_masked;
需要提醒的是，会话变量方案要求应用在每次查询前正确设置变量，一旦遗漏就会回退到CASE的默认分支，因此默认分支必须设计成最严格的掩码级别，宁可脱敏过度也不能脱敏不足。
六、常见坑点与最佳实践
第一，掩码列参与索引会失效。外层的函数计算会导致无法走基表索引，因此强烈建议把过滤、排序条件都放在内层子查询完成，外层只做投影脱敏，这也是前面强调分层结构的原因。
第二，注意LIKE模糊查询的泄露风险。如果允许用户用完整手机号做精确查询，即使查询结果是脱敏的，攻击者也可以通过是否命中来逐位猜出手机号。对于敏感字段的检索，应改为对哈希后的辅助列做等值匹配，而不是开放明文LIKE。
第三，脱敏要覆盖所有出口。除了SELECT列表，还要检查ORDER BY、GROUP BY导出的报表、慢日志中记录的参数等。建议在代码评审时把敏感字段的SELECT语句列为重点检查项，并定期用information_schema扫描数据库中是否存在绕过视图直接查基表的授权。
总结来说，CASE表达式负责条件分支，子查询负责分层解耦，两者结合就能构建出结构清晰、粒度可控的SQL脱敏方案。再配合视图封装和严格的账号授权，就能形成一道覆盖数据库出口的可靠防线。