PHP 语法进阶 15｜PDO 与数据库：预处理语句是唯一的正确写法

为什么用 PDO，不用 `mysqli_` 或 `mysql_`

mysql_*（mysql_query 那一套）在 PHP 7.0 已被彻底删除。看到这类代码说明是十年以上的老项目。
mysqli 只支持 MySQL，切库时要重写。
PDO 支持十几种数据库，接口统一，而且预处理是它的核心能力，用起来比 mysqli 的绑定清晰得多。

代价是 PDO 不支持 MySQL 的部分专有特性（如 mysqli 的异步查询、某些预处理细节）。但对 99% 的业务来说，PDO 够用且更好维护。

连接

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4";

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // 失败抛异常，别用静默模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // 默认返回关联数组
    PDO::ATTR_EMULATE_PREPARES   => false,                   // 用真正的服务端预处理
    PDO::ATTR_STRINGIFY_FETCHES  => false,                   // 数字列返回 int/float
]);

四个选项逐个说：

ERRMODE_EXCEPTION 是必须的。默认的 ERRMODE_SILENT 会让失败"静默"，你得每次检查 errorCode()，几乎没人做到，结果是"SQL 写错了但页面正常，数据没入库"。

DEFAULT_FETCH_MODE => FETCH_ASSOC 省掉每次 fetch(PDO::FETCH_ASSOC) 的重复。注意不能用 FETCH_BOTH（同时给数字键和字符串键，内存翻倍）。

ATTR_EMULATE_PREPARES => false 让 MySQL 走真正的服务端预处理，而不是 PDO 在客户端拼 SQL。后者在极端情况下仍可能被注入（尤其是配合不当的字符集），真实预处理更安全。

ATTR_STRINGIFY_FETCHES => false 让 id 返回 int 而不是 "1"。这个非常影响后续类型判断：

<?php
// 没关 STRINGIFY 时
var_dump($row['id']);            // string(1) "1"
// 关了之后
var_dump($row['id']);            // int(1)

一定要在 DSN 里写 `charset`

<?php
// ✗ 不要连接后再执行 SET NAMES
$pdo = new PDO("mysql:host=x;dbname=y", $u, $p);
$pdo->exec("SET NAMES utf8mb4");

// ✓ 写在 DSN 里
$pdo = new PDO("mysql:host=x;dbname=y;charset=utf8mb4", $u, $p);

SET NAMES 在连接建立之后才生效，中间那段窗口里如果执行了查询，字符集是不对的。DSN 里的 charset 会在握手阶段就设好。

用 utf8mb4 而不是 utf8。MySQL 的 utf8 其实是 3 字节的残缺实现，存 emoji 会报错或被截断。

预处理：防注入的唯一正解

先看错误写法，这是 PHP 历史事故的头号来源：

<?php
// ✗ 拼接
$id = $_GET['id'];
$pdo->query("SELECT * FROM users WHERE id = {$id}");

// ✗ 转义也不够（因为还要考虑字符集、数字型注入等）
$name = $pdo->quote($_GET['name']);
$pdo->query("SELECT * FROM t WHERE name = {$name}");

正确写法：

<?php
// 命名占位符
$st = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$st->execute([':id' => $id]);          // 冒号可省，写 ['id' => $id]

// 问号占位符
$st = $pdo->prepare("SELECT * FROM users WHERE id = ? AND status = ?");
$st->execute([$id, 'active']);

预处理的本质是"SQL 结构和数据分离"：SQL 先编译，数据后绑定，数据永远不会被当成 SQL 语法解析。所以它不只防 ' OR 1=1，也防数字型注入、LIKE 注入、编码绕过——全部免疫。

三条使用纪律：

占位符不能和字符串拼接混用，"WHERE name LIKE '%:kw%'" 是错的（占位符在引号里就不算占位符了）。
? 和 :name 不能在同一条语句里混用。
能用占位符的地方全都用，包括 LIMIT、OFFSET：

<?php
$st = $pdo->prepare("SELECT * FROM users ORDER BY id LIMIT :lim OFFSET :off");
$st->bindValue(':lim', $limit, PDO::PARAM_INT);
$st->bindValue(':off', $offset, PDO::PARAM_INT);
$st->execute();

注意 LIMIT 必须 bindValue + PDO::PARAM_INT，不能用 execute([...]) 传数字——因为 execute 的数组会把所有值当字符串绑，MySQL 收到 LIMIT '10' 会报语法错误。

正确的 LIKE 写法：

<?php
$kw = '%' . str_replace(['%', '_'], ['\%', '\_'], $keyword) . '%';
$st = $pdo->prepare("SELECT * FROM articles WHERE title LIKE :kw");
$st->execute([':kw' => $kw]);

通配符要自己拼进值里，不能拼进 SQL 里。同时要对 % _ 做转义（否则用户输入 % 就变成模糊匹配全部）。

取值：五种常用方式

<?php
// 1) 一行
$row = $st->fetch();                    // 没有更多行时返回 false
// 2) 全部
$rows = $st->fetchAll();
// 3) 只取一列
$count = $st->fetchColumn();            // 第一个字段
// 4) 直接映射成对象
$user = $st->fetchObject(User::class);
// 5) 用生成器避免大结果集占内存（见第 12 篇）
while ($row = $st->fetch()) { process($row); }

fetchAll() 会把整个结果集读进内存。查一万行、每行十几个字段，几百 MB 就没了。大数据量一律用 while ($row = $st->fetch())：

