欄位 server_id VARCHAR(2) 存儲數字字串,直接 ORDER BY server_id DESC 產生字典序:
8, 7, 6, 5, 4, 3, 2, 10, 1 -- 10 排在 2 之後!
SELECT server_id FROM cardserver
WHERE game_id = 1
ORDER BY server_id + 0 DESC LIMIT 10;
-- 結果:10, 8, 7, 6, 5, 4, 3, 2, 1
MySQL 遇到數學運算會自動將字串轉為數值。
SELECT server_id FROM cardserver
WHERE game_id = 1
ORDER BY CAST(server_id AS SIGNED) DESC LIMIT 10;
SELECT server_id FROM cardserver
WHERE game_id = 1
ORDER BY CONVERT(server_id, SIGNED) DESC LIMIT 10;
| 目標型別 | 說明 |
|---|---|
| SIGNED [INTEGER] | 帶正負號整數 |
| UNSIGNED [INTEGER] | 無正負號整數 |
| DECIMAL | 定點小數 |
| CHAR[(N)] | 定長字串 |
| DATE / DATETIME / TIME | 日期時間 |
| BINARY[(N)] | 二進位字串 |
Using filesort,因排序發生在轉型後。SMALLINT UNSIGNED 或 INT,避免轉型開銷並啟用索引排序。INDEX idx_game_server (game_id, server_id) 並使用 ORDER BY server_id + 0。INDEX (game_id, CAST(server_id AS UNSIGNED)) (MySQL 8.0+ 支援函數索引)。