如何利用PostgreSQL的pgp_sym_encrypt/decrypt实现DELETE语句LIKE匹配删除？

问题根源 #

你用pgp_sym_encrypt生成加密值匹配LIKE条件失败，核心原因是pgp_sym_encrypt默认使用随机初始化向量（IV）——即使明文和加密参数完全相同，每次生成的密文也不一样，自然无法匹配数据库中已存储的加密值。

正确的SQL语句 #

要实现目标，需先解密存储字段，再和目标字符串做LIKE匹配：

DELETE FROM estado
WHERE pgp_sym_decrypt(nome::bytea, 'AbcdAbcdAbcdAbcd', 'cipher-algo=aes192') LIKE '%Rio%';

关键说明 #

• 解密参数必须和加密时完全一致：密码、加密算法要和存储数据时pgp_sym_encrypt的参数对应，否则无法正确解密。
• 字段类型转换：如果nome字段存储为text类型，需先转为bytea（pgp_sym_decrypt第一个参数要求是bytea类型）；若存储时直接用bytea，可去掉::bytea转换。
• 匹配规则调整：如果是精确删除值为Rio的记录，把LIKE '%Rio%'改成= 'Rio'即可。
• 性能提示：这种解密后匹配的方式会触发全表扫描，无法利用字段索引。数据量较大时可考虑：
  • 对加密前的关键信息单独存储哈希值（仅支持精确匹配）
  • 使用确定性加密方案（支持匹配但安全性低于随机IV方案）

内容的提问来源于stack exchange，提问作者André Rosa