PDO 与 MySQL

这一课你将能做到

1. 用 PDO 正确连接 MySQL：异常模式、真实预处理、关联数组取回三件配置一步到位
2. 用预处理语句完成增删改查，从原理上理解它为什么能根治 SQL 注入
3. 用事务保证「下单扣库存」这类多步操作的原子性

为什么要学

业务数据最终都落在数据库，而 PHP 操作数据库的事实标准就是 PDO（PHP Data Objects）：一套统一接口支持 MySQL、PostgreSQL、SQLite 等，配合预处理语句把 SQL 注入这个二十年的经典漏洞从源头堵死。你的 Spring Boot 项目里 MyBatis 的 #{} 占位符，底层思想和 PDO 预处理完全一致——学这一课也是在反向巩固 JDBC 知识。

人话：PDO 是「数据库万能遥控器」，预处理是「先把 SQL 模板交给数据库审核，再把数据当纯数据递过去」——数据和指令永不混合，注入自然无从谈起。

先建立图像

拼接 SQL 与预处理 SQL 的本质区别：

拼接（危险）：
  "SELECT * FROM users WHERE name = '" + $input + "'"
  输入：' OR '1'='1
  数据库收到：SELECT * FROM users WHERE name = '' OR '1'='1'
                                    ↑ 数据变成了指令！

