萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL TEXT 欄位排序失效?CAST 轉型破解首字元排序陷阱

問題現象:TEXT 欄位排序只看第一個字元

MySQL 中 TEXT 型別預設僅對首字元排序。例如欄位 f1 存儲數字字串 '100'、'20'、'3',執行 ORDER BY f1 ASC 會得到 '100', '20', '3'(字典序),而非預期的數值序 '3', '20', '100'。

根本原因

TEXT 屬於二進位大物件,MySQL 排序時視為二進位字串,逐字元比較 ASCII 碼,故 '1' < '2' < '3',導致 '100' 排在最前。

解決方案:CAST 顯式轉型

使用 CAST(expr AS type) 將欄位轉為數值型別再排序:

寫法一:直接在 ORDER BY 轉型

SELECT * FROM `table` ORDER BY CAST(f1 AS SIGNED) ASC;

寫法二:搭配別名複用

SELECT CAST(f1 AS SIGNED) AS f2 FROM `table` ORDER BY f2 ASC;

支援的目標型別

  • SIGNED [INTEGER]:有號整數(含負數)
  • UNSIGNED [INTEGER]:無號整數(非負)
  • DECIMAL:定點數(支援小數)
  • CHAR[(N)]、BINARY[(N)]:字串/二進位
  • DATE、DATETIME、TIME:日期時間

實測效果對比

轉型前(字典序):

+------+
| f1   |
+------+
| 100  |
| 20   |
| 3    |
+------+

轉型後(數值序):

+------+
| f1   |
+------+
| 3    |
| 20   |
| 100  |
+------+

效能考量與最佳實務

  • 索引失效:CAST() 會導致無法使用欄位索引,大量資料排序較慢
  • 替代方案:
    • 設計階段改用 INT/BIGINT/DECIMAL 數值型別
    • 若必須用 TEXT,可新增 GENERATED COLUMN(MySQL 5.7+)儲存轉型值並建索引
    • 應用層排序:撈資料後在程式端排序
  • 資料清洗:轉型前確認資料純數字,否則 CAST('abc' AS SIGNED) 回傳 0 且產生警告

延伸:CHAR_LENGTH vs LENGTH

同樣涉及字串處理,LENGTH() 回傳位元組數(UTF-8 中文 3 位元組),CHAR_LENGTH() 回傳字元數(中文算 1)。排序長度時請按語意選擇。

結語

CAST(f1 AS SIGNED) 是解決 TEXT 數字字串排序的速效良方,但請權衡效能與資料量。長期專案建議從結構設計著手,避免「字串存數字」的反模式。

copyright © 萬盛學電腦網 all rights reserved