PHP MySQL 读取数据
query vs prepare, the fetch family, and paged reads for large result sets.
Key Points`fetchAll` suits small result sets; `fetch` row by row for large ones.
Fetching rows
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY id");
$rows = $stmt->fetchAll();
foreach ($rows as $r) {
echo "{$r['id']} {$r['name']} {$r['email']}\n";
}
echo "共 " . count($rows) . " 行\n";Output
1 Tom tom@example.com
2 Amy amy@example.com
3 Bob bob@example.com
共 3 行Use query() for parameterless SQL and prepare() + execute() when parameters exist. Any user input requires the latter.
Fetch modes
| Mode | Result |
|---|---|
| FETCH_ASSOC | 关联数组，键是列名 |
| FETCH_NUM | 索引数组 |
| FETCH_BOTH | 两者都有（默认，浪费内存） |
| FETCH_OBJ | stdClass 对象 |
| FETCH_COLUMN | 单列一维数组 |
| FETCH_KEY_PAIR | 两列组成键值对 |
| FETCH_CLASS | 映射到自定义类 |
PHP
<?php
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
// 单列
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
// 键值对
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
print_r($stmt->fetchAll(PDO::FETCH_KEY_PAIR));
// 对象
$stmt = $pdo->query("SELECT id, name FROM users WHERE id = 1");
$o = $stmt->fetch(PDO::FETCH_OBJ);
echo $o->name, "\n";Output
Array
(
[0] => 1
[1] => 2
[2] => 3
)
Array
(
[1] => Tom
[2] => Amy
[3] => Bob
)
TomOne row, one value
PHP
<?php
// 取一行
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();
echo $user ? $user["name"] : "不存在", "\n";
// 取一个值
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE age > ?");
$stmt->execute([20]);
echo "人数: " . $stmt->fetchColumn() . "\n";
// 取不到时的默认值
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([999]);
echo $stmt->fetchColumn() ?: "无记录", "\n";Output
Tom
人数: 3
无记录fetch() returns false when empty; fetchAll() returns []. Test with if (!$row) or === [], not count().
Large result sets
PHP
<?php
// 不好：一次全读进内存
// $rows = $pdo->query("SELECT * FROM big_table")->fetchAll();
// 好：逐行处理，内存恒定
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
$sum = 0;
while (($row = $stmt->fetch()) !== false) {
$sum += (int)$row["id"];
}
echo "id 之和: $sum\n";Output
id 之和: 6MySQL buffers the whole result client-side by default.
fetchAll on 100k rows can eat hundreds of MB. fetch helps PHP but not MySQL buffering — true streaming needs MYSQL_ATTR_USE_BUFFERED_QUERY => false.Mapping to classes
PHP
<?php
class User
{
public int $id;
public string $name;
public function label(): string {
return "#{$this->id} {$this->name}";
}
}
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);
foreach ($users as $u) {
echo $u->label(), "\n";
}Output
#1 Tom
#2 Amy
#3 BobFETCH_CLASS creates the object then assigns properties, so the constructor does not run by default. Use FETCH_PROPS_LATE or initialise manually.
取数据
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY id");
$rows = $stmt->fetchAll();
foreach ($rows as $r) {
echo "{$r['id']} {$r['name']} {$r['email']}\n";
}
echo "共 " . count($rows) . " 行\n";Output
1 Tom tom@example.com
2 Amy amy@example.com
3 Bob bob@example.com
共 3 行没有参数的查询用 query()，有参数的用 prepare() + execute()。带了用户输入就必须用后者。
fetch 的几种模式
| 模式 | 结果 |
|---|---|
| FETCH_ASSOC | 关联数组，键是列名 |
| FETCH_NUM | 索引数组 |
| FETCH_BOTH | 两者都有（默认，浪费内存） |
| FETCH_OBJ | stdClass 对象 |
| FETCH_COLUMN | 单列一维数组 |
| FETCH_KEY_PAIR | 两列组成键值对 |
| FETCH_CLASS | 映射到自定义类 |
PHP
<?php
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
// 单列
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
// 键值对
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
print_r($stmt->fetchAll(PDO::FETCH_KEY_PAIR));
// 对象
$stmt = $pdo->query("SELECT id, name FROM users WHERE id = 1");
$o = $stmt->fetch(PDO::FETCH_OBJ);
echo $o->name, "\n";Output
Array
(
[0] => 1
[1] => 2
[2] => 3
)
Array
(
[1] => Tom
[2] => Amy
[3] => Bob
)
Tom单行与单值
PHP
<?php
// 取一行
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();
echo $user ? $user["name"] : "不存在", "\n";
// 取一个值
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE age > ?");
$stmt->execute([20]);
echo "人数: " . $stmt->fetchColumn() . "\n";
// 取不到时的默认值
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([999]);
echo $stmt->fetchColumn() ?: "无记录", "\n";Output
Tom
人数: 3
无记录fetch() 没数据时返回 false，fetchAll() 返回空数组 []。判断「有没有」用 if (!$row) 或 if ($rows === [])，别用 count()。
大结果集
PHP
<?php
// 不好：一次全读进内存
// $rows = $pdo->query("SELECT * FROM big_table")->fetchAll();
// 好：逐行处理，内存恒定
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
$sum = 0;
while (($row = $stmt->fetch()) !== false) {
$sum += (int)$row["id"];
}
echo "id 之和: $sum\n";Output
id 之和: 6MySQL 默认把整个结果集缓冲在客户端内存里。
fetchAll 十万行能吃掉几百 MB。用 fetch 逐行取只能缓解 PHP 侧，MySQL 侧仍会缓冲——真正的流式要设 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false。映射到类
PHP
<?php
class User
{
public int $id;
public string $name;
public function label(): string {
return "#{$this->id} {$this->name}";
}
}
$stmt = $pdo->query("SELECT id, name FROM users ORDER BY id");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);
foreach ($users as $u) {
echo $u->label(), "\n";
}Output
#1 Tom
#2 Amy
#3 BobFETCH_CLASS 会先创建对象再赋属性，所以构造函数不会自动跑。需要在构造后初始化就用 FETCH_PROPS_LATE 或者手动处理。