SQL改写基础：INSERT/UPDATE/SELECT中的加解密实战

好，咱们进入正题。我记得刚接手第一个加密改造项目时，甲方只给了一句话：“把这几张表的敏感字段都加密了，业务不能停。”说白了，列级加密的核心就两条：写入时加密，读取时解密。但怎么写得顺手、读得高效，这里头有不少门道。

4.1 加密函数的选择与准备

在实际项目中，我选加密函数有几个硬指标：性能不能太差、密钥管理要方便、数据库要原生支持。你想想看，如果每写一条记录都卡几秒，业务部门肯定不答应。

我个人习惯用AES-256，它平衡了安全性和性能。不同数据库的调用方式略有差异：

数据库 加密函数 解密函数
MySQL AES_ENCRYPT() AES_DECRYPT()
PostgreSQL pgp_sym_encrypt() pgp_sym_decrypt()
Oracle DBMS_CRYPTO.ENCRYPT DBMS_CRYPTO.DECRYPT
SQL Server ENCRYPTBYKEY() DECRYPTBYKEY()
我的小经验：密钥不要硬编码在SQL里。我在项目中吃过这个亏——后来换密钥时，改了几百个存储过程，改到想哭。建议用数据库的密钥管理服务，或者放在配置中心统一管理。

4.2 INSERT语句中的加密改写

这是最基础的场景。假设我们有一张用户表 t_user，其中 phone 字段需要加密存储。原来的INSERT长这样：

-- 原始SQL
INSERT INTO t_user (user_id, user_name, phone)
VALUES (1001, '张三', '13800138000');

-- 改写后SQL
INSERT INTO t_user (user_id, user_name, phone)
VALUES (1001, '张三', AES_ENCRYPT('13800138000', 'my_secret_key'));

嗯，这里要注意：加密后的数据是二进制类型。如果你原来字段定义是 VARCHAR(20)，直接就炸了。我遇到过一位同事，改完INSERT后一直报错，排查了半天才发现是字段类型没改。

正确的表结构应该是：

CREATE TABLE t_user (
    user_id   INT PRIMARY KEY,
    user_name VARCHAR(50),
    phone     VARBINARY(256)  -- 改成二进制类型，长度留余量
);
避坑指南：VARBINARY的长度设置建议是原明文字段的2-4倍。我曾经用VARBINARY(32)存一个手机号，结果AES加密后输出正好32字节，差点撑爆。后来统一改成256，省心。

4.3 UPDATE语句中的加密改写

UPDATE的逻辑和INSERT类似，但有一个坑——部分更新。有些业务场景只需要更新某几个加密字段，这时候就要注意了：

-- 只更新加密字段，其他字段不变
UPDATE t_user
SET phone = AES_ENCRYPT('13900139000', 'my_secret_key')
WHERE user_id = 1001;

-- 多个加密字段一起更新
UPDATE t_user
SET 
    phone = AES_ENCRYPT('13900139000', 'my_secret_key'),
    email = AES_ENCRYPT('zhangsan@example.com', 'my_secret_key')
WHERE user_id = 1001;

你想想看，如果更新时顺手把别的字段也覆盖了，那后果……所以写UPDATE时，WHERE条件一定要带对。我习惯在UPDATE前先查一次，确认要改哪几条记录。

4.4 SELECT语句中的解密还原

加密存进去了，怎么读出来呢？说白了就是反向操作：

-- 原始SQL
SELECT user_id, user_name, phone FROM t_user WHERE user_id = 1001;

-- 改写后SQL
SELECT 
    user_id,
    user_name,
    AES_DECRYPT(phone, 'my_secret_key') AS phone
FROM t_user
WHERE user_id = 1001;

这里有个关键点：AES_DECRYPT返回的也是二进制，需要转成字符串。MySQL里可以套一层 CAST：

SELECT 
    user_id,
    user_name,
    CAST(AES_DECRYPT(phone, 'my_secret_key') AS CHAR) AS phone
FROM t_user;
重点：如果解密失败（比如密钥不对或数据损坏），AES_DECRYPT会返回NULL。所以查询结果里出现NULL时，先检查密钥有没有配错。

4.5 实战中的常见场景

光讲CRUD太干了，我结合一个真实案例说说。去年做个电商订单系统，订单表有十几个字段需要加密，包括收货人、手机号、地址。最头疼的是批量插入：

-- 批量插入加密数据
INSERT INTO t_order (order_id, user_id, receiver_name, receiver_phone, receiver_addr, order_amount)
VALUES
(2024001, 1001, AES_ENCRYPT('李四', 'key'), AES_ENCRYPT('13600136000', 'key'), AES_ENCRYPT('北京市朝阳区xxx', 'key'), 199.00),
(2024002, 1002, AES_ENCRYPT('王五', 'key'), AES_ENCRYPT('13700137000', 'key'), AES_ENCRYPT('上海市浦东新区xxx', 'key'), 299.00),
(2024003, 1003, AES_ENCRYPT('赵六', 'key'), AES_ENCRYPT('13800138000', 'key'), AES_ENCRYPT('广州市天河区xxx', 'key'), 399.00);

这种做法性能还行，但有个问题：每个值都重复传密钥。如果要用不同密钥加密不同字段怎么办？可以用存储过程或应用层封装一下。

4.6 最容易翻车的点：WHERE条件

我曾经遇到一个线上事故，某同事在WHERE里直接写解密后的值：

-- 错误写法！索引失效
SELECT * FROM t_user
WHERE CAST(AES_DECRYPT(phone, 'key') AS CHAR) = '13800138000';

为什么说这是错的？因为全表扫描。数据库拿到每条数据都要先解密，再比较，几十万条记录直接就慢查询了。而且解密后的值没法建索引。

正确的做法是：先用其他条件缩小范围，或者在应用层做查询。如果非要按加密字段查询，可以考虑加一个哈希索引列，存明文SHA256的摘要，用来做精确匹配。这个后面章节我们会细聊。

准则：绝不在WHERE条件中使用解密函数，除非你能忍受全表扫描。记住这句话。

4.7 知识体系总览

到这里，INSERT/UPDATE/SELECT的加密改写基本讲完了。我画了张图，把这个流程串起来：

列级加密SQL改写核心流程 应用层输入 明文数据 INSERT AES_ENCRYPT() 加密写入 UPDATE AES_ENCRYPT() 更新密文 SELECT AES_DECRYPT() 解密展示 数据库存储 密文（VARBINARY） ⚠️ 注意 绝不可以在WHERE中解密 会触发全表扫描

4.8 核心要点总结

为了让你记得更牢，我把今天的内容整理成一个清单：

  • INSERT：在VALUES中用加密函数替换明文值，字段类型要改成VARBINARY
  • UPDATE：在SET子句中调用加密函数，注意不要误覆盖其他字段
  • SELECT：用解密函数还原数据，记得用CAST转成字符串
  • 禁止：在WHERE条件中使用解密函数，这是性能杀手
  • 密钥：统一管理，别硬编码。改一次密钥你就知道疼了
← 上一章 📖 返回目录 下一章 →