第四章：数据库

1 PDO 简介

PDO（PHP Data Objects）是 PHP 提供的统一数据库访问抽象层。它不是数据库抽象——不会重写 SQL，而是提供一致的 API 来访问不同的数据库系统。无论底层是 MySQL、PostgreSQL 还是 SQLite，你的 PHP 代码几乎不需要改动。

为什么选择 PDO 而不是 mysqli？

2 连接数据库

DSN（数据源名称）格式

DSN 指定了要连接的数据库类型和位置，格式为 驱动名:参数：

// MySQL DSN
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=myapp;charset=utf8mb4';

// PostgreSQL DSN
$dsn = 'pgsql:host=127.0.0.1;port=5432;dbname=myapp';

// SQLite DSN（文件数据库，无需用户名密码）
$dsn = 'sqlite:/path/to/database.db';

// SQLite 内存数据库
$dsn = 'sqlite::memory:';

创建连接（推荐配置）

$dsn  = 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4';
$user = 'root';
$pass = 'secret';

$options = [
    // 出错时抛出异常，而不是静默失败
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // fetch 默认返回关联数组，而非数字+关联混合
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // 关闭模拟预处理，使用数据库原生预处理（更安全）
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    // 生产环境不要暴露详细错误信息
    error_log('数据库连接失败: ' . $e->getMessage());
    exit('服务暂时不可用');
}

封装为可复用的连接类

class Database
{
    private static ?PDO $instance = null;

    public static function connect(): PDO
    {
        if (self::$instance === null) {
            $config = require __DIR__ . '/config/database.php';
            self::$instance = new PDO(
                $config['dsn'],
                $config['username'],
                $config['password'],
                [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                ]
            );
        }
        return self::$instance;
    }
}

// config/database.php
return [
    'dsn'      => 'mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4',
    'username' => 'root',
    'password' => 'secret',
];

// 任何地方使用
$pdo = Database::connect();
$users = $pdo->query('SELECT * FROM users')->fetchAll();

3 CRUD 操作

以下示例均使用预处理语句（详见下一节），这是操作数据库的唯一正确姿势。

建表 SQL

CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(200) NOT NULL UNIQUE,
    age        INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT — 插入数据

$stmt = $pdo->prepare('INSERT INTO users (name, email, age) VALUES (:name, :email, :age)');

$stmt->execute([
    ':name'  => '张三',
    ':email' => 'zhangsan@example.com',
    ':age'   => 28,
]);

// 获取刚插入的自增 ID
$newId = $pdo->lastInsertId();
echo "新用户 ID: {$newId}";

批量插入

$users = [
    ['name' => '李四', 'email' => 'lisi@example.com', 'age' => 32],
    ['name' => '王五', 'email' => 'wangwu@example.com', 'age' => 25],
    ['name' => '赵六', 'email' => 'zhaoliu@example.com', 'age' => 30],
];

$stmt = $pdo->prepare('INSERT INTO users (name, email, age) VALUES (:name, :email, :age)');

foreach ($users as $user) {
    $stmt->execute($user);
}
// prepare 一次，execute 多次——高效且安全

SELECT — 查询数据

// 查询单条记录
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute([':id' => 1]);
$user = $stmt->fetch();  // 返回一行（关联数组）或 false

if ($user) {
    echo "姓名: {$user['name']}, 邮箱: {$user['email']}";
}

// 查询多条记录
$stmt = $pdo->prepare('SELECT * FROM users WHERE age >= :min_age ORDER BY created_at DESC');
$stmt->execute([':min_age' => 25]);
$users = $stmt->fetchAll();  // 返回所有行的数组

foreach ($users as $user) {
    echo "{$user['name']} ({$user['age']}岁)\n";
}

// 只取一列
$stmt = $pdo->prepare('SELECT COUNT(*) FROM users WHERE age >= :min_age');
$stmt->execute([':min_age' => 25]);
$count = $stmt->fetchColumn();  // 返回第一行的第一列
echo "符合条件: {$count} 人";

UPDATE — 更新数据

