MySQL 中 TEXT 型別預設僅對首字元排序。例如欄位 f1 存儲數字字串 '100'、'20'、'3',執行 ORDER BY f1 ASC 會得到 '100', '20', '3'(字典序),而非預期的數值序 '3', '20', '100'。
TEXT 屬於二進位大物件,MySQL 排序時視為二進位字串,逐字元比較 ASCII 碼,故 '1' < '2' < '3',導致 '100' 排在最前。
使用 CAST(expr AS type) 將欄位轉為數值型別再排序:
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 且產生警告同樣涉及字串處理,LENGTH() 回傳位元組數(UTF-8 中文 3 位元組),CHAR_LENGTH() 回傳字元數(中文算 1)。排序長度時請按語意選擇。
CAST(f1 AS SIGNED) 是解決 TEXT 數字字串排序的速效良方,但請權衡效能與資料量。長期專案建議從結構設計著手,避免「字串存數字」的反模式。