MySQL VARCHAR 字符串類型數字排序問題的三種解決方案

MySQL 字符串欄位存數字時的排序陷阱與解法

業務常見場景：欄位定義為 VARCHAR 但實際存純數字（如訂單號、商品 ID）。直接 ORDER BY col 會按字典序排序（1, 10, 11, 2, 3...），導致業務邏輯錯誤。本文提供三種將字符串按數值排序的實用技巧。

現象示例

CREATE TABLE t (id VARCHAR(10));
INSERT INTO t VALUES ('1'),('2'),('10'),('3'),('20');
SELECT * FROM t ORDER BY id;  -- 結果：1, 10, 2, 20, 3 (字典序)

方案一：隱式類型轉換（最簡單）

利用 MySQL 自動類型轉換，讓字符串參與數學運算即轉為數值：

SELECT * FROM t ORDER BY id + 0;      -- 或 id * 1
SELECT * FROM t ORDER BY CAST(id AS UNSIGNED);

• +0 / *1：零成本隱式轉換，執行計劃仍可使用索引（MySQL 5.6+）。
• CAST(... AS UNSIGNED)：語意明確，適合嚴格 SQL 模式。

方案二：FIND_IN_SET 指定順序（適用於固定枚舉值）

若排序順序由業務自定義（非單純大小），可用 FIND_IN_SET：

SET @sort_order = '10,1,20,2,3';  -- 業務指定順序
SELECT *, FIND_IN_SET(id, @sort_order) AS rank
FROM t
WHERE id IN (1,2,3,10,20)
ORDER BY rank;

注意：FIND_IN_SET 參數必須是常量字符串或 SET 類型列，否則無法使用位運算優化；且字符串中不能包含逗號。

SELECT * FROM t ORDER BY LPAD(id, 10, '0');
-- 結果：0000000001, 0000000002, 0000000003, 0000000010, 0000000020