在实际生产环境中，为了保障用户隐私数据的安全性，通常需要对一些敏感信息进行加密存储处理。例如用户的手机号码、家庭住址、身份证号码、银行卡号等关键信息，这些数据一旦泄露，可能会对用户个人甚至企业造成严重的影响。因此，在数据库中直接以明文形式存储这类数据是不符合安全规范的做法。
然而，在业务实现过程中，往往又存在根据这些敏感字段进行快速检索的需求。例如，系统可能需要根据用户的手机号查找其账户信息，或者根据地址模糊匹配特定区域的用户群体。这就带来了一个挑战：如何在保证数据加密存储的前提下，仍能支持对加密字段的高效查询，尤其是模糊查询功能？
1、方法一：SQL层解密处理（适合数据量较少的场景，因为不走索引）
思路：
- 数据加密存储到数据库。
- 在查询SQL中，通过解密方法先将密文还原成明文，在通过还原后的明文进行模糊匹配。
注意事项：
- 这种方式需要管理密钥（可以明文传参，也可以通过keyring插件管理）
- 解密函数包裹会造成索引失效，如果数据量很大的场景下，不推荐使用。
实现方式：
- MySQL自带了AES_ENCRYPT(str, key)和AES_DECRYPT(encrypted_str, key)这两个函数，可以直接使用明文密钥进行加密和解密操作。
- 在一般场景下，可以直接在SQL语句中指定明文密钥。如果需要在更复杂，安全需求高的场景下，建议使用Keyring插件进行密钥管理。
（1）、 直接使用明文密钥示例
1、创建包含加密字段的表
首先，创建一个名为user_info的表，其中包含一个使用AES加密的address_encrypted字段。
SQL示例：
CREATE TABLE user_info (
id INT AUTO_INCREMENT PRIMARY KEY,
address_encrypted VARBINARY(512) -- 使用VARBINARY类型存储加密后的数据
);
操作示例：
2、插入加密的数据
接下来，向表中插入一些加密的数据。
SQL示例：
-- 设定一个简单的密钥
SET @key = '1234567890';
-- 插入加密的数据
INSERT INTO user_info (address_encrypted)
VALUES (AES_ENCRYPT(CONVERT('山东省青岛市市南区香港中路123号' USING utf8mb4), @key));
INSERT INTO user_info (address_encrypted)
VALUES (AES_ENCRYPT(CONVERT('湖北省长沙市北区梨园中路789号' USING utf8mb4), @key));
操作示例：
此时，查看表中的数据，可以看到是加密存储的。
3、执行模糊查询
现在我们可以基于解密后的值进行模糊查询。为了确保查询效率，可以考虑将解密结果转换为字符串后再进行比较。
SQL示例：
-- 设定相同的密钥用于解密
SELECT
CONVERT(AES_DECRYPT(address_encrypted, @key) USING utf8mb4) AS address
FROM
user_info
WHERE
CONVERT(AES_DECRYPT(address_encrypted, @key) USING utf8mb4) LIKE '%山%';
操作示例：
如上，可以看到，虽然数据库是密文存储，但是支持模糊查询。
（2）、使用keyring插件来管理密钥
以上的明文密钥在实际生产中还是存在诸多安全隐患的。如果不小心泄露了密钥，那么存储在数据库中的加密数据实际上已经等于明文了。可以使用keyring插件来管理加密密钥，这样密钥配置在服务器端，SQL上也不会明文展示，可以大大提升安全问题。
要求：
使用MySQL 8.0或更高版本，并且启用了对称加密功能。
1、启用MySQL Keyring Plugin
首先，需要确认MySQL服务器已经配置了keyring插件。这通常涉及到修改MySQL配置文件(my.cnf 或 my.ini)，并添加如下内容：
ini示例：
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
注意：
重启MySQL服务使配置生效。
2、创建加密密钥
登录到MySQL命令行工具，创建一个新的密钥：
sql示例：
SET @encryption_key_id = UUID();
SET @keyring_plugin_status = 'ACTIVE';
SELECT sys.create_innodb_key(@encryption_key_id);
3、创建包含加密字段的表和新增加密数据
之后建表，新增数据和模糊查询的示例同上明文操作一致。用到密钥的地方直接使用@encryption_key_id即可。
建表如：
sql示例：
CREATE TABLE user_info (
id INT AUTO_INCREMENT PRIMARY KEY,
address_encrypted VARBINARY(512) -- 使用VARBINARY类型存储加密后的数据
);
新增加密数据：
sql示例：
INSERT INTO user_info (address_encrypted)
VALUES (AES_ENCRYPT('山东省青岛市市南区香港中路123号', @encryption_key_id));
4、执行模糊查询
现在我们可以基于解密后的值进行模糊查询。
SQL示例：
-- 设定相同的密钥用于解密
SELECT
CONVERT(AES_DECRYPT(address_encrypted, @encryption_key_id) USING utf8mb4) AS address
FROM
user_info
WHERE
CONVERT(AES_DECRYPT(address_encrypted, @encryption_key_id) USING utf8mb4) LIKE '%山%';
(3)、总结和注意
注意事项：
- 安全性：虽然这种方法简单易用，但在生产环境中直接在SQL脚本中硬编码密钥可能会带来安全风险。建议采用更加安全的方式来管理和保护你的密钥。
- 性能：对大量数据进行解密可能会影响查询性能。如果可能的话，尽量减少需要解密的数据量，或者考虑使用索引优化查询。
- 字符集问题：由于AES加密/解密的结果是二进制数据，因此在处理时需要注意字符集问题。确保在解密后正确地转换为所需的字符集格式。
这个方法适用于中小规模的应用场景。对于大规模应用或对安全性有更高要求的情况，建议探索数据库提供的其他安全功能或第三方安全解决方案。
2、方法二：分片加密（可以走索引，适用于大数据量的场景）
思路：
- 数据表中出了原始数据字段，还需要新增加一个分片加密字段。
- 原始字段直接加密存储，用于数据查询后的展示。
- 分片加密字段存储原始字符串分片成多个小字符串的加密结果，也是密文展示，主要用户模糊查询，但通常需要更大的存储空间。
- 在查询SQL中，将查询关键字进行加密，通过分片加密字段模糊查询加密后的关键字，可以实现模糊匹配。
（1）、创建包含分片字段的数据表
SQL示例：
CREATE TABLE user_info (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
address varbinary(255) NOT NULL,
address_index text COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
（2）、插入加密数据
假设要插入的实际地址为：山东省青岛市市南区香港中路123号
1、先将要加密的数据进行分片
这里我们使用Java的代码实现分片加密。
示例代码：
import com.huaban.analysis.jieba.JiebaSegmenter;
import com.huaban.analysis.jieba.SegToken;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import java.util.Base64;
import java.util.List;
public class AddressEncryptionUtils {
private static final String AES_ALGORITHM = "AES/ECB/PKCS5Padding";
private static final String SECRET_KEY = "1234567890123456"; // 替换为实际的密钥
// 加密方法
public static String encrypt(String plaintext) throws Exception {
SecretKeySpec keySpec = new SecretKeySpec(SECRET_KEY.getBytes(), "AES");
Cipher cipher = Cipher.getInstance(AES_ALGORITHM);
cipher.init(Cipher.ENCRYPT_MODE, keySpec);
byte[] encryptedBytes = cipher.doFinal(plaintext.getBytes());
return Base64.getEncoder().encodeToString(encryptedBytes);
}
// 分词并生成加密索引
public static String generateAddressIndex(String address) throws Exception {
JiebaSegmenter segmenter = new JiebaSegmenter();
StringBuilder indexBuilder = new StringBuilder();
List<SegToken> tokens = segmenter.process(address, JiebaSegmenter.SegMode.INDEX);
System.out.print("字符串分片为:");
for (SegToken token : tokens) {
System.out.print(token.word + " ");
String encryptedWord = encrypt(token.word);
indexBuilder.append(encryptedWord).append("|");
}
System.out.println();
return indexBuilder.toString();
}
// 示例用法
public static void main(String[] args) throws Exception {
String address = "山东省青岛市市南区香港中路123号";
System.out.println("原始地址：" + address);
String encryptedAddress = encrypt(address); // 加密家庭住址
System.out.println("加密后的地址：" + encryptedAddress);
String addressIndex = generateAddressIndex(address); // 生成分片加密索引
System.out.println("加密后的分片地址：" + addressIndex);
}
}
操作结果：
2、通过SQL新增数据
这里我只是写的demo实现功能。在实际项目中，应该使用ORM等工具实现数据保存到数据库。
SQL示例：
INSERT INTO user_info (name, address, address_index)
VALUES (
'张三',
FROM_BASE64('6r3JHNaPayfBsftPSUQzillOed6Uk5qHWp1v7gwiw6XWtE5f1YGSf3f9V0c1mG0h'),
'Aikz5rk5cBA/cDRO5hNUPg==|D5Tsz//QkwQbfFSVn8U/fQ==|GJmpls+COThAsTvbR4dvwQ==|BVofwaHbZYbtRU3xRl0KBw==|vV42gf2Z8as63gj7mVywaQ==|XHz5/XRtdNjPkaKAauQWKQ==|e7ywzpIwhPgE+jz0uEgyrA==|eLFZmqZs9YnDZ/zzWM19gQ==|z9aS2TnKlmkEcJw9JOMC0g==|DGYYLscQhABl66pHxebOkA==|lNiUOKip28WaWGip/GOfCg==|'
);
（3）、执行模糊查询
假设查询的关键词为：山东省
1、关键词加密，获取密文
可以通过上诉的Java代码示例获取查询关键字的加密字符串。
操作结果：
2、通过分片加密字段进行模糊查询
通过分片加密字段来检索关键字进行模糊查询。注意返回需要的是完整数据。
SQL示例：
SELECT
name,
CAST(AES_DECRYPT(address, '1234567890123456') AS CHAR) AS address
FROM
user_info
WHERE
address_index LIKE '%D5Tsz//QkwQbfFSVn8U/fQ==%';
操作结果：
（4）、 注意事项
- 密钥管理：确保密钥的安全性，防止泄露。
- 性能考虑：分片加密会增加存储空间和查询时间，需评估系统性能。
- 字符集匹配：确保加密算法和数据库字符集兼容。
- 查询精度：分片长度影响查询精度和存储成本，需根据实际情况调整。
向阳而生，Dare To Be！！！