SQL加密字段脱敏后GROUP BY统计方法

在SQL中对加密字段做分组统计，确实是个让人头疼的问题——直接拿密文去GROUP BY，结果往往是错的。道理很简单：数据库的GROUP BY按字节值归类，而加密字段的密文因为随机IV或填充差异，同一明文加密后可能生成不同的密文字节序列。两个相同的手机号被当成两行数据，却把两个不同的手机号因为极小概率的碰撞合并到了一起，这显然不是我们想要的结果。

更棘手的是，直接在SQL里写GROUP BY AES_DECRYPT(...)这类解密表达式时，MySQL、PostgreSQL、SQL Server等主流数据库要么报语法错误，要么直接拒绝执行。这并非单纯的语法限制，而是优化器无法为解密函数生成有效的执行计划。

MySQL 8.0+的推荐方案：STORED生成列+索引

一种比较干净的做法是把解密逻辑固化进表结构，让数据库把它当作普通字段处理。这里的关键是使用STORED生成列——VIRTUAL类型的生成列无法建立索引，所以一定要用STORED。

具体来说，创建生成列时，需要显式地用CAST将解密结果转为字符串，避免隐式转换导致索引失效。语法大概是这样的：

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

紧接着为这个生成列创建索引：

CREATE INDEX idx_phone_plain ON users(phone_plain);

之后，统计查询就和普通字段一样了：

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

需要留意的是，密钥轮换相对麻烦——需要先DROP COLUMN再重建，因为所有行会重新计算生成列的值。

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

对于SQL Server和PostgreSQL来说，运行时解密开销大、不易索引、密钥轮换困难等问题更突出。在高频统计的场景下，更好的策略是前置处理——在数据写入时就额外存储脱敏标识字段。

比如存储手机号前3位前缀（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，然后GROUP BY也是phone——以为这样能“隐藏”号码，实际返回的仍然是原始明文。更严重的是，如果不加聚合函数，数据库还会直接报错。

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