PHP MySQL ORDER BY 子句完整教學

語法

SELECT column_list FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

• ASC (預設)：升序 (小到大、A-Z)
• DESC：降序 (大到小、Z-A)

MySQLi 範例

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if ($mysqli->connect_errno) die('連線失敗: ' . $mysqli->connect_error);

// 年齡升序
$sql = "SELECT * FROM Persons ORDER BY Age ASC";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
    echo $row['FirstName'] . ' ' . $row['LastName'] . ' ' . $row['Age'] . '<br>';
}

/* 輸出範例:
Glenn Quagmire 33
Peter Griffin 35
*/
$mysqli->close();
?>

多欄位排序

當第一欄位值相同時，才依第二欄位排序：

SELECT * FROM Persons ORDER BY LastName ASC, FirstName ASC

PDO 範例

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->query("SELECT * FROM Persons ORDER BY Age DESC");
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['FirstName'] . ' ' . $row['LastName'] . ' ' . $row['Age'] . '<br>';
    }
} catch (PDOException $e) {
    echo "錯誤: " . $e->getMessage();
}
?>

效能與索引建議

• ORDER BY 欄位若有索引，可避免 filesort 大幅提升效能。
• 複合索引順序需符合 ORDER BY 欄位順序與方向 (如 INDEX (LastName, FirstName))。
• ORDER BY RAND() 會全表掃描，大表極慢，改用應用層隨機抽樣 (如先取 ID 範圍再隨機)。
• 分頁查詢 ORDER BY id LIMIT 100000, 20 深分頁效能差，改用游標式分頁 WHERE id > last_id ORDER BY id LIMIT 20。

動態排序參數化 (防注入)

<?php
$allowed = ['Age', 'FirstName', 'LastName', 'CreatedAt'];
$dirAllowed = ['ASC', 'DESC'];

$col = in_array($_GET['sort'] ?? '', $allowed) ? $_GET['sort'] : 'Age';
$dir = in_array(strtoupper($_GET['dir'] ?? ''), $dirAllowed) ? strtoupper($_GET['dir']) : 'ASC';

$sql = "SELECT * FROM Persons ORDER BY `$col` $dir";
// 使用反引號包裹識別符，配合白名單驗證，防止 SQL 注入
?>