MySQL加密解密函数AES_ENCRYPT AES_DECRYPT

官方文档：

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

 

加密函数

AES_ENCRYPT(str,key)

例：select AES_ENCRYPT('字符串','mima');

mysql> select AES_ENCRYPT('字符串','mima');
+---------------------------------+
| AES_ENCRYPT('字符串','mima')    |
+---------------------------------+
| 6                     |
+---------------------------------+
1 row in set (0.00 sec)

在存入数据库的时候，转成十六进制

create table t2(c1 varchar(64);
insert into t2 select hex(aes_encrypt(('字符串'),'mima'));

mysql> select * from t2;
+----------------------------------+
| c1 |
+----------------------------------+
| 36CD256BB4BD99CB184D089408954681 |
+----------------------------------+
1 row in set (0.00 sec)

 

 

解密函数 AES_DECRYPT(str,key)

解密之前先用huhex函数转一次

mysql> select aes_decrypt(unhex(c1),'mima') from t2;
+-------------------------------+
| aes_decrypt(unhex(c1),'mima') |
+-------------------------------+
| 字符串                        |
+-------------------------------+
1 row in set (0.00 sec)

 

 

加密后字符有多长，和加密内容有关

示例1、

加密内容长度是15个字符， 16 * (trunc(15 / 16) + 1) = 16*(0+1) = 16 个字节，一个字符是8位，所以需要32个字符显示。

mysql> select AES_ENCRYPT('123456789012345','mima');

+------------------------------------------------------------------------------+

| AES_ENCRYPT('123456789012345','mima') |

+------------------------------------------------------------------------------+

| 0xAC94004F5D2ABA62C7EB8C3F46C18DFA |

+------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

示例2、

加密内容长度是16个字符， 16 * (trunc(16 / 16) + 1) = 16*(1+1) = 32 个字节，一个字符是8位，所以需要64个字符显示。

mysql> select AES_ENCRYPT('1234567890123456','mima');

+--------------------------------------------------------------------------------+

| AES_ENCRYPT('1234567890123456','mima') |

+--------------------------------------------------------------------------------+

| 0x363BBB99593EDC9B7E73CC61ED7CD6320632E308519E8A239BA4885CF11BFD2B |

+--------------------------------------------------------------------------------+

1 row in set (0.00 sec)