mysql 加密脱敏字段模糊查询笔记

原创已于 2022-12-20 14:48:58 修改 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2022-12-20 14:33:35 首次发布

mysql 专栏收录该内容

1 篇文章

订阅专栏

mysql 加密脱敏字段模糊查询

入库的数据是要进行加密脱敏的数据；但是这个字段还要支持界面模糊匹配；这里做个笔记吧
仅供参考

insert into 的sql 大概长这样

insert into tableName(id, name, idno, create_time, user_id, user_name)
		values(#{id}, #{name} HEX(AES_ENCRYPT(#{idno}, #{aesKey})), #{createTime}, #{userId}, #{userName})

然后select 的sql

select id, CAST(AES_DECRYPT(UNHEX(idno), #{aesKey}) AS CHAR) idno, name, create_time createTime, user_id userId, user_name userName from tableName
		where CAST(AES_DECRYPT(UNHEX(idno), #{aesKey}) AS CHAR) LIKE CONCAT('%',#{idno},'%')

参考博客

https://blog.youkuaiyun.com/weixin_43816759/article/details/120926773
https://blog.youkuaiyun.com/qq_39940205/article/details/113733233
https://blog.youkuaiyun.com/weixin_44890030/article/details/103575984
https://blog.youkuaiyun.com/qq_43632766/article/details/99295742
https://blog.youkuaiyun.com/u013614857/article/details/82858743