MySQL 加密解密 详解

MySQL 数据库内部有多种 加密函数 解密函数

被加密的字段的类型需要是 VARBINARY、BLOB 类型 存储

AES_ENCRYPT(str,key) : 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串

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

mysql> CREATE TABLE userdata(username VARCHAR(20),pwssword VARCHAR(50),encryptedpassword VARBINARY(100));

mysql> INSERT INTO  userdata( username,pwssword,encryptedpassword ) VALUES('smith','abdABC123', AES_ENCRYPT(' abdABC123 ','key') );

mysql> SELECT * FROM userdata;

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

| username | pwssword  | encryptedpassword |

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

| smith    | abdABC123 | .|

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

1 row in set (0.01 sec)

mysql> SELECT username,pwssword,AES_DECRYPT(encryptedpassword,'key') FROM userdata;

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

| username | pwssword  | AES_DECRYPT(encryptedpassword,'key') |

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

| smith    | abdABC123 | abdABC123                            |

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

1 row in set (0.00 sec)

MD5(str) : MD5方式对 str 加密

mysql> SELECT MD5('abcABC123');

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

| MD5('abcABC123')                 |

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

| 480aeb42d7b1e3937fe8db12a1ffe6d8 |

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

1 row in set (0.00 sec)

SHA(str) : SHA方式对 str 加密

mysql> SELECT SHA('abcABC123');

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

| SHA('abcABC123')                         |

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

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

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

1 row in set (0.00 sec)

SHA1(str) : SHA1方式对 str 加密

mysql> SELECT SHA1('abcABC123');

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

| SHA1('abcABC123')                        |

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

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

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

1 row in set (0.00 sec)

ENCODE(str,key) : 使用字符串 key 作为秘钥来加密字符串str，加密结果是二进制数

DECODE(str,key) : 使用字符串 key 作为秘钥来解密字符串str

mysql> INSERT INTO userdata(username,pwssword,encryptedpassword) VALUES('KT','abdABC123',ENCODE('abdABC123','key'));

mysql> SELECT * FROM userdata WHERE username=' KT ' ;

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

| username | pwssword  | encryptedpassword |

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

| KT       | abdABC123 | 1       |

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

1 row in set (0.00 sec)

mysql> SELECT username,pwssword, DECODE (encryptedpassword,'key') FROM userdata  WHERE username=' KT ' ;

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

| username | pwssword  | DECODE(encryptedpassword,'key') |

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

| KT       | abdABC123 | abdABC123                       |

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

1 row in set, 1 warning (0.00 sec)

ENCRYPT(str,salt) : 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

mysql> SELECT ENCRYPT('abdABC123','key');

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

| ENCRYPT('abdABC123','key') |

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

| kezazmcIo.aCw              |

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

1 row in set, 1 warning (0.00 sec)

PASSWORD(str) : 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法

mysql> INSERT INTO userdata(username,pwssword,encryptedpassword) VALUES('Tom','abdABC123', PASSWORD ('abdABC123'));

mysql> SELECT * FROM userdata WHERE username='Tom';

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

| username | pwssword  | encryptedpassword                         |

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

| Tom      | abdABC123 | *6970C4ACB558CFFCAD8DE8DA17CDD40CFC023FDA |

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

1 row in set (0.00 sec)

mysql> SELECT PASSWORD('abcde');

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

| PASSWORD('abcde')                         |

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

| *8DC54F2E15823C98AEA063E339A5D4C53D1A471A |

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

1 row in set, 1 warning (0.00 sec)