netnr/ MySQL-Encryption.sql 2022-07-05 11:51
MySQL 数据加密、解密
Edit Delete Raw Download
-- 旧版本加密、解密(已弃用)
INSERT INTO users (username, password) VALUES ('john', ENCODE('guessme', 'salt')); -- 写入加密
SELECT username, DECODE(password,'salt') AS password FROM users WHERE username = 'john'; -- 查询解密

-- 新版本加密、解密(安全级别、性能高)
INSERT INTO users (username, password) VALUES ('steven', aes_encrypt('password', 'salt')); -- 写入加密
SELECT username, aes_decrypt(password,'salt') AS password FROM users WHERE username = 'steven';  -- 查询解密

SET block_encryption_mode = 'aes-256-cbc'; -- 修改加密模式
-- 或添加到配置文件 /etc/my.cnf 重启服务
block_encryption_mode=aes-256-cbc
-- 生成向量(随机、固定)
SELECT RANDOM_BYTES(16),hex(RANDOM_BYTES(16)),unhex('F1C50531999CD95D7CF56E281A4C23F4');
AES_ENCRYPT('text' , 'password', UNHEX('F1C50531999CD95D7CF56E281A4C23F4')) -- 256 cbc 加密、固定向量

-- help
https://www.cnblogs.com/mc-r/p/15890408.html
-- aes 速度很快,旧硬件可以处理 100MB/s
https://stackoverflow.com/questions/15564591
-- 官方文档
https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt