MySQL 类型转换与加密函数深度解析

MySQL 类型转换与加密函数深度解析

一、类型转换函数详解

1. 显式类型转换

CAST 函数

CAST(expression AS type)

• 支持类型：BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED [INTEGER], UNSIGNED [INTEGER]
• 示例：

  SELECT CAST('2023-08-15' AS DATE); -- 2023-08-15
  SELECT CAST(123.456 AS DECIMAL(5,2)); -- 123.46 (四舍五入)
  SELECT CAST('123' AS SIGNED); -- 123
  SELECT CAST(123 AS CHAR); -- '123'
  

CONVERT 函数

CONVERT(expression, type)
CONVERT(expression USING charset)

• 两种形式：类型转换和字符集转换
• 示例：

  SELECT CONVERT('abc' USING utf8mb4); -- 字符集转换
  SELECT CONVERT(123.456, DECIMAL(5,2)); -- 123.46
  

2. 隐式类型转换

MySQL 在以下场景自动转换类型：

• 数值计算：'10' + 5 → 15
• 字符串连接：CONCAT('ID:', 100) → 'ID:100'
• 比较操作：WHERE int_column = '123'

3. 格式化函数

FORMAT(number, decimal_places) -- 数字格式化
DATE_FORMAT(date, format)      -- 日期格式化

• 示例：

  SELECT FORMAT(1234567.89, 2); -- '1,234,567.89'
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2025-06-17 14:30:45'
  

4. 二进制转换函数

5. 类型转换注意事项

1. 精度丢失：

   SELECT CAST(123.789 AS UNSIGNED); -- 123 (小数部分截断)
   

2. 日期转换陷阱：

   SELECT CAST('2023-02-30' AS DATE); -- NULL (非法日期)
   

3. 字符集不一致：

   SELECT CAST(_utf8'你好' AS CHAR CHARACTER SET latin1); -- 乱码
   

4. 性能影响：

   -- 避免在WHERE条件中使用转换（索引失效）
   SELECT * FROM orders WHERE CAST(order_id AS CHAR) = '1001';
   

二、加密函数详解

1. 不可逆哈希函数

SHA2 使用详解

SHA2(str, hash_length) -- hash_length: 224, 256, 384, 512

示例：

SELECT SHA2('password', 256); 
-- '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8'

2. 可逆加密函数

AES 加解密

-- 加密（结果以二进制存储）
SELECT AES_ENCRYPT('secret', 'encryption_key');

-- 解密
SELECT CAST(AES_DECRYPT(encrypted_data, 'encryption_key') AS CHAR);

最佳实践：

1. 使用VARBINARY类型存储加密数据
2. 密钥长度：128, 192或256位
3. 示例完整流程：

   CREATE TABLE secure_data (
     id INT PRIMARY KEY,
     secret VARBINARY(200)
   );
   
   INSERT INTO secure_data 
   VALUES (1, AES_ENCRYPT('信用卡号', 'my_secure_key'));
   
   SELECT id, CAST(AES_DECRYPT(secret, 'my_secure_key') AS CHAR) 
   FROM secure_data;
   

3. 密码存储专用函数

推荐方案：bcrypt（需应用层实现）

MySQL内置方案：

-- 创建密码哈希
CREATE USER 'test'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password';

-- 模拟密码验证
SELECT PASSWORD('password'); -- 生成哈希（已废弃）

4. 其他加密函数

5. 加密函数安全准则

1. 密钥管理：

   • 切勿硬编码密钥
   • 使用MySQL密钥环或外部密钥管理服务

2. 算法选择：

   • 优先选择AES和SHA2
   • 弃用MD5、SHA1和DES

3. 数据存储：

   -- 正确设置二进制字段
   CREATE TABLE user_secrets (
     user_id INT,
     secret VARBINARY(256) -- 足够存储加密后数据
   );
   

4. 传输安全：

   • 始终使用SSL/TLS连接
   • 启用require_secure_transport

三、综合应用案例

安全数据存储系统

-- 创建安全表
CREATE TABLE financial_records (
  record_id INT AUTO_INCREMENT PRIMARY KEY,
  plain_text VARCHAR(100), -- 非敏感数据
  encrypted_data VARBINARY(256), -- AES加密数据
  data_hash CHAR(64) -- SHA256校验值
);

-- 插入加密记录
INSERT INTO financial_records (plain_text, encrypted_data, data_hash)
VALUES (
  '交易摘要',
  AES_ENCRYPT('卡号:1234 余额:$5000', 'my_super_secret_key'),
  SHA2('卡号:1234 余额:$5000', 256)
);

-- 查询验证与解密
SELECT 
  plain_text,
  CAST(AES_DECRYPT(encrypted_data, 'my_super_secret_key') AS decrypted_data,
  data_hash = SHA2(CAST(AES_DECRYPT(encrypted_data, 'my_super_secret_key') AS CHAR), 256) AS hash_verified
FROM financial_records;

四、常见错误及解决方案

类型转换错误

-- 错误：转换失败
SELECT CAST('abc' AS UNSIGNED); -- 结果为0

-- 安全转换函数（自定义）
CREATE FUNCTION safe_cast_int(str VARCHAR(20))
RETURNS INT DETERMINISTIC
BEGIN
  RETURN CAST(str AS SIGNED); -- 简单示例，实际需更复杂校验
END;

加密数据截断

-- 错误：加密后数据超出字段容量
CREATE TABLE small_table (
  data VARBINARY(10) -- 太小
);
INSERT INTO small_table 
VALUES (AES_ENCRYPT('long data...', 'key')); -- 可能截断

-- 解决方案：计算最大长度
SELECT MAX(LENGTH(AES_ENCRYPT('your data', 'key')));

密钥轮换问题

-- 多密钥支持表设计
CREATE TABLE key_management (
  key_id INT PRIMARY KEY,
  key_value VARBINARY(256),
  active BOOL
);

-- 解密时尝试多个密钥
SELECT 
  COALESCE(
    CAST(AES_DECRYPT(data, key1) AS CHAR),
    CAST(AES_DECRYPT(data, key2) AS CHAR)
  ) AS decrypted
FROM records;

五、性能优化建议

1. 加密代价：

   -- 批量加密避免重复连接
   SET @key = 'key';
   INSERT INTO secure_table
   SELECT 
     AES_ENCRYPT(data, @key) 
   FROM large_table;
   

2. 索引限制：

   • 加密字段无法有效索引
   • 解决方案：添加哈希值索引

   ALTER TABLE users 
   ADD COLUMN email_hash BINARY(32) 
     AS (UNHEX(SHA2(email, 256))) VIRTUAL,
   ADD INDEX idx_email_hash (email_hash);
   

3. 硬件加速：

   • 启用AES-NI指令集（服务器配置）
   • 使用专用加密硬件