Chapter 4: Database

1 PDO Introduction

PDO (PHP Data Objects) is a unified database access abstraction layer provided by PHP. It is not a database abstraction — it doesn't rewrite SQL, but provides a consistent API to access different database systems. Whether the underlying database is MySQL, PostgreSQL, or SQLite, your PHP code barely needs to change.

Why Choose PDO Over mysqli?

2 Connecting to Database

DSN (Data Source Name) Format

The DSN specifies the type and location of the database to connect to, in the format driver:parameters:

// 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:';

Creating a Connection (Recommended Configuration)

$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('服务暂时不可用');
}

Encapsulate as a Reusable Connection Class

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 Operations

All examples below use prepared statements (detailed in the next section), which is the only correct way to interact with a database.

Table Creation 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 — Inserting Data

$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}";

Batch Insert

$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 — Querying Data

// 查询单条记录
$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 — Updating Data

$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 — Deleting Data

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

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

4 Prepared Statements

Prepared Statements are the core mechanism for preventing SQL injection. The principle is to separate SQL structure from data — the database first compiles the SQL template, then receives the parameters separately, so parameters are never executed as SQL code.

❌ Wrong Approach — String Concatenation

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

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

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

✅ Correct Approach — Prepared Statements

// 方式一：命名占位符（推荐，可读性好）
$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 vs 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 Queries

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

Correct Way to Handle IN Queries

$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 Transaction Management

Transactions bundle multiple SQL operations into an atomic unit — either all succeed or all are rolled back. PDO manages transactions through three methods:

• $pdo->beginTransaction() — Start a transaction
• $pdo->commit() — Commit the transaction
• $pdo->rollBack() — Roll back the transaction

ACID Properties

Classic Example: Money Transfer

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 Query Builder Pattern

Frequently writing SQL by hand is error-prone and highly repetitive. You can encapsulate a lightweight query builder that assembles SQL through method chaining while maintaining the security of prepared statements.

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();
    }
}

Method Chaining Examples

$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 Quick Start

SQLite is an embedded database that requires no server installation, storing data in a single file. It's ideal for prototyping, CLI tools, small applications, and unit testing. Since PDO is used, switching to SQLite requires changing only one line — the 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');

Complete Example: Notes App

$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 Chapter Summary