Mysql加密解密

Mysql加密解密

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_aes-encrypt

函数定义

AES_ENCRYPT(str,key)

返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，使用 VARBINARY or BLOB 存储

AES_DECRYPT(str,key)

返回用密钥key对字符串str利用高级加密标准算法解密后的结果

常用法

mysql> SELECT HEX(AES_ENCRYPT('test','key')) ;
+----------------------------------+
| HEX(AES_ENCRYPT('test','key'))   |
+----------------------------------+
| 9E9CE44CD9DF2B201F51947E03BCCBE2 |
+----------------------------------+

mysql> SELECT AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'),'key');
+--------------------------------------------------------------+
| AES_DECRYPT(UNHEX('9E9CE44CD9DF2B201F51947E03BCCBE2'),'key') |
+--------------------------------------------------------------+
| test                                                         |
+--------------------------------------------------------------+

mysql> SELECT to_base64(AES_ENCRYPT('test','key')) ;
+--------------------------------------+
| to_base64(AES_ENCRYPT('test','key')) |
+--------------------------------------+
| npzkTNnfKyAfUZR+A7zL4g==             |
+--------------------------------------+

mysql> SELECT AES_DECRYPT(from_base64('npzkTNnfKyAfUZR+A7zL4g=='),'key');
+------------------------------------------------------------+
| AES_DECRYPT(from_base64('npzkTNnfKyAfUZR+A7zL4g=='),'key') |
+------------------------------------------------------------+
| test                                                       |
+------------------------------------------------------------+

使用案例

注意字段宽度

create table aes_info(id int primary key, no varchar(8), pw varchar(255));

insert into aes_info values (1,'00050001',HEX(AES_ENCRYPT('test','key')));

select * from aes_info;
+----+----------+----------------------------------+
| id | no       | pw                               |
+----+----------+----------------------------------+
|  1 | 00050001 | 9E9CE44CD9DF2B201F51947E03BCCBE2 |
+----+----------+----------------------------------+

select id,no,AES_DECRYPT(UNHEX(pw),'key') from aes_info;
+----+----------+------------------------------+
| id | no       | AES_DECRYPT(UNHEX(pw),'key') |
+----+----------+------------------------------+
|  1 | 00050001 | test                         |
+----+----------+------------------------------+