$stmt = $pdo->prepare('UPDATE users SET name = :name, age = :age WHERE id = :id');
$stmt->execute([
    ':name' => '张三丰',
    ':age'  => 29,
    ':id'   => 1,
]);

// 获取受影响的行数
$affected = $stmt->rowCount();
echo "更新了 {$affected} 条记录";

DELETE — 删除数据

$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute([':id' => 3]);

echo "删除了 {$stmt->rowCount()} 条记录";

4 预处理语句

预处理语句（Prepared Statements）是防止 SQL 注入的核心机制。其原理是将 SQL 结构与数据分离——数据库先编译 SQL 模板，再单独接收参数，参数永远不会被当作 SQL 代码执行。

❌ 错误做法 — 字符串拼接

// ⚠️ 极度危险！用户可以输入恶意内容
$name = $_GET['name'];

// 如果 name = "'; DROP TABLE users; --" → 灾难
$pdo->query("SELECT * FROM users WHERE name = '$name'");

// 即使用了 addslashes / htmlspecialchars 也不安全！
// 唯一正确的方案是预处理语句

✅ 正确做法 — 预处理语句

// 方式一：命名占位符（推荐，可读性好）
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name AND age > :age');
$stmt->execute([':name' => $_GET['name'], ':age' => 18]);
$users = $stmt->fetchAll();

// 方式二：位置占位符
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = ? AND age > ?');
$stmt->execute([$_GET['name'], 18]);
$users = $stmt->fetchAll();

bindValue 与 bindParam

$stmt = $pdo->prepare('SELECT * FROM users WHERE age > :age LIMIT :limit');

// bindValue: 立即绑定值（推荐）
$stmt->bindValue(':age', 18, PDO::PARAM_INT);
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->execute();

// bindParam: 绑定变量引用（值在 execute 时才读取）
$age = 18;
$stmt->bindParam(':age', $age, PDO::PARAM_INT);
$age = 25;       // 改变变量
$stmt->execute(); // 实际绑定的是 25

LIKE 查询

// LIKE 模糊查询：通配符写在参数里，不要写在 SQL 里
$keyword = '%' . $_GET['q'] . '%';
$stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE :keyword');
$stmt->execute([':keyword' => $keyword]);

IN 查询的正确写法

$ids = [1, 3, 5, 7];

// 动态生成占位符
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($placeholders)");
$stmt->execute($ids);

5 事务管理

事务将多条 SQL 操作打包为一个原子单元——要么全部成功，要么全部回滚。PDO 通过三个方法管理事务：

• $pdo->beginTransaction() — 开始事务
• $pdo->commit() — 提交事务
• $pdo->rollBack() — 回滚事务

ACID 特性

经典示例：转账