预处理（安全）：
  SQL 模板先发：SELECT * FROM users WHERE name = ?    → 数据库预编译
  数据后发：    [''' OR ''1''=''1']                   → 只当字面量
  数据库收到：  查找用户名恰好是 ' OR '1'='1 的人     → 查不到，安全

跟着做

步骤 1：规范建连

准备数据库与表（在 MySQL 客户端执行）：

CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE demo;
CREATE TABLE users (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(50)  NOT NULL,
    email    VARCHAR(100) NOT NULL UNIQUE,
    balance  DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

db.php——把正确姿势固化下来：

<?php
declare(strict_types=1);

$pdo = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8mb4',
    'root',
    'secret',
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // 出错抛异常
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // 关联数组取回
        PDO::ATTR_EMULATE_PREPARES   => false,                  // 用数据库真预处理
    ]
);

三个配置缺一不可：不开异常，错误静默；不设 FETCH_ASSOC，取回的数组带数字键；不关模拟预处理，某些场景（如 LIMIT 参数绑定）行为有差异且绕过了数据库端校验。

步骤 2：查询（占位符两种写法）

<?php
// 问号占位符：按位置传参
$stmt = $pdo->prepare('SELECT id, name, balance FROM users WHERE balance > ? ORDER BY balance DESC LIMIT ?');
$stmt->bindValue(1, 100.0, PDO::PARAM_STR);
$stmt->bindValue(2, 10, PDO::PARAM_INT);   // LIMIT 必须绑成 int
$stmt->execute();
$rich = $stmt->fetchAll();

// 命名占位符：可读性更好
$stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE :kw AND id > :minId');
$stmt->execute(['kw' => '%张%', 'minId' => 0]);   // LIKE 的 % 写在值里，不写进 SQL 模板
foreach ($stmt as $row) {
    echo $row['id'], ': ', $row['name'], ' 余额 ', $row['balance'], PHP_EOL;
}

// 单行查询与取单值
$user = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$user->execute([1]);
$row = $user->fetch();            // 没有时返回 false，判断用 === false 或 if ($row)
$count = (int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

步骤 3：写入与受影响行数

<?php
$stmt = $pdo->prepare(
    'INSERT INTO users (name, email, balance) VALUES (:name, :email, :balance)'
);
$stmt->execute([
    'name'    => '张三',
    'email'   => 'zhangsan@example.com',
    'balance' => 199.90,
]);
$newId = (int) $pdo->lastInsertId();      // 自增主键，对标 JDBC 的 getGeneratedKeys

$del = $pdo->prepare('DELETE FROM users WHERE id = ?');
$del->execute([999]);
echo $del->rowCount(), PHP_EOL;           // 受影响行数，0 表示没删到

lastInsertId() 和 rowCount() 是写入操作的两个高频搭档：前者拿新主键，后者判断操作是否命中。

步骤 4：事务保证原子性

<?php
function transfer(PDO $pdo, int $from, int $to, float $amount): void
{
    $pdo->beginTransaction();             // 开启事务
    try {
        $deduct = $pdo->prepare('UPDATE users SET balance = balance - ? WHERE id = ? AND balance >= ?');
        $deduct->execute([$amount, $from, $amount]);
        if ($deduct->rowCount() === 0) {
            // 余额不足时 UPDATE 影响行数为 0，主动失败
            throw new \RuntimeException('余额不足或账户不存在');
        }
        $pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')
            ->execute([$amount, $to]);

        $pdo->commit();                   // 全部成功才提交
        echo "转账成功\n";
    } catch (\Throwable $e) {
        $pdo->rollBack();                 // 任何一步失败整体回滚
        echo '转账失败：', $e->getMessage(), "\n";
    }
}

事务里任何一句抛异常都会走到 rollBack，数据库回到转账前状态——「扣了钱没加钱」的不一致不可能出现。

讲清楚要点

1. 预处理的防注入原理在数据库端：prepare 阶段数据库完成语法解析、生成执行计划，参数只以「数据槽位」存在；execute 阶段数据无论长什么样都进不了已编译的指令流。这与 JDBC PreparedStatement、MyBatis #{} 完全同源。
2. DSN 里指定 charset=utf8mb4：连接层字符集决定数据如何编解码，漏掉它，中文落库乱码且与页面编码无关。utf8mb4 才能存 emoji（MySQL 的 utf8 是三字节残缺版）。
3. fetchAll/fetch/column 按需取：大结果集用 fetch() 逐行处理或 fetchAll(PDO::FETCH_COLUMN) 单列；默认 FETCH_ASSOC 取回关联数组，FETCH_OBJ 可取 stdClass 对象。
4. 错误处理三档：ERRMODE_SILENT（默认，静默失败，最坑）、ERRMODE_WARNING、ERRMODE_EXCEPTION。生产统一 EXCEPTION，让 try/catch 接管——静默模式是无数「查不到数据却没报错」悬案的元凶。

你可能会懵的地方

• PDO 和 mysqli 选哪个？ 选 PDO。mysqli 只支持 MySQL，PDO 是统一抽象层，可换数据库；主流框架（Laravel 的 Eloquent 底层）也全部基于 PDO。mysqli 仅在维护遗留项目时接触。
• 预处理能不能提升查询性能？ 单请求场景基本不能——PHP 每次请求生命周期太短，预编译缓存无法跨请求复用（这点与 Java 常驻连接池不同）。它的核心价值是安全；性能收益（免重复解析）只在同一连接内多次执行同一 SQL 时体现。
• LIKE 模糊查询的 % 写在哪？ 写在参数值里：WHERE name LIKE :kw + execute(['kw' => "%{$kw}%"])。把 % 拼进 SQL 模板字符串再绑定是可以的（如 LIKE :kw 传 "%{$kw}%"），但绝不能把用户输入直接拼进模板本身。
• LIMIT、ORDER BY 字段能用占位符吗？ LIMIT/OFFSET 的数字可以绑定（注意 ATTR_EMULATE_PREPARES=false 时 LIMIT 参数需绑成 int，或直接 (int) 强转后内插数字）。ORDER BY 的列名/方向不能用占位符——它属于 SQL 结构，只能用白名单映射：$sortMap = ['price' => 'balance', 'time' => 'created_at']。
• 事务嵌套怎么办？ MySQL 不支持真嵌套事务。PDO 再调 beginTransaction 会抛异常；需要局部回滚用 SAVEPOINT（手工执行 SQL），或者把业务拆成不嵌套的单元。连接是事务的载体，别在事务中复用连接去做别的查询以外的操作。

错法对照

错法	问题	正解
"SELECT * FROM users WHERE id = " . $_GET['id']	SQL 注入 + 隐式转换	预处理绑定参数
忘设 PDO::ATTR_ERRMODE => ERRMODE_EXCEPTION	SQL 错误静默吞掉	建连即开异常模式
用 utf8 而不是 utf8mb4	emoji 落库报错/截断	DSN 与表结构统一 utf8mb4
WHERE name LIKE '%:kw%'	占位符不是字符串替换，查不出	LIKE :kw，% 放进参数值
复用同一命名占位符（AND a = :id OR b = :id）	真预处理下默认不可复用	分别命名 :idA、:idB
拿 rowCount() 判断 SELECT 行数	rowCount 只可靠用于写操作	SELECT 用 fetch 判空或 COUNT(*)

卡点急救

1. could not find driver：PDO 扩展或 MySQL 驱动没装。php.ini 里放开 extension=pdo_mysql（Docker 镜像里 docker-php-ext-install pdo_mysql）。
2. Access denied for user：账号、密码、host 三者有错；root@127.0.0.1 与 root@localhost 在 MySQL 里是不同账户，检查授权。
3. 中文乱码：逐层排查——连接 DSN charset=utf8mb4、表/列字符集 utf8mb4、页面与响应头 UTF-8。用 SHOW VARIABLES LIKE 'character%' 看实际连接编码。

面试常问

• 预处理语句为什么能防 SQL 注入？ prepare 阶段数据库对 SQL 模板完成解析、优化并缓存执行计划，占位符只是待填充的数据槽位；execute 阶段传入的值只会作为字面量数据绑定，无法改变已编译的语句结构。用户输入 ' OR '1'='1 只会被当成一个奇怪的用户名去匹配。
• PDO 与 mysqli 的区别？ PDO 是跨数据库的统一抽象（MySQL/PostgreSQL/SQLite…），API 一致，便于迁移，框架生态全部基于它；mysqli 仅服务 MySQL，性能差异可忽略但绑定单一厂商，且部分特性（命名占位符）不支持。
• 如何在 PHP 中保证一组 SQL 的原子性？ beginTransaction() 开启事务，所有语句执行成功后 commit()，任一异常进入 catch 执行 rollBack()；底层 InnoDB 保证 ACID。注意 PHP 每请求短连接的特性意味着事务无法跨请求，长事务要拆分，连接池需借助外部代理（如 ProxySQL）实现。

自测三问

1. ATTR_EMULATE_PREPARES => false 关闭的是什么机制？为什么要关？
2. lastInsertId() 与 rowCount() 分别用于什么场景？
3. 用户输入要参与 ORDER BY 排序字段时，安全的写法是什么？

小结一句话

PDO 三件配置（异常模式、关联取回、真预处理）+ 预处理绑定 + 事务，构成了 PHP 数据库操作的安全与正确性底线。

延伸

• PHP 官方手册：PDO
• 下一课：REST API 风格——把数据库操作包装成规范的 HTTP 接口。