PHP MySQL Order By
Sorting, multi-column order, and why the sort column needs an allowlist.
Key PointsSort columns cannot be bound — validate against an allowlist and interpolate.
Basic ordering
PHP
<?php
$stmt = $pdo->query("SELECT name, age FROM users ORDER BY age DESC");
foreach ($stmt->fetchAll() as $r) {
echo "{$r['name']} {$r['age']}\n";
}
echo "---\n";
$stmt = $pdo->query("SELECT name, age FROM users ORDER BY name ASC");
foreach ($stmt->fetchAll() as $r) {
echo $r["name"] . "\n";
}Output
Bob 28
Tom 30
Amy 25
---
Amy
Bob
TomMulti-column ordering
PHP
<?php
$stmt = $pdo->query(
"SELECT name, status, age FROM users
ORDER BY status ASC, age DESC"
);
foreach ($stmt->fetchAll() as $r) {
echo "{$r['status']} {$r['name']} {$r['age']}\n";
}Output
1 Bob 28
1 Tom 30
2 Amy 25Sort by the first column, then by the second when tied. Order is priority — reversing it changes the result.
The sort column needs an allowlist
ORDER BY ? is a syntax error — identifiers cannot be bound. Interpolating is an injection point. An allowlist map is the only safe route.
PHP
<?php
$allowed = [
"id" => "id",
"name" => "name",
"age" => "age",
"time" => "created_at",
];
$sort = $_GET["sort"] ?? "id";
$dir = strtolower($_GET["dir"] ?? "asc") === "desc" ? "DESC" : "ASC";
if (!isset($allowed[$sort])) {
$sort = "id";
}
$column = $allowed[$sort];
$sql = "SELECT name FROM users ORDER BY $column $dir";
echo $sql, "\n";Output
SELECT name FROM users ORDER BY created_at DESCThe allowlist means user input is only a lookup key, never part of the SQL. Even
sort=id;DROP TABLE users fails isset and falls back.PHP
<?php
// 错误写法演示（不要用）
$bad = "id; DROP TABLE users";
$sql = "SELECT name FROM users ORDER BY $bad";
echo $sql, "\n";
// 白名单后
$allowed = ["id" => "id", "name" => "name"];
$col = $allowed[$bad] ?? "id";
echo "SELECT name FROM users ORDER BY $col", "\n";Output
SELECT name FROM users ORDER BY id; DROP TABLE users
SELECT name FROM users ORDER BY idWhere NULLs sort
MySQL treats NULL as smallest: first in ASC, last in DESC. Reorder explicitly if needed.
PHP
<?php
$sql1 = "SELECT name FROM users ORDER BY age ASC";
$sql2 = "SELECT name FROM users ORDER BY age IS NULL, age ASC";
echo $sql1, "\n";
echo $sql2, "\n";Output
SELECT name FROM users ORDER BY age ASC
SELECT name FROM users ORDER BY age IS NULL, age ASCage IS NULL yields 0 or 1, so sorting by it puts non-nulls first — MySQL's equivalent of NULLS LAST.
Sorting with paging
PHP
<?php
$page = max(1, (int)($_GET["page"] ?? 1));
$size = 2;
$offset = ($page - 1) * $size;
$total = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$pages = (int)ceil($total / $size);
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d OFFSET %d", $size, $offset);
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);
echo "第 $page/$pages 页: " . implode(", ", $rows) . "\n";Output
第 1/2 页: Tom, AmyUnstable sorts (many equal keys) make rows appear on two pages or none. Always add a unique tiebreaker:
ORDER BY status, id.Performance
- Index the sort column —— otherwise MySQL uses
filesort - Match composite index order ——
(status, created_at)supports that ORDER BY - **
ORDER BY RAND()sorts the whole table** —— use a random ID offset instead - **Check
EXPLAINforUsing filesort** —— its presence means no index sort
PHP
<?php
// ORDER BY RAND() 的替代：先取随机起点
$total = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$offset = random_int(0, max(0, $total - 3));
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d, 3", $offset);
echo $sql, "\n";Output
SELECT name FROM users ORDER BY id LIMIT 0, 3基本排序
PHP
<?php
$stmt = $pdo->query("SELECT name, age FROM users ORDER BY age DESC");
foreach ($stmt->fetchAll() as $r) {
echo "{$r['name']} {$r['age']}\n";
}
echo "---\n";
$stmt = $pdo->query("SELECT name, age FROM users ORDER BY name ASC");
foreach ($stmt->fetchAll() as $r) {
echo $r["name"] . "\n";
}Output
Bob 28
Tom 30
Amy 25
---
Amy
Bob
Tom多列排序
PHP
<?php
$stmt = $pdo->query(
"SELECT name, status, age FROM users
ORDER BY status ASC, age DESC"
);
foreach ($stmt->fetchAll() as $r) {
echo "{$r['status']} {$r['name']} {$r['age']}\n";
}Output
1 Bob 28
1 Tom 30
2 Amy 25先按第一列排，第一列相同时再按第二列排。顺序就是优先级，写反了结果完全不同。
排序列必须白名单
ORDER BY ? 是语法错误——排序列是标识符，不能绑定。而直接拼接就是注入点。唯一安全的做法是白名单映射。
PHP
<?php
$allowed = [
"id" => "id",
"name" => "name",
"age" => "age",
"time" => "created_at",
];
$sort = $_GET["sort"] ?? "id";
$dir = strtolower($_GET["dir"] ?? "asc") === "desc" ? "DESC" : "ASC";
if (!isset($allowed[$sort])) {
$sort = "id";
}
$column = $allowed[$sort];
$sql = "SELECT name FROM users ORDER BY $column $dir";
echo $sql, "\n";Output
SELECT name FROM users ORDER BY created_at DESC白名单的价值在于：**用户输入的
sort 只用来查表，永远不会进入 SQL**。哪怕传 sort=id;DROP TABLE users，isset($allowed[...]) 也是 false，直接回退默认值。PHP
<?php
// 错误写法演示（不要用）
$bad = "id; DROP TABLE users";
$sql = "SELECT name FROM users ORDER BY $bad";
echo $sql, "\n";
// 白名单后
$allowed = ["id" => "id", "name" => "name"];
$col = $allowed[$bad] ?? "id";
echo "SELECT name FROM users ORDER BY $col", "\n";Output
SELECT name FROM users ORDER BY id; DROP TABLE users
SELECT name FROM users ORDER BY idNULL 的位置
MySQL 里 NULL 被当作最小值，ASC 时排最前，DESC 时排最后。想改变位置就显式排。
PHP
<?php
$sql1 = "SELECT name FROM users ORDER BY age ASC";
$sql2 = "SELECT name FROM users ORDER BY age IS NULL, age ASC";
echo $sql1, "\n";
echo $sql2, "\n";Output
SELECT name FROM users ORDER BY age ASC
SELECT name FROM users ORDER BY age IS NULL, age ASCage IS NULL 的结果是 0 或 1，先按它排就能把非 NULL 放前面。这是 MySQL 里的常用技巧，等价于 PostgreSQL 的 NULLS LAST。
排序 + 分页
PHP
<?php
$page = max(1, (int)($_GET["page"] ?? 1));
$size = 2;
$offset = ($page - 1) * $size;
$total = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$pages = (int)ceil($total / $size);
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d OFFSET %d", $size, $offset);
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);
echo "第 $page/$pages 页: " . implode(", ", $rows) . "\n";Output
第 1/2 页: Tom, AmyORDER BY 不稳定时（比如按 status 排而大量行 status 相同），分页会出现同一行在两页都出现或都不出现。分页排序一定要带上唯一列兜底：ORDER BY status, id。性能
- 排序列有索引才快 —— 否则 MySQL 要
Using filesort - 联合索引要匹配排序顺序 ——
(status, created_at)支持ORDER BY status, created_at - **
ORDER BY RAND()会全表排序** —— 数据量大时改用随机 ID 偏移 - **
EXPLAIN看Using filesort** —— 出现了就说明没走索引排序
PHP
<?php
// ORDER BY RAND() 的替代：先取随机起点
$total = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$offset = random_int(0, max(0, $total - 3));
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d, 3", $offset);
echo $sql, "\n";Output
SELECT name FROM users ORDER BY id LIMIT 0, 3