各位观众老爷，大家好！我是今天的主讲人，江湖人称“MySQL小霸王”（其实是自己封的）。今天咱们要聊点刺激的：如何用MySQL的JSON函数，给咱们的数据穿上“隐身衣”和“防弹衣”，也就是动态脱敏和加密。
一、开场白：为啥要搞数据脱敏和加密？
想象一下，你辛辛苦苦攒了点私房钱，结果被人扒了个精光，是不是心态崩了？ 数据也一样！ 客户的身份证号、银行卡号、家庭住址，那都是宝贝疙瘩，一旦泄露，轻则被诈骗，重则公司吃官司。
所以，数据脱敏和加密，就是为了保护这些敏感数据，防止它们被不法分子盯上。
- 数据脱敏： 就是把敏感数据“变脸”，让它们看起来像真的，但实际上是假的。比如，把手机号中间几位变成
*，把身份证号只显示头尾几位。 - 数据加密： 就是把数据变成乱码，只有拥有密钥的人才能解密。 就像你写日记，用特殊的密码，只有你自己能看懂。
二、MySQL的JSON函数：脱敏加密的利器
MySQL从5.7版本开始，加入了JSON函数，这玩意儿可厉害了，能让我们像操作JSON对象一样操作数据。 它不仅可以存储和查询JSON数据，还能修改JSON数据！ 这就给咱们的脱敏和加密提供了极大的便利。
三、JSON函数脱敏实战
咱们先来玩点简单的，用JSON函数给数据脱敏。
1. 手机号脱敏
假设咱们有一张users表，里面有个phone_number字段，存储了用户的手机号。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
phone_number VARCHAR(20)
);
INSERT INTO users (name, phone_number) VALUES
('张三', '13812345678'),
('李四', '13987654321'),
('王五', '13700001111');现在，我们要把手机号中间四位变成*。 可以这样写：
SELECT
id,
name,
JSON_REPLACE(
JSON_OBJECT('phone_number', phone_number),
'$.phone_number',
CONCAT(
SUBSTRING(phone_number, 1, 3),
'****',
SUBSTRING(phone_number, 8)
)
) AS masked_phone
FROM
users;这段代码做了什么？
JSON_OBJECT('phone_number', phone_number): 把phone_number字段的值变成一个JSON对象，键为phone_number。JSON_REPLACE(...): 替换JSON对象中的值。CONCAT(...): 拼接字符串，把手机号的前三位、****和后四位拼接起来。SUBSTRING(...): 截取字符串。
执行结果：
| id | name | masked_phone |
|---|---|---|
| 1 | 张三 | {"phone_number": "138****5678"} |
| 2 | 李四 | {"phone_number": "139****4321"} |
| 3 | 王五 | {"phone_number": "137****0111"} |
看，手机号中间四位都变成*了！ 是不是很简单？
2. 身份证号脱敏
身份证号更敏感，咱们可以只显示头尾几位，中间的都变成*。
ALTER TABLE users ADD COLUMN id_card VARCHAR(20);
UPDATE users SET id_card =
CASE id
WHEN 1 THEN '370202199001011234'
WHEN 2 THEN '410105198512125678'
WHEN 3 THEN '510104200007079012'
END;
SELECT
id,
name,
JSON_REPLACE(
JSON_OBJECT('id_card', id_card),
'$.id_card',
CONCAT(
SUBSTRING(id_card, 1, 6),
'********',
SUBSTRING(id_card, 15)
)
) AS masked_id_card
FROM
users;这段代码跟手机号脱敏类似，只是截取的位数不一样。
执行结果：
| id | name | masked_id_card |
|---|---|---|
| 1 | 张三 | {"id_card": "370202********1234"} |
| 2 | 李四 | {"id_card": "410105********5678"} |
| 3 | 王五 | {"id_card": "510104********9012"} |
3. 动态脱敏：根据用户角色脱敏
更高级一点，咱们可以根据用户的角色，动态地脱敏。 比如，管理员可以看到完整的手机号，普通用户只能看到脱敏后的手机号。
这需要用到存储过程或者视图。 咱们先创建一个存储过程：
DROP PROCEDURE IF EXISTS get_user_data;
DELIMITER //
CREATE PROCEDURE get_user_data(IN user_role VARCHAR(20))
BEGIN
SELECT
id,
name,
CASE
WHEN user_role = 'admin' THEN phone_number
ELSE JSON_EXTRACT(
JSON_REPLACE(
JSON_OBJECT('phone_number', phone_number),
'$.phone_number',
CONCAT(
SUBSTRING(phone_number, 1, 3),
'****',
SUBSTRING(phone_number, 8)
)
),
'$.phone_number'
)
END AS phone_number
FROM
users;
END //
DELIMITER ;这段代码做了什么？
CASE WHEN user_role = 'admin' THEN phone_number ELSE ... END: 判断用户角色，如果是admin，就返回完整的手机号，否则返回脱敏后的手机号。JSON_EXTRACT(..., '$.phone_number'): 从JSON对象中提取phone_number的值。
调用存储过程：
CALL get_user_data('admin'); -- 管理员
CALL get_user_data('user'); -- 普通用户执行结果：
CALL get_user_data('admin'): 返回完整的手机号。CALL get_user_data('user'): 返回脱敏后的手机号。
四、JSON函数加密实战
光脱敏还不够，有些数据需要加密存储，防止被破解。
1. AES加密/解密
MySQL提供了AES_ENCRYPT()和AES_DECRYPT()函数，用于AES加密和解密。 AES是一种对称加密算法，也就是说，加密和解密使用同一个密钥。
ALTER TABLE users ADD COLUMN encrypted_name VARBINARY(255);
UPDATE users SET encrypted_name = AES_ENCRYPT(name, 'my_secret_key');
SELECT
id,
name,
AES_DECRYPT(encrypted_name, 'my_secret_key') AS decrypted_name
FROM
users;这段代码做了什么？
AES_ENCRYPT(name, 'my_secret_key'): 使用my_secret_key作为密钥，对name字段进行AES加密。AES_DECRYPT(encrypted_name, 'my_secret_key'): 使用my_secret_key作为密钥，对encrypted_name字段进行AES解密。
注意：
- 密钥一定要保管好，一旦丢失，数据就无法解密了！
encrypted_name字段的类型必须是VARBINARY，用于存储二进制数据。
2. 结合JSON函数加密特定字段
如果只想加密JSON字段中的某个属性，可以这样：
ALTER TABLE users ADD COLUMN profile JSON;
UPDATE users SET profile =
CASE id
WHEN 1 THEN '{"age": 30, "address": "北京市海淀区"}'
WHEN 2 THEN '{"age": 25, "address": "上海市浦东新区"}'
WHEN 3 THEN '{"age": 40, "address": "深圳市南山区"}'
END;
UPDATE users
SET profile = JSON_SET(
profile,
'$.address',
AES_ENCRYPT(JSON_EXTRACT(profile, '$.address'), 'my_secret_key')
);
SELECT
id,
name,
profile,
JSON_EXTRACT(profile, '$.age') AS age,
AES_DECRYPT(JSON_EXTRACT(profile, '$.address'), 'my_secret_key') AS decrypted_address
FROM
users;这段代码做了什么？
JSON_SET(profile, '$.address', AES_ENCRYPT(...)): 把profile字段中的address属性的值，用AES加密。JSON_EXTRACT(profile, '$.address'): 从profile字段中提取address属性的值。
五、安全注意事项
- 密钥管理： 密钥一定要安全存储，可以使用专门的密钥管理系统。
- 权限控制： 只有授权的用户才能访问敏感数据。
- 日志审计： 记录所有的数据访问操作，方便追踪问题。
- 定期轮换密钥： 定期更换密钥，防止密钥泄露。
- 不要在客户端解密： 尽量在服务端解密数据，防止客户端泄露密钥。
- HTTPS： 使用HTTPS协议，保证数据传输过程中的安全。
六、总结
今天咱们学习了如何使用MySQL的JSON函数进行数据脱敏和加密。 JSON函数提供了灵活的操作方式，可以对JSON数据进行精确的脱敏和加密。 但是，安全无小事，一定要注意密钥管理和权限控制，才能真正保护好咱们的数据。
七、作业
- 研究一下MySQL的其他加密函数，比如
MD5()、SHA1()等。 它们有什么优缺点？ 适用于哪些场景？ - 尝试使用存储过程或视图，实现更复杂的动态脱敏逻辑。 比如，根据用户的角色和数据类型，选择不同的脱敏策略。
- 了解一下密钥管理系统的基本原理，并尝试搭建一个简单的密钥管理系统。
八、互动环节
大家有什么问题？ 或者有什么更好的脱敏和加密方案？ 欢迎一起交流学习！
希望今天的讲座对大家有所帮助。 咱们下次再见！