MySQL 無限級分類排序：字串排序陷阱與正確方案

現象

sorts 欄位 varchar 存 '1', '1-2', '1-2-3', '1-11', '1-11-10'，ORDER BY sorts 導致 1-11 排在 1-2 **前面**（字典序：'1'='1', '-'='-', '1' < '2')。

根因

**字串逐字元比較**，'1-11' vs '1-2'：前三字元 '1','-','1' vs '1','-','2'，'1' (ASCII 49) < '2' (50) → 1-11 排前。

解決方案

1. 定寬零填充：每層固定 3 位，001, 001-002, 001-002-003, 001-011，字串排序即正確。
   插入時：LPAD(num, 3, '0')。
2. 分層欄位 + 遞迴 CTE (MySQL 8.0+)：
   表結構 id, parent_id, name, seq (同層排序號)。
   查詢：
   

   WITH RECURSIVE cte AS (
       SELECT id, parent_id, name, seq, CAST(LPAD(seq,3,'0') AS CHAR(100)) AS path
       FROM categories WHERE parent_id = 0
       UNION ALL
       SELECT c.id, c.parent_id, c.name, c.seq, CONCAT(cte.path, '-', LPAD(c.seq,3,'0'))
       FROM categories c JOIN cte ON c.parent_id = cte.id
   )
   SELECT * FROM cte ORDER BY path;

3. 物化路徑：新增 path VARCHAR(500) 存 '001/002/003/'，查詢 ORDER BY path，插入/移動時觸發器維護路徑。

推薦

**方案二 (CTE + 定寬 seq)** 結構清晰、查詢高效、支援無限層級、避免字串排序陷阱。