PostgreSQL+EF Core 加密列LIKE/ILIKE模糊搜索实现方案咨询

你当前基于值转换器的服务端加解密方案，本质是写入时应用层把明文加密为密文落库，读取时把库内密文解密为明文返回。精确匹配能正常运行，是因为查询时你会把待匹配的明文用相同规则加密为密文，再通过=匹配库内存储的密文，二者可以完全对应。
但LIKE/ILIKE是数据库层直接对存储的字段值做模式匹配，加密后的密文是经过混淆、散列处理的字符串，和明文的字符顺序、片段特征完全没有对应关系，自然无法直接通过密文完成模糊匹配。

方案1：数据库侧解密后匹配（改造量最小） #

如果你的数据库内置了和应用侧一致的加解密函数，直接在SQL层对加密列先做解密，再对解密得到的明文做LIKE/ILIKE匹配即可，现有值转换器的读写逻辑完全不需要改动。

• 适用场景：中小数据量（单表10万行以内），加解密算法为数据库原生支持（如AES、SM4等通用对称加密算法），解密密钥可安全传入数据库
• 实现示例（以PostgreSQL的AES-CBC加密为例）：

SELECT * FROM 你的业务表
WHERE convert_from(
  decrypt(加密列名, '你的解密密钥'::bytea, 'aes-cbc/pad:pkcs'),
  'UTF8'
) ILIKE '%检索关键词%';

• 注意事项：
  • 解密密钥不要硬编码在SQL中，通过参数化查询的方式从应用配置传入，避免密钥泄露
  • 该方案需要逐行做解密计算，无法利用普通B树索引，数据量过大会有明显性能问题

方案2：新增片段索引列（查询性能最优） #

如果单表数据量较大、对查询响应速度要求高，可以在原有加密列之外，额外存储用于模糊检索的明文片段映射，绕过密文无法匹配的问题。

• 适用场景：大数据量、高并发查询场景，模糊匹配规则相对固定
• 实现逻辑：
  • 写入数据时，除了将全量明文加密存入原加密列，同时根据常用的模糊查询粒度，将明文拆分为固定长度的连续字符片段（即n-gram分词，比如要支持任意位置模糊匹配，就拆分出所有长度为2-3的连续子串），将这些片段存入独立的检索索引表或同表的检索字段中
  • 如果需要支持大小写不敏感的ILIKE匹配，拆分片段前先将明文统一转为小写/大写即可
  • 查询时，先把检索关键词按相同规则拆成字符片段，通过片段索引快速筛出候选数据ID，再把候选ID对应的密文拉回应用层解密，做最终的精确模糊匹配过滤，排除片段匹配带来的误判
• 注意事项：片段索引会存储部分明文特征，高敏感数据（如身份证、银行卡号）需谨慎评估安全性后使用。

方案3：替换支持密文检索的加密方案 #

如果业务对数据安全性要求极高，不允许在数据库侧存储任何明文相关信息，可替换现有加密算法为原生支持密文检索的方案。

• 可选方向：
  • 保序加密（OPE）：可支持前缀类LIKE匹配，密文顺序和明文顺序一致，落地难度中等，安全性中等
  • 可搜索对称加密（SSE）：专门为密文检索场景设计，支持多种模糊匹配规则，安全性更高，但需要引入专用加密组件，改造成本较高
  • 同态加密：理论上支持任意密文计算，但当前性能极差，不适合线上业务场景使用
• 注意事项：该方案需要替换现有值转换器内的全部加解密逻辑，改造成本最高。

方案4：应用层内存过滤（零额外改造） #

如果你的表是数据量极小的配置表、字典表（单表1万行以内），不需要做任何逻辑改造：直接把查询范围内的全量数据拉到应用侧，值转换器会自动把密文解密为明文，直接在内存中做LIKE/ILIKE匹配即可。

• 缺点：数据量稍大就会出现查询慢、应用内存占用过高的问题，仅适合极小表使用。

内容的提问来源于stack exchange，提问作者Faizan Saeed