MySQL VARCHAR 轉 INT 類型轉換函數 CAST 與 CONVERT 實戰

MySQL VARCHAR 轉 INT 類型轉換函數 CAST 與 CONVERT 實戰

業務中常需將 VARCHAR 存儲的數字字符串按數值大小排序/篩選/運算。MySQL 提供 CAST() 和 CONVERT() 兩大標準轉換函數。本文記錄語法、簽名差異、SIGNED/UNSIGNED 選擇、排序實戰、以及隱式轉換對比。

一、兩大函數語法對比

1.1 CAST (SQL 標準語法)

CAST(expr AS type)
-- type 可選值:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]      -- 有符號整數
UNSIGNED [INTEGER]    -- 無符號整數
TIME

1.2 CONVERT (MySQL 擴展語法，支持字符集轉換)

CONVERT(expr, type)
-- 或字符集轉換語法:
CONVERT(expr USING charset_name)

二、VARCHAR 轉 INT 實戰示例

2.1 排序場景：字符串排序 vs 數值排序

-- 表 cardserver，server_id VARCHAR(20) 存儲 '1','2','10','100'
-- 字符串排序: '1', '10', '100', '2' (字典序)
-- 數值排序: 1, 2, 10, 100

-- CAST 方案
SELECT server_id 
FROM cardserver 
WHERE game_id = 1 
ORDER BY CAST(server_id AS SIGNED) DESC 
LIMIT 10;

-- CONVERT 方案
SELECT server_id 
FROM cardserver 
WHERE game_id = 1 
ORDER BY CONVERT(server_id, SIGNED) DESC 
LIMIT 10;

2.2 SIGNED vs UNSIGNED 選擇

建議： ID類字段優先 UNSIGNED，擴大正數範圍、語義更清晰。

三、更多實戰模板

-- 1. 篩選數值範圍
SELECT * FROM gyzd_yysinfo 
WHERE CAST(yysid AS UNSIGNED) BETWEEN 100 AND 200;

-- 2. 數值運算
SELECT CAST(price_str AS DECIMAL(10,2)) * quantity AS total 
FROM order_items;

-- 3. 分組聚合
SELECT CAST(category_id AS UNSIGNED) AS cat_id, COUNT(*) 
FROM products 
GROUP BY cat_id;

-- 4. 連接查詢類型對齊 (VARCHAR ID 關聯 INT ID)
SELECT u.*, o.* 
FROM users u 
JOIN orders o ON CAST(u.user_id AS UNSIGNED) = o.user_id;  -- 索引可能失效，見下文

-- 5. 字符集轉換 (CONVERT 專有)
SELECT CONVERT(name USING utf8mb4) FROM legacy_table;

四、性能與索引影響 (關鍵！)

4.1 函數作用於列 → 索引失效

方案適用場景優缺點
虛擬生成列 + 索引 (5.7+)頻繁按數值排序/篩選✅ 索引生效、✅ 寫入透明、❌ 佔額外空間
應用層轉換寫入 INT 列新建表/重構✅ 根治、✅ 索引原生、❌ 需遷移
覆蓋索引包含原 VARCHAR 列偶爾查詢、數據量小⚠️ 仍需 Filesort、但避免回表
按字符串排序兼容 (左補零)固定長度數字 ID✅ 索引生效、❌ 業務侵入性強


4.3 虛擬生成列最佳實踐 (5.7+)
需求推薦方案關鍵語法
臨時查詢排序/篩選CAST/CONVERTCAST(col AS UNSIGNED)
高頻查詢、大表虛擬生成列 + 索引GENERATED ALWAYS AS (CAST(col AS UNSIGNED)) VIRTUAL
新表設計/重構直接定義 INT/BIGINT UNSIGNEDid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
字符集轉換CONVERT ... USINGCONVERT(col USING utf8mb4)


核心提醒： CAST/CONVERT 解決「讀取時語義正確」，但**破壞索引**；高頻場景**必須**用虛擬生成列或重構列類型物質化數值，實現「存儲即索引、查詢即命中」。