[草稿]mysql字段加密方案

关于 MySQL 数据加密的思路，其实有两种：

在数据库外部加密后存入数据库
在数据库内部对数据进行加密

这两种的区别就是第二种比第一种在使用上要更加方便，因为在外部加密的话每一次的查找如果是按照被加密的项来的话，都需要先算出加密后的数据再放入sql中，同时返回的也是加密的数据，需要在外部进行解密；而第二种的话可以直接在sql中传原值和加密的key即可，解密也可以在sql中完成。这使得部分组合sql查询使用第一种办法就无法完成了，比如查询的字段是另一场查询的结果这种，因为必须经过一个外部加密的过程。

[TOC]

双向加密

ENCODE / DECODE

传入两个值，一个是要加密的记录，一个是加密和解密的key.加密之后的二进制字符长度和原始长度是一样的，以blob类型存储

BLOB 类型的字段用于存储二进制数据 MySQL 中，BLOB 是个类型系列，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大大小上不同。 MySQL 的四种 BLOB 类型类型大小 (单位：字节) TinyBlob 最大 255 Blob 最大 65K Medium。

加密：SELECT ENCODE('mytext','mykeystring');

mysql> SELECT ENCODE('mytext','mykeystring');
+--------------------------------+
| ENCODE('mytext','mykeystring') |
+--------------------------------+
| ">¿¡È                         | 
+--------------------------------+

解密：SELECT DECODE(ENCODE('mytext','mykeystring'),'mykeystring');

mysql> SELECT DECODE(ENCODE('mytext','mykeystring'),'mykeystring');
+------------------------------------------------------+
| DECODE(ENCODE('mytext','mykeystring'),'mykeystring') |
+------------------------------------------------------+
| mytext                                               | 
+------------------------------------------------------+

AES_ENCRYPT / AES_DECRYPT

这种加密算法使用AES(高级加密标准，Advanced Encryption Standard)，使用key_str加密，key_str的长度可以达到256位，加密的结果是一个二进制的字符串，以blob类型存储

加密：SELECT AES_ENCRYPT('mytext', 'mykeystring');

mysql> USE bak;
mysql> DROP TABLE IF EXISTS jiami;
mysql> CREATE TABLE jiami (username varchar(10), pasword varchar(50), encryptedpassword varbinary(255));
mysql> INSERT INTO jiami (username,pasword,encryptedpassword) VALUES ('sam','password123',AES_ENCRYPT('password123','key'));

mysql> SELECT * FROM jiami;
+----------+-------------+-------------------+
| username | pasword     | encryptedpassword |
+----------+-------------+-------------------+
| sam      | password123 | •›¨í ƒðbáÒ9•j    |
+----------+-------------+-------------------+

解密：SELECT AES_DECRYPT(AES_ENCRYPT('mytext','mykeystring'), 'mykeystring');

mysql> SELECT username,pasword,AES_DECRYPT(encryptedpassword,'key') FROM jiami;
+----------+-------------+--------------------------------------+
| username | pasword     | AES_DECRYPT(encryptedpassword,'key') |
+----------+-------------+--------------------------------------+
| sam      | password123 | password123                          |
+----------+-------------+--------------------------------------+

注：加密的字段用 workbench 查看则会显示为 BLOB，而在 mysql 命令行下则正常显示。

DES_ENCRYPT / DES_DECRYPT

这种加密方法使用了3DES（三重加密数据算法，听着就知道加密等级比较gap），加密时可以选择使用key_num还是key_str

加密：SELECT DES_ENCRYPT('mytext',5),DES_ENCRYPT('mytext','mypassward');

mysql> SELECT DES_ENCRYPT('mytext',5),DES_ENCRYPT('mytext','mypassward');
+-------------------------+------------------------------------+
| DES_ENCRYPT('mytext',5) | DES_ENCRYPT('mytext','mypassward') |
+-------------------------+------------------------------------+
| …ÿc}æ¤~               | ÿ]ï×ñ”Å                          | 
+-------------------------+------------------------------------+

解密：使用DES_DECRYPT

但是w3resource中有一句:This function works only with Secure Sockets Layer (SSL) if support for SSL is available in MySql configuration.我个人的理解是如果使用这种加密方法，就必须使用SSL安全连接的方式连接数据库，否则就浪费了较高的加密等级了

单向加密

其实关于单向加密是不是加密的争论网上一直有，比如MD5到底是不是加密？我这里就把它当成加密算法了，不抬杠

MD5加密

MD5加密的结果是32位十六进制数的二进制字符串

加密：SELECT MD5('w3resource');

mysql> SELECT MD5('w3resource'); 
+----------------------------------+
| MD5('w3resource')                |
+----------------------------------+
| b273cb2263eb88f61f7133cd308b4064 | 
+----------------------------------+

ENCRYPT加密

ENCRYPT使用Unix的crypt()系统调用实现，返回一个二进制字符串。因为它是基于Unix系统调用的，所以在Windows中会返回NULL

加密：SELECT ENCRYPT('w3resource', 'encode');

mysql> SELECT ENCRYPT('w3resource', 'encode');
+---------------------------------+
| ENCRYPT('w3resource', 'encode') |
+---------------------------------+
| NULL                            | 
+---------------------------------+

SHA1加密

SHA1返回的是40位的十六进制数字的二进制字符串，输入是NULL的时候输出也是NULL

加密：SELECT SHA1('w3resource');

mysql> SELECT SHA1('w3resource');
+------------------------------------------+
| SHA1('w3resource')                       |
+------------------------------------------+
| d228359c41174cede6b3c401eb8d11746a4ad1eb | 
+------------------------------------------+

PASSWORD

这个一般是用来加密密码的，输入为NULL时输出也是NULL

加密：SELECT PASSWORD('w3resource');

mysql> SELECT PASSWORD('w3resource');
+-------------------------------------------+
| PASSWORD('w3resource')                    |
+-------------------------------------------+
| *EE0804DDC2CC3E85A47191ECCCBA29B775DFFA77 | 
+-------------------------------------------+

最佳实践

通常情况下，敏感数据包括但不限于：真实姓名、手机号码、邮箱地址、家庭住址、身份证号、银行卡号、交易记录、账号密码等。

1、密码，建议用md5加密后保存，验证密码的时候先对输入的字符串做md5，再对数据库种的加密密码做比对。
2、手机号地址建议用base64
3、代码层：密码加一些随机的字符串，做hash，得到一个不可解密的字符串保存到mysql；解密的时候再hash反向解密n次，
4、AES、DES，计算放在数据库层，不建议；
5、对称加密算法库，无法检索，但是可以拆分多个字段，比如籍贯+详细地址；
6、业务侧，由开发同事来决定；

参考：https://www.zhihu.com/question/36767829
参考：https://www.cnblogs.com/jiading/p/12608166.html

mysql字段加密方案