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. 二進制轉換函數

函數	描述	示例
BIN()	十進制轉二進制	BIN(10) → '1010'
HEX()	轉十六進制	HEX(255) → 'FF'
OCT()	轉八進制	OCT(8) → '10'
CONV(num, from_base, to_base)	任意進制轉換	CONV('A',16,10) → '10'

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. 不可逆哈希函數

函數	算法	輸出長度	特點
MD5()	MD5	32字符	已不推薦用于安全場景
SHA1()	SHA-1	40字符	安全漏洞，不推薦
SHA2()	SHA-2	可選長度	推薦使用

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. 其他加密函數

函數	用途	注意事項
ENCODE()/DECODE()	簡單加密	已廢棄，不安全
DES_ENCRYPT()	DES加密	需要SSL支持
COMPRESS()	數據壓縮	非加密函數，但常配合使用
UNCOMPRESS()	解壓數據	需處理NULL值

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
BEGINRETURN 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指令集（服務器配置）
   • 使用專用加密硬件

---