語法
SELECT col1, col2 ...
FROM table_name
[WHERE condition]
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC] ...
核心規則
- 默認
ASC 升序，DESC 降序
- 可多欄排序：先按第一列，相同再按第二列……
- 可與
WHERE、LIMIT 組合
NULL 排序：ASC 時 NULL 在前，DESC 時 NULL 在後
命令行示例
mysql> SELECT * FROM tutorials_tbl ORDER BY tutorial_author ASC;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
+-------------+----------------+-----------------+-----------------+
mysql> SELECT * FROM tutorials_tbl ORDER BY tutorial_author DESC;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
+-------------+----------------+-----------------+-----------------+
PHP 實現
<?php
$mysqli = new mysqli('localhost', 'root', 'rootpassword', 'TUTORIALS', 3306);
if ($mysqli->connect_errno) { die('連接失敗: ' . $mysqli->connect_error); }
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl ORDER BY tutorial_author DESC";
$result = $mysqli->query($sql);
if (!$result) { die('查詢失敗: ' . $mysqli->error); }
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['tutorial_id']} | 標題: {$row['tutorial_title']} | 作者: {$row['tutorial_author']} | 日期: {$row['submission_date']}<br>";
}
$result->free();
$mysqli->close();
?>
性能優化關鍵
- 索引支持排序：
ORDER BY col 若有索引可直接按索引順序讀取，避免 filesort。
- 複合索引順序：
WHERE a=1 ORDER BY b, c → 索引 (a, b, c)；WHERE a>1 ORDER BY b → 索引 (a, b) 可能用不上排序部分。
- 避免混合 ASC/DESC：MySQL 8.0+ 支持降序索引
INDEX (a ASC, b DESC)，舊版本混合方向會導致 filesort。
- LIMIT 配合：
ORDER BY id DESC LIMIT 10 只需掃描索引前 10 行，極快。
