如何在SQL中使用GROUP BY对加密字段进行脱敏后的统计

GROUP BY 不能直接对加密字段脱敏分组，因密文分组不等价于明文逻辑分组；正确做法是使用 STORED 生成列预解密并建索引，或写入时存储脱敏标识（如手机号前缀、邮箱域名哈希）以支持高效、可索引的分组统计。

GROUP BY 不能直接对加密字段脱敏分组

数据库执行 GROUP BY encrypted_phone 时，只按密文字节值分组，不是按解密后的明文逻辑分组。比如两个相同手机号加密后生成不同密文（因IV随机或填充差异），就会被拆成两组；反过来，不同手机号若偶然生成相同密文（极小概率但存在），又会错误合并。更关键的是，GROUP BY AES_DECRYPT(encrypted_phone, 'key') 在 MySQL/PostgreSQL/SQL Server 中全部报错或拒绝执行——这不是语法限制，而是优化器无法为解密表达式生成有效执行计划。

MySQL 8.0+ 推荐用 STORED 生成列 + 索引

把解密逻辑固化进表结构，让数据库当普通字段处理：

• 必须用 STORED（VIRTUAL 不支持索引）：

  ALTER TABLE users ADD COLUMN phone_plain VARCHAR(20) GENERATED ALWAYS AS (CAST(AES_DECRYPT(encrypted_phone, 'my_key') AS CHAR)) STORED;

• 显式 CAST 转字符串，避免隐式转换导致索引失效
• 立刻建索引：

  CREATE INDEX idx_phone_plain ON users(phone_plain);

• 后续统计和普通字段一样：

  SELECT phone_plain, COUNT(*) FROM users GROUP BY phone_plain;

• 密钥轮换需 DROP COLUMN + 重建，且所有行会重新计算生成列值

SQL Server 和 PostgreSQL 更适合写入时存脱敏标识

运行时解密开销大、不可索引、密钥轮换难。高频统计场景应前置处理：

• 写入时额外存 phone_prefix CHAR(3) 或 email_domain_hash BINARY(32)
• 这些字段可建索引、可 GROUP BY、无解密开销
• 密钥轮换只需重算标识字段，不影响历史数据
• 别写 GROUP BY SUBSTRING(ENCRYPTBYKEY(...), 1, 10) —— 这种表达式无法走索引，每次都是全表扫描
• 脱敏掩码逻辑（如 CONCAT(LEFT(phone,3),'****',RIGHT(phone,4))）必须在 SELECT 或视图里完成，再对结果字段 GROUP BY

脱敏后分组 ≠ 用 GROUP BY 做脱敏

GROUP BY 只归类，不修改数据。想统计“138****1234”出现次数，必须先生成这个掩码值，再分组：

SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone, COUNT(*) FROM users WHERE LEN(phone) = 11 GROUP BY CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4));

常见错误是写成 SELECT phone, COUNT(*) FROM users GROUP BY phone，以为能“隐藏”号码——实际返回的仍是原始明文，且若没加聚合函数会直接报错。

真正容易被忽略的点：脱敏字段是否保留业务区分度。用 HASHBYTES('SHA2_256', email) 后再 GROUP BY，只能查重复哈希值，无法反推邮箱归属；而用前缀截取或区间映射（如 CASE WHEN age BETWEEN 20 AND 29 THEN '20s'），才能支撑有意义的分布统计。