向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

实践如下：
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */; Query OK, 1 row affected (0.01 sec) use test; Database changed create table test( id int (12),idcard varchar (128)); Query OK, 0 rows affected (0.03 sec) INSERT INTO test (idcard) VALUES (HEX(AES_ENCRYPT('452528199909091234','my_secret_key'))) ; Query OK, 1 row affected (0.00 sec) SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test; +------+--------------------+ | id   | idcard             | +------+--------------------+ | NULL | 452528199909091234 | +------+--------------------+ 1 row in set (0.00 sec) select * from test\G; *************************** 1. row ***************************     id: NULL idcard: AC89B7A39D9A5A43BF9B0B455E2097B37E7BB2A8379B6D820FB3FC97EEB197B2 1 row in set (0.00 sec) SELECT id,LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6) AS idcard FROM test; +------+--------+ | id   | idcard | +------+--------+ | NULL | 452528 | +------+--------+ 1 row in set (0.00 sec) SELECT id,CONCAT('******',LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6)) AS idcard FROM test; +------+--------------+ | id   | idcard       | +------+--------------+ | NULL | ******452528 | +------+--------------+ 1 row in set (0.01 sec) SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE idcard=HEX(AES_ENCRYPT('452528199909091234','my_secret_key')); +------+--------------------+ | id   | idcard             | +------+--------------------+ | NULL | 452528199909091234 | +------+--------------------+ 1 row in set (0.00 sec) SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE AES_DECRYPT(UNHEX(idcard),'my_secret_key')='452528199909091234'; +------+--------------------+ | id   | idcard             | +------+--------------------+ | NULL | 452528199909091234 | +------+--------------------+ 1 row in set (0.00 sec)

项目中涉及到用户敏感信息，比如手机号、身份证号码等，应该以密文形式存储在数据库中，本文例子演示如何利用mysql的加解密函数对数据进行加密存储、解密读取、以及过滤查询。

  下面以在表"test"中将身份证信息写入"idcard"字段、以及从"idcard"字段读取数据、以及以"idcard"为过滤条件进行过滤查询为例子；假设加密秘钥为"my_secret_key"

一）加密存储数据：'452528199909091234'，秘钥为:'my_secret_key'
INSERT INTO test (idcard) VALUES (HEX(AES_ENCRYPT('452528199909091234','my_secret_key')))

二）读取并解密数据返回

1.读取并解密数据，数据全部返回。
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test

2.读取并解密数据，只返回最左边6个字符
SELECT id,LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6) AS idcard FROM test

3.读取并解密数据，只返回最左边6个字符，补充前缀'******'，读出来的结果为
SELECT id,CONCAT('******',LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6)) AS idcard FROM test

三）过滤查询

1.按照密文比较方式(要比较的数据先加密，加密后的数据和数据库里存的密文比较)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE idcard=HEX(AES_ENCRYPT('452528199909091234','my_secret_key'))

2.按照明文比较方式(将数据库存的密文解密出明文，然后和要比较的数据进行比较)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE AES_DECRYPT(UNHEX(idcard),'my_secret_key')='452528199909091234'
来自：https://blog.csdn.net/u010178611/article/details/126508465

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：http://jackxiang.com/post/11713/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！