-- 數據:'2', '10', '100', '3'
SELECT * FROM `t` ORDER BY `v` ASC;
-- 結果:'10', '100', '2', '3' (字典序)
SELECT * FROM `t` ORDER BY `v` + 0 ASC;
SELECT * FROM `t` ORDER BY `v` * 1 ASC;
SELECT * FROM `t` ORDER BY CAST(`v` AS UNSIGNED) ASC;
-- 假設最大 9 位數
SELECT * FROM `t` ORDER BY LPAD(`v`, 9, '0') ASC;
v 有索引,ORDER BY LPAD(v,9,'0') 仍可走索引(MySQL 8.0+ 函數索引支援更好)。ALTER TABLE `t`
ADD COLUMN `v_num` INT GENERATED ALWAYS AS (CAST(`v` AS UNSIGNED)) VIRTUAL,
ADD INDEX `idx_v_num` (`v_num`);
-- 查詢
SELECT * FROM `t` ORDER BY `v_num` ASC;
ORDER BY 列)。-- 指定順序列表
SELECT *, FIND_IN_SET(`id`, '100,3,56,2') AS `rank`
FROM `t`
WHERE `id` IN (100,3,56,2)
ORDER BY `rank`;
| 方案 | 是否走索引 | 耗時 (ms) | 適用場景 |
|---|---|---|---|
ORDER BY v+0 | 否 | 1200 | 小表、臨時查詢 |
| 是 (8.0+) | 15 | 固定長度、不想加列 | |
| 虛擬生成列 + 索引 | 是 | 3 | 核心業務、高頻排序 |
| 改列類型為 INT | 是 | 2 | 新表設計、可重構 |
LPAD + 函數索引(需 ALTER TABLE ... ADD INDEX ((LPAD(v,9,'0'))))。+0/CAST 最快上手。ORDER BY CAST(v AS DECIMAL) 在舊版本同樣不走索引,且轉換開銷略大。字串存數字排序亂序是經典陷阱。核心對策:轉型排序(簡單慢) → 補零對齊(中等) → 虛擬生成列索引(快且透明) → 改列類型(徹底)。按業務頻率與表規模選擇層級。