function transfer(PDO $pdo, int $fromId, int $toId, float $amount): void
{
    try {
        $pdo->beginTransaction();

        // 扣除转出方余额
        $stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount');
        $stmt->execute([':amount' => $amount, ':id' => $fromId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException('余额不足或账户不存在');
        }

        // 增加转入方余额
        $stmt = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :id');
        $stmt->execute([':amount' => $amount, ':id' => $toId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException('目标账户不存在');
        }

        // 记录转账流水
        $stmt = $pdo->prepare(
            'INSERT INTO transactions (from_id, to_id, amount, created_at) VALUES (:from, :to, :amount, NOW())'
        );
        $stmt->execute([':from' => $fromId, ':to' => $toId, ':amount' => $amount]);

        $pdo->commit();
        echo "转账成功: {$fromId} → {$toId}，金额 {$amount}";

    } catch (Throwable $e) {
        // 出现任何错误都回滚
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        error_log('转账失败: ' . $e->getMessage());
        throw $e;
    }
}

// 使用
transfer($pdo, fromId: 1, toId: 2, amount: 500.00);

6 查询构建器模式

频繁手写 SQL 容易出错且重复度高。可以封装一个轻量级查询构建器，用链式调用来组装 SQL，同时保持预处理的安全性。

class QueryBuilder
{
    private PDO $pdo;
    private string $table = '';
    private array $conditions = [];
    private array $bindings = [];
    private array $columns = ['*'];
    private ?string $orderBy = null;
    private ?int $limit = null;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function table(string $table): static
    {
        $this->table = $table;
        return $this;
    }

    public function select(string ...$columns): static
    {
        $this->columns = $columns;
        return $this;
    }

    public function where(string $column, string $operator, mixed $value): static
    {
        $placeholder = ':w' . count($this->conditions);
        $this->conditions[] = "{$column} {$operator} {$placeholder}";
        $this->bindings[$placeholder] = $value;
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): static
    {
        $this->orderBy = "{$column} {$direction}";
        return $this;
    }

    public function limit(int $limit): static
    {
        $this->limit = $limit;
        return $this;
    }

    public function get(): array
    {
        $sql = 'SELECT ' . implode(', ', $this->columns) . " FROM {$this->table}";

        if ($this->conditions) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        if ($this->orderBy) {
            $sql .= " ORDER BY {$this->orderBy}";
        }
        if ($this->limit !== null) {
            $sql .= " LIMIT {$this->limit}";
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        return $stmt->fetchAll();
    }

    public function first(): ?array
    {
        $this->limit = 1;
        $results = $this->get();
        return $results[0] ?? null;
    }

    public function insert(array $data): int
    {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_map(fn($k) => ":{$k}", array_keys($data)));
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        return (int) $this->pdo->lastInsertId();
    }

    public function update(array $data): int
    {
        $setParts = array_map(fn($k) => "{$k} = :set_{$k}", array_keys($data));
        $sql = "UPDATE {$this->table} SET " . implode(', ', $setParts);

        $bindings = [];
        foreach ($data as $k => $v) {
            $bindings[":set_{$k}"] = $v;
        }

        if ($this->conditions) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
            $bindings = array_merge($bindings, $this->bindings);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($bindings);
        return $stmt->rowCount();
    }

    public function delete(): int
    {
        $sql = "DELETE FROM {$this->table}";
        if ($this->conditions) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        return $stmt->rowCount();
    }
}

链式调用示例

$db = new QueryBuilder($pdo);

// SELECT
$users = $db->table('users')
    ->select('id', 'name', 'email')
    ->where('age', '>=', 18)
    ->where('name', 'LIKE', '%张%')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

// INSERT
$id = $db->table('users')->insert([
    'name'  => '新用户',
    'email' => 'new@example.com',
    'age'   => 22,
]);

// UPDATE
$db->table('users')
    ->where('id', '=', $id)
    ->update(['name' => '已更新用户']);

// DELETE
$db->table('users')
    ->where('id', '=', $id)
    ->delete();

7 SQLite 快速上手

SQLite 是嵌入式数据库，无需安装服务器，数据存储在单个文件中。非常适合原型开发、CLI 工具、小型应用和单元测试。由于使用 PDO，切换到 SQLite 几乎只需改一行 DSN。

// 文件数据库
$pdo = new PDO('sqlite:app.db', options: [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// 内存数据库（进程结束即销毁，适合测试）
$pdo = new PDO('sqlite::memory:');

// 启用 WAL 模式提升并发性能
$pdo->exec('PRAGMA journal_mode=WAL');
// 启用外键约束（SQLite 默认关闭）
$pdo->exec('PRAGMA foreign_keys=ON');

完整示例：笔记应用

$pdo = new PDO('sqlite:notes.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// 建表（IF NOT EXISTS 保证幂等）
$pdo->exec('
    CREATE TABLE IF NOT EXISTS notes (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        title      TEXT NOT NULL,
        content    TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
');

// 添加笔记
$stmt = $pdo->prepare('INSERT INTO notes (title, content) VALUES (:title, :content)');
$stmt->execute([':title' => '学习 PDO', ':content' => 'PDO 是 PHP 的数据库抽象层...']);

// 查询所有笔记
$notes = $pdo->query('SELECT * FROM notes ORDER BY created_at DESC')->fetchAll();

foreach ($notes as $note) {
    echo "[{$note['id']}] {$note['title']} ({$note['created_at']})\n";
}

8 本章要点