MySQL VARCHAR 存儲數字排序錯誤：字符串排序 vs 數值排序解決方案

問題現象

-- id VARCHAR(20) 存純數字：'1','2','10','20','100'
SELECT * FROM t ORDER BY id;
-- 結果：1, 10, 100, 2, 20  (字典序/字符串排序)

-- 期望：1, 2, 10, 20, 100  (數值排序)

核心解決方案：隱式/顯式類型轉換

方法一：算術運算強制轉型 (最簡潔)

SELECT * FROM t ORDER BY id + 0;
SELECT * FROM t ORDER BY id * 1;
SELECT * FROM t ORDER BY id - 0;

原理：MySQL 字符串參與數學運算 → 自動轉為 DOUBLE → 數值排序。

方法二：CAST 顯式轉型 (標準、清晰)

SELECT * FROM t ORDER BY CAST(id AS UNSIGNED);
SELECT * FROM t ORDER BY CAST(id AS SIGNED);

方法三：CONVERT 函數

SELECT * FROM t ORDER BY CONVERT(id, UNSIGNED INTEGER);

進階排序：FIND_IN_SET 自定義順序

場景：按指定 ID 列表順序輸出 (如熱門商品置頂)。

-- $string_hot_pid = '100,50,200,10'
SELECT p.*, FIND_IN_SET(p.id, '100,50,200,10') AS rank
FROM products p
WHERE p.id IN (100,50,200,10)
ORDER BY rank;

FIND_IN_SET 語法與特性

FIND_IN_SET(str, strlist)
-- strlist: 逗號分隔字符串 'a,b,c'
-- 返回: str 在列表中的位置 (1-based)，不存在返回 0
-- 優化: 若 str 為常量、strlist 為 SET 列 → 位運算優化
-- 限制: str 不能包含逗號，strlist 超長需調大 group_concat_max_len

性能對比與索引影響

虛擬列優化方案 (MySQL 5.7+)

ALTER TABLE t 
ADD COLUMN id_num INT GENERATED ALWAYS AS (CAST(id AS UNSIGNED)) STORED,
ADD INDEX idx_id_num (id_num);

-- 查詢自動走索引
SELECT * FROM t ORDER BY id_num;

避坑指南

1. 非純數字字符串：'123abc' + 0 = 123 (截取前導數字) + Warning
2. 前導零：'001' + 0 = 1，排序正確但失去前導零語義
3. 負數：CAST('-10' AS SIGNED) 正確，UNSIGNED 會變大正數
4. 超大數字：超出 BIGINT 範圍用 DECIMAL(65,0) 或字符串左補零定長排序
5. NULL 值：排序時 NULL 排在最前 (ASC) 或最後 (DESC)

最佳實踐

1. 新系統：正確類型建模：數字用 INT/BIGINT/DECIMAL，拒絕 VARCHAR 存數字
2. 存量系統：虛擬列索引：不修改應用代碼，DB 層透明優化
3. 臨時查詢：CAST/算術轉型：可讀性優先
4. 自定義順序：FIND_IN_SET：列表較短 (＜1000) 時極其高效

字符串排序與數值排序的本質差異在於 **比較器**。MySQL 提供多種轉型路徑，**選對工具、加上索引、修正建模**，徹底告別「10 排在 2 前面」的低級錯誤。