MySQL 數(shù)據(jù)庫(kù)內(nèi)部有多種 加密函數(shù) 解密函數(shù)

南平ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書(shū)銷(xiāo)售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話(huà)聯(lián)系或者加微信：028-86922220（備注：SSL證書(shū)合作）期待與您的合作！

被加密的字段的類(lèi)型需要是 VARBINARY、BLOB 類(lèi)型 存儲(chǔ)

AES_ENCRYPT(str,key) : 返回用密鑰key對(duì)字符串str利用高級(jí)加密標(biāo)準(zhǔn)算法加密后的結(jié)果，調(diào)用AES_ENCRYPT的結(jié)果是一個(gè)二進(jìn)制字符串

AES_DECRYPT(str,key) : 返回用密鑰key對(duì)字符串str利用高級(jí)加密標(biāo)準(zhǔn)算法解密后的結(jié)果

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方式對(duì) str 加密

mysql> SELECT MD5('abcABC123');

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

| MD5('abcABC123')? ? ? ? ? ? ? ? ?|

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

| 480aeb42d7b1e3937fe8db12a1ffe6d8 |

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

1 row in set (0.00 sec)

SHA(str) : SHA方式對(duì) str 加密

mysql> SELECT SHA('abcABC123');

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

| SHA('abcABC123')? ? ? ? ? ? ? ? ? ? ? ? ?|

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

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

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

1 row in set (0.00 sec)

SHA1(str) : SHA1方式對(duì) str 加密

mysql> SELECT SHA1('abcABC123');

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

| SHA1('abcABC123')? ? ? ? ? ? ? ? ? ? ? ? |

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

| 0a6807c0856b137fb44ce239587e4f34e011b005 |

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

1 row in set (0.00 sec)

ENCODE(str,key) : 使用字符串 key 作為秘鑰來(lái)加密字符串str，加密結(jié)果是二進(jìn)制數(shù)

DECODE(str,key) : 使用字符串 key 作為秘鑰來(lái)解密字符串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()函數(shù)，用關(guān)鍵詞salt(一個(gè)可以惟一確定口令的字符串，就像鑰匙一樣)加密字符串str

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

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

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

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

| kezazmcIo.aCw? ? ? ? ? ? ? |

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

1 row in set, 1 warning (0.00 sec)

PASSWORD(str) : 返回字符串str的加密版本，這個(gè)加密過(guò)程是不可逆轉(zhuǎn)的，和UNIX密碼加密過(guò)程使用不同的算法

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)

---------------------?