如何在MySQL中利用函数实现敏感手机号与身份证脱敏展示-mysql教程-PHP中文网

mysql中可用substring和concat组合实现手机号脱敏（如concat(substring(phone,1,3),'**',substring(phone,-4))），身份证需按场景保留前6位和后4位或出生年份，混合类型用case when分类处理，高频使用可封装自定义函数。

如何在mysql中利用函数实现敏感手机号与身份证脱敏展示

MySQL中用SUBSTRING和CONCAT脱敏手机号

手机号脱敏最常见的是保留前3位和后4位，中间用星号替换。MySQL没有现成的“脱敏函数”，但用SUBSTRING和CONCAT组合就能一行搞定：

SELECT CONCAT(SUBSTRING(phone, 1, 3), '****', SUBSTRING(phone, -4)) AS masked_phone FROM users;

注意SUBSTRING(phone, -4)取末4位，比SUBSTRING(phone, LENGTH(phone)-3, 4)更简洁且兼容MySQL 5.7+；如果字段含空格或短横线（如'138-1234-5678'），得先用REPLACE清洗，否则位置计算会错。

常见错误：直接写SUBSTRING(phone, 4, 7)想截中间7位——实际可能因长度不一导致越界或漏位；更稳妥的做法是统一用LENGTH动态算：SUBSTRING(phone, 4, LENGTH(phone)-7)，但不如负索引直观。

身份证号脱敏要分场景处理

身份证18位，常规脱敏是保留前6位（地址码）和后4位（校验码+顺序码），中间12位掩码。但要注意：不同业务对“前6位”要求不同——有的只要前2位（省份），有的要前6位（到区县），还有的要求保留出生年份（第7–10位）。所以不能硬编码位置：

通用方案（前6+后4）：CONCAT(SUBSTRING(id_card, 1, 6), '**********', SUBSTRING(id_card, -4))
保出生年（第7–10位）：CONCAT(SUBSTRING(id_card, 1, 6), SUBSTRING(id_card, 7, 4), '******', SUBSTRING(id_card, -4))
若字段含X校验码，确保大小写一致——MySQL默认不区分，但应用层展示时X必须大写，建议在脱敏后用UPPER兜底：UPPER(SUBSTRING(id_card, -1))

性能提示：这些函数在SELECT里执行无索引消耗，但若用于WHERE条件（如模糊查脱敏后号码），将无法走索引，务必避免。

用CASE WHEN适配多类型字段混合脱敏

一张表里可能同时存手机号、身份证、邮箱等，字段类型混杂。直接写多个CONCAT易出错，用CASE WHEN按字段标识分类处理更可控：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SELECT 
  CASE 
    WHEN id_type = 'phone' THEN CONCAT(SUBSTRING(val, 1, 3), '****', SUBSTRING(val, -4))
    WHEN id_type = 'id_card' THEN CONCAT(SUBSTRING(val, 1, 6), '**********', SUBSTRING(val, -4))
    ELSE val 
  END AS masked_val
FROM identity_table;

关键点：id_type必须是明确的枚举值（如'phone'/'id_card'/'email'），不能依赖字段长度判断（比如“长度=11就是手机”），因为虚拟运营商号段有11位以上，港澳台证件也可能干扰判断。

容易踩的坑：没加ELSE分支，遇到未知类型时返回NULL，前端展示成空白，排查困难；建议默认返回原值或打上'[unknown]'标记便于追踪。

创建自定义函数提升复用性

频繁脱敏时，把逻辑封装成函数最省事。MySQL允许创建DETERMINISTIC函数，但要注意权限和SQL_MODE限制：

DELIMITER $$
CREATE FUNCTION mask_idcard(id VARCHAR(18)) 
RETURNS VARCHAR(18)
READS SQL DATA
DETERMINISTIC
BEGIN
  IF LENGTH(id) = 18 THEN
    RETURN CONCAT(SUBSTRING(id, 1, 6), '**********', SUBSTRING(id, -4));
  ELSEIF LENGTH(id) = 15 THEN
    RETURN CONCAT(SUBSTRING(id, 1, 6), '******', SUBSTRING(id, -3));
  ELSE
    RETURN id;
  END IF;
END$$
DELIMITER ;

调用：SELECT mask_idcard('11010119900307299X');

注意：函数内不能有SELECT ... INTO或临时表操作；若服务器启用了sql_safe_updates，需临时关闭；生产环境部署前，务必在测试库验证15位老身份证和带X的18位证是否都覆盖。

真正麻烦的不是写函数，而是后续修改——一旦函数被视图或存储过程引用，ALTER FUNCTION可能失败，得先DROP再重建，这点常被忽略。