解决 MySQL 加密函数解密时为16进制数据
看代码:
mysql> select aes_decrypt(aes_encrypt('Iloveyou','1'),'1'); +--------------------------------------------------------------------------------------------+ | aes_decrypt(aes_encrypt('Iloveyou','1'),'1') | +--------------------------------------------------------------------------------------------+ | 0x496C6F7665796F75 | +--------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
解密之后为16进制数据,原因为系统编码方式(gbk)跟数据库采用的编码(utf8)方式不一样,
既然解密后为16进制数,那么把这个数转换成我想要的就可以了,mysql提供了 convert函数,用法如下
convert(进制数 using 编码格式);
所以可以用如下方式解密得到我们想要的—
+------------------------------------------------------------------+
| convert(aes_decrypt(aes_encrypt('Iloveyou','1'),'1') using utf8) |
+------------------------------------------------------------------+
| Iloveyou |
+------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select convert(aes_decrypt(aes_encrypt('woaini','1'),'1') using utf8);
+----------------------------------------------------------------+
| convert(aes_decrypt(aes_encrypt('woaini','1'),'1') using utf8) |
+----------------------------------------------------------------+
| woaini |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
#尝试加密汉字,解密后出现null;
mysql> select convert(aes_decrypt(aes_encrypt('我爱你','1'),'1') using utf8);
+----------------------------------------------------------------+
| convert(aes_decrypt(aes_encrypt('我爱你','1'),'1') using utf8) |
+----------------------------------------------------------------+
| NULL |
+----------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
但是当加密汉字的时候解密后会出现null,所以我该考虑一下更改电脑编码格式了吧!