<?php
$st = $pdo->query("SELECT * FROM logs");
while ($row = $st->fetch()) {      // 每次只取一行
    processLog($row);
}

注意这是PHP 层的逐行取，MySQL 客户端库默认仍然会把整个结果缓冲到本地。真要流式，MySQL 驱动要设：

<?php
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

关掉缓冲后，遍历期间不能在同一连接上执行其他查询，否则报 "Cannot execute queries while other unbuffered queries are active"。要么遍历完再查，要么用另一个连接。

事务

<?php
try {
    $pdo->beginTransaction();

    $st = $pdo->prepare("UPDATE account SET balance = balance - :amt WHERE id = :id");
    $st->execute([':amt' => 100, ':id' => 1]);

    $st->execute([':amt' => 100, ':id' => 2]);   // 注意这里语义不对，仅示范

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;                       // 别吞掉异常
}

三条纪律：

beginTransaction 之后必须保证有一个 commit 或 rollBack。漏掉会让连接一直挂着未提交事务（尤其在长连接 / 连接池里，后续请求全受影响）。
异常要重新抛出。catch 里只 rollBack 不 throw，调用方以为成功了。
inTransaction() 判断一下再 rollback，避免"事务已因错误自动回滚，再 rollback 报错"。

隔离级别与死锁

<?php
$pdo->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

MySQL 默认是 REPEATABLE READ。做"先查库存再扣减"这类逻辑，要么用 SELECT ... FOR UPDATE 加行锁，要么用乐观锁（版本号 / WHERE stock >= n 条件更新）：

<?php
// 乐观锁：靠一条 UPDATE 的受影响行数判断成败，不需要事务隔离
$st = $pdo->prepare("UPDATE stock SET qty = qty - :n WHERE sku = :sku AND qty >= :n");
$st->execute([':n' => $num, ':sku' => $sku]);
if ($st->rowCount() === 0) {
    throw new RuntimeException("库存不足");
}

这个写法比"查-判断-更新"安全得多，而且不需要显式事务。

`lastInsertId` 与 `rowCount`

<?php
$pdo->exec("INSERT INTO t (name) VALUES ('x')");
echo $pdo->lastInsertId();       // 自增 id

lastInsertId() 只对自增主键有效，且在连接级别有效——如果用连接池或长连接，一定要"插入后立刻取"，中间别执行别的插入。

rowCount() 的语义要看驱动：MySQL 上 UPDATE 返回的是"真正被改的行数"（值没变的行不算）。如果业务需要"匹配了多少行"，要么 SQL 里加 SQL_CALC_FOUND_ROWS（已弃用），要么查一次再改。

<?php
$st->execute();
if ($st->rowCount() === 0) {
    // 可能是"没这行"，也可能是"值没变化"，两者在这里区分不了
}

批量插入的正确姿势

<?php
// ✗ 循环单条插入，一万次 = 一万次网络往返
foreach ($rows as $r) {
    $st->execute($r);
}

// ✓ 多值插入，分批
$chunks = array_chunk($rows, 500);
foreach ($chunks as $chunk) {
    $placeholders = implode(',', array_fill(0, count($chunk), '(?,?,?)'));
    $st = $pdo->prepare("INSERT INTO t (a,b,c) VALUES {$placeholders}");
    $st->execute(array_merge(...$chunk));
}

注意 $placeholders 是拼出来的，但拼的是固定数量的 ?，不含任何用户数据——这是安全的。分批 500 条左右通常是最优区间。

连接管理

<?php
// 赋值 null 立即关闭
$pdo = null;

ATTR_PERSISTENT => true（长连接）要慎用。 在 PHP-FPM 下它能省掉握手开销，但也带来两个问题：

连接里的未提交事务、临时表、SET 变量、锁会残留到下个请求。
连接数不容易控制，容易打满数据库的 max_connections。

真要复用连接，用连接池方案（如 ProxySQL、PgBouncer），别靠 PDO 的长连接。

一个完整的仓储类

<?php
final class UserRepository
{
    public function __construct(private PDO $pdo) {}

    public function find(int $id): ?array
    {
        $st = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $st->execute([$id]);
        return $st->fetch() ?: null;      // fetch 失败返回 false
    }

    public function create(string $name, string $email): int
    {
        $st = $this->pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
        $st->execute([$name, $email]);
        return (int)$this->pdo->lastInsertId();
    }

    /** @return array<int, array> */
    public function all(int $limit = 100, int $offset = 0): array
    {
        $st = $this->pdo->prepare("SELECT * FROM users ORDER BY id LIMIT :l OFFSET :o");
        $st->bindValue(':l', $limit, PDO::PARAM_INT);
        $st->bindValue(':o', $offset, PDO::PARAM_INT);
        $st->execute();
        return $st->fetchAll();
    }
}

坑清单

拼接 SQL，不预处理——最严重的注入入口。
ATTR_ERRMODE 用默认静默，SQL 出错没人知道。
DSN 里漏了 charset=utf8mb4，或用 utf8 存不了 emoji。
LIMIT 用 execute([...]) 绑数字，MySQL 报语法错误，必须 bindValue + PARAM_INT。
LIKE 的通配符拼进 SQL 而不是值里，或者没转义 % _。
fetchAll() 取大结果集，内存爆掉。
事务漏了 commit/rollBack，连接挂死。
catch 里 rollback 后不重新抛异常，上层误判成功。
rowCount() 在 UPDATE 上返回"实际改变的行数"，不是"匹配的行数"。
长连接残留事务，污染下一个请求。