您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 字符與數字轉換實戰技巧：隱式轉換、顯式轉換與字符集處理

編輯：MySQL 資料庫相關知識

核心轉換場景與技巧

1. 字符數字隱式轉數值（+0 技巧）

-- VARCHAR 排序按字典序：'1','10','2' → 錯誤順序
SELECT * FROM pony ORDER BY d;        -- 1, 10, 2
SELECT * FROM pony ORDER BY (d + 0);  -- 1, 2, 10 正確數值序

原理：MySQL 遇到算術運算會嘗試將操作數轉為數值，非數字前綴轉為 0。

2. IFNULL/COALESCE 類型一致性陷阱

-- 錯誤：返回類型被字符串 '0' 污染
SELECT IFNULL(a/b, '0');  -- 結果列類型變為 VARCHAR

-- 正確：使用數值 0
SELECT IFNULL(a/b, 0);    -- 結果保持數值類型

3. 字符與數字比較的隱式轉換規則

SELECT 11 = '11ddddd';     -- 1 (true!) MySQL 截取前導數字比較
SELECT 11 = 'abc';         -- 0 (false) 無數字前綴視為 0
SELECT BINARY 11 = '11ddddd'; -- 0 (false) 二進制嚴格比較

規則：字符串比較數字時，字符串提取前導數字部分轉數值比較。嚴格比較用 BINARY 或 CAST。

4. 顯式類型轉換：CAST / CONVERT

-- 標準 SQL
CAST(expr AS type)
CONVERT(expr, type)

-- MySQL 特有：字符集轉換
CONVERT(expr USING charset_name)
-- 例：CONVERT('中文' USING gb2312)

MySQL 支持的目標類型

類型關鍵字	說明	示例
BINARY	二進制字符串，等同 BINARY 前綴	CAST('a' AS BINARY)
CHAR[(N)]	定長字符串，可指定長度	CAST(123 AS CHAR(10))
DATE	日期 YYYY-MM-DD	CAST('2024-01-15' AS DATE)
TIME	時間 HH:MM:SS	CAST('14:30:00' AS TIME)
DATETIME	日期時間	CAST('2024-01-15 14:30:00' AS DATETIME)
DECIMAL[(M,D)]	定點數/浮點數	CAST('123.45' AS DECIMAL(10,2))
SIGNED [INTEGER]	有符號整數	CAST('-10' AS SIGNED)
UNSIGNED [INTEGER]	無符號整數	CAST('10' AS UNSIGNED)

實戰避坑指南

排序修正：數字存 VARCHAR 時，ORDER BY col+0 或 ORDER BY CAST(col AS UNSIGNED)
函數返回類型：IFNULL/COALESCE/CASE 所有分支類型必須一致，優先用數值 0 而非 '0'
比較安全：WHERE 子句涉及字符數字混比，WHERE CAST(col AS UNSIGNED) = 11 或 WHERE col = '11' AND col NOT LIKE '%[^0-9]%'
字符集轉換：跨編碼系統交互用 CONVERT(str USING utf8mb4)，避免亂碼