PHP小码农

Appearance

数据库交互 ​

PHP 与数据库的交互是 Web 开发中最核心的功能之一。PHP 通过 PDO（PHP Data Objects）扩展提供了一个统一的数据库访问接口，支持多种数据库系统（MySQL、PostgreSQL、SQLite、SQL Server 等）。本节将全面介绍如何使用 PDO 连接数据库、执行查询、获取数据、防止 SQL 注入以及错误处理，并给出完整的 CRUD 示例。

前置知识

阅读本节前，你需要：

  • 已阅读过 基础交互脚本 和 表单处理 两节
  • 了解基本的 SQL 语法（SELECT、INSERT、UPDATE、DELETE）
  • 了解数据库的基本概念（表、行、列、主键）
  • 已安装 PHP 并启用了 PDO 扩展（extension=pdo_mysql）

基础概念 ​

什么是 PDO ​

PDO（PHP Data Objects）是 PHP 提供的数据库抽象层，它定义了一个统一的接口来访问多种数据库。PDO 的主要优势包括：

  • 数据库无关性：使用相同的 API 访问不同的数据库，切换数据库只需修改连接字符串（DSN）
  • 预处理语句：原生支持预处理语句，有效防止 SQL 注入攻击
  • 面向对象接口：使用 OOP 风格的 API，更符合现代 PHP 的编码习惯
  • 错误处理：支持异常模式，便于错误捕获和处理
  • 高性能：使用本地驱动，性能优于 ADODB、MDB2 等抽象层

PDO 支持的数据库驱动 ​

驱动名称数据库DSN 示例
pdo_mysqlMySQL/MariaDBmysql:host=localhost;dbname=test
pdo_pgsqlPostgreSQLpgsql:host=localhost;dbname=test
pdo_sqliteSQLitesqlite:/path/to/database.sqlite
pdo_sqlsrvSQL Serversqlsrv:Server=localhost;Database=test
pdo_ociOracleoci:dbname=localhost/XE

检查可用的 PDO 驱动

使用 PDO::getAvailableDrivers() 可以查看当前 PHP 环境中可用的 PDO 驱动。本节所有示例使用 MySQL 数据库。

PDO 连接数据库 ​

创建数据库连接 ​

使用 PDO 连接数据库需要构建一个 DSN（Data Source Name）字符串，并指定用户名和密码：

php
<?php
declare(strict_types=1);

// database-connection.php — PDO 连接数据库示例

// 数据库配置
$dbConfig = [
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'php_tutorial',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
];

// 构建 DSN
$dsn = sprintf(
    'mysql:host=%s;port=%d;dbname=%s;charset=%s',
    $dbConfig['host'],
    $dbConfig['port'],
    $dbConfig['database'],
    $dbConfig['charset']
);

// PDO 连接选项
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,   // 异常模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // 默认关联数组
    PDO::ATTR_EMULATE_PREPARES   => false,                    // 禁用模拟预处理
    PDO::ATTR_PERSISTENT         => false,                    // 非持久连接（推荐）
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'",    // 设置字符集
];

try {
    $pdo = new PDO($dsn, $dbConfig['username'], $dbConfig['password'], $options);
    echo "数据库连接成功\n";
    echo "PDO 驱动版本: " . $pdo->getAttribute(PDO::ATTR_SERVER_VERSION) . "\n";
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage() . "\n");
}

连接管理最佳实践 ​

在实际项目中，推荐使用单例模式或依赖注入来管理数据库连接：

php
<?php
declare(strict_types=1);

/**
 * 数据库连接管理类
 * 使用单例模式确保全局只有一个数据库连接
 */
class Database
{
    private static ?self $instance = null;
    private ?PDO $connection = null;

    private function __construct(
        private readonly string $host = '127.0.0.1',
        private readonly int $port = 3306,
        private readonly string $database = 'php_tutorial',
        private readonly string $username = 'root',
        private readonly string $password = '',
        private readonly string $charset = 'utf8mb4'
    ) {
        $this->connect();
    }

    public static function getInstance(
        string $host = '127.0.0.1',
        int $port = 3306,
        string $database = 'php_tutorial',
        string $username = 'root',
        string $password = ''
    ): self {
        if (self::$instance === null) {
            self::$instance = new self($host, $port, $database, $username, $password);
        }
        return self::$instance;
    }

    private function connect(): void
    {
        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            $this->host,
            $this->port,
            $this->database,
            $this->charset
        );

        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'",
        ];

        $this->connection = new PDO($dsn, $this->username, $this->password, $options);
    }

    public function getConnection(): PDO
    {
        return $this->connection;
    }

    /**
     * 执行查询并返回所有结果
     */
    public function query(string $sql, array $params = []): array
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * 执行查询并返回单行结果
     */
    public function queryOne(string $sql, array $params = []): ?array
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    /**
     * 执行语句（INSERT/UPDATE/DELETE）
     */
    public function execute(string $sql, array $params = []): int
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
}

执行查询 ​

使用 query() 直接执行 ​

对于没有用户输入的简单查询，可以使用 query() 方法直接执行：

php
<?php
declare(strict_types=1);

// 创建示例表
$sql = "CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `age` TINYINT UNSIGNED DEFAULT NULL,
    `bio` TEXT DEFAULT NULL,
    `status` ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$pdo->exec($sql);
echo "表 users 创建成功\n";

// 使用 query() 执行 SELECT
$users = $pdo->query("SELECT * FROM users WHERE status = 'active' ORDER BY id DESC LIMIT 10");
foreach ($users as $user) {
    echo "ID: {$user['id']}, 用户名: {$user['username']}, 邮箱: {$user['email']}\n";
}

使用 prepare() 预处理语句 ​

预处理语句是 PDO 最重要、最推荐的使用方式。它通过参数绑定的方式防止 SQL 注入：

php
<?php
declare(strict_types=1);

// ========== 预处理语句 — 位置参数 ==========
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND status = ?");
$stmt->execute([1, 'active']);
$user = $stmt->fetch();
// ? 占位符按位置绑定

// ========== 预处理语句 — 命名参数（推荐） ==========
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id AND status = :status");
$stmt->execute([
    ':id' => 1,
    ':status' => 'active',
]);
$user = $stmt->fetch();

// ========== bindValue vs bindParam ==========
// bindValue — 绑定值（推荐，更安全）
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->bindValue(':email', 'zhangsan@example.com', PDO::PARAM_STR);
$stmt->execute();
$user = $stmt->fetch();

// bindParam — 绑定引用（用于存储过程和特殊场景）
$stmt = $pdo->prepare("SELECT COUNT(*) as total FROM users WHERE age > :minAge");
$minAge = 18;
$stmt->bindParam(':minAge', $minAge, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch();
echo "18 岁以上用户数: {$result['total']}\n";

// 注意：bindParam 绑定的是变量引用，不是变量的值
// 如果在 execute() 之前改变了变量的值，使用的是新值

禁用模拟预处理

始终设置 PDO::ATTR_EMULATE_PREPARES => false，确保预处理语句在服务器端执行，而不是在 PHP 端模拟。这是防止 SQL 注入的重要防线。

获取数据 ​

fetch() 获取单行数据 ​

fetch() 方法每次从结果集中获取一行数据，支持多种获取模式：

php
<?php
declare(strict_types=1);

// 获取模式设置
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// PDO::FETCH_ASSOC — 关联数组（最常用）
$stmt = $pdo->query("SELECT id, username, email FROM users LIMIT 1");
$user = $stmt->fetch(PDO::FETCH_ASSOC);
// 结果：['id' => 1, 'username' => '张三', 'email' => 'zhangsan@example.com']

// PDO::FETCH_NUM — 数字索引数组
$user = $stmt->fetch(PDO::FETCH_NUM);
// 结果：[0 => 1, 1 => '张三', 2 => 'zhangsan@example.com']

// PDO::FETCH_BOTH — 同时包含关联和数字索引（默认）
$user = $stmt->fetch(PDO::FETCH_BOTH);

// PDO::FETCH_OBJ — 匿名对象
$user = $stmt->fetch(PDO::FETCH_OBJ);
// 结果：object { id: 1, username: '张三', email: 'zhangsan@example.com' }
echo "用户名: " . $user->username . "\n";

// PDO::FETCH_CLASS — 映射到指定类
class UserDTO
{
    public function __construct(
        public readonly ?int $id = null,
        public readonly ?string $username = null,
        public readonly ?string $email = null,
        public readonly ?string $status = null,
    ) {
    }
}

$stmt = $pdo->query("SELECT id, username, email, status FROM users LIMIT 1");
$user = $stmt->fetch(PDO::FETCH_CLASS, UserDTO::class);
echo "用户: {$user->username} ({$user->email})\n";

// 使用 fetchAll() 一次性获取所有数据
$stmt = $pdo->query("SELECT id, username, email FROM users WHERE status = 'active'");
$activeUsers = $stmt->fetchAll(PDO::FETCH_CLASS, UserDTO::class);

foreach ($activeUsers as $user) {
    echo "  - {$user->username} ({$user->email})\n";
}

// 使用 fetchColumn() 获取单个列的值
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$totalUsers = $stmt->fetchColumn();
echo "用户总数: {$totalUsers}\n";

插入、更新、删除数据 ​

INSERT 操作 ​

php
<?php
declare(strict_types=1);

// 插入单条数据
$sql = "INSERT INTO users (username, email, age, bio, status) VALUES (:username, :email, :age, :bio, :status)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':username' => 'zhangsan',
    ':email' => 'zhangsan@example.com',
    ':age' => 28,
    ':bio' => 'PHP 开发者，热爱编程。',
    ':status' => 'active',
]);

// 获取最后插入的 ID
$lastInsertId = $pdo->lastInsertId();
echo "新用户 ID: {$lastInsertId}\n";

// 使用 exec() 快速执行（仅用于没有参数的场景）
$count = $pdo->exec("UPDATE users SET status = 'active' WHERE status = 'inactive'");
echo "更新了 {$count} 行\n";

UPDATE 操作 ​

php
<?php
declare(strict_types=1);

// 更新用户信息
$sql = "UPDATE users SET username = :username, email = :email, age = :age WHERE id = :id";
$stmt = $pdo->prepare($sql);

$affectedRows = $stmt->execute([
    ':username' => 'zhangsan_updated',
    ':email' => 'zhangsan_new@example.com',
    ':age' => 29,
    ':id' => 1,
]);

echo "更新了 {$affectedRows} 行\n";

// 批量更新
$updates = [
    ['id' => 1, 'status' => 'active'],
    ['id' => 2, 'status' => 'inactive'],
    ['id' => 3, 'status' => 'active'],
];

$sql = "UPDATE users SET status = :status WHERE id = :id";
$stmt = $pdo->prepare($sql);

$totalUpdated = 0;
foreach ($updates as $update) {
    $stmt->execute($update);
    $totalUpdated += $stmt->rowCount();
}
echo "批量更新了 {$totalUpdated} 行\n";

DELETE 操作 ​

php
<?php
declare(strict_types=1);

// 删除单条记录
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => 1]);
echo "删除了 {$stmt->rowCount()} 行\n";

// 带条件的删除（防止误删）
$sql = "DELETE FROM users WHERE status = :status AND created_at < :date";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':status' => 'banned',
    ':date' => '2023-01-01 00:00:00',
]);
echo "删除了 {$stmt->rowCount()} 个已封禁的旧账户\n";

预处理语句防 SQL 注入 ​

SQL 注入的原理 ​

SQL 注入是指攻击者通过在用户输入中嵌入恶意 SQL 代码来操控数据库查询。预处理语句通过将 SQL 语句结构和数据分开处理，从根本上防止了 SQL 注入：

php
<?php
declare(strict_types=1);

/**
 * SQL 注入防护演示
 */

// ===== 危险做法 — 绝对不要这样做 =====
$userId = "1 OR 1=1";  // 恶意输入
// $sql = "SELECT * FROM users WHERE id = {$userId}";
// 这会变成：SELECT * FROM users WHERE id = 1 OR 1=1
// 攻击者可以获取所有用户数据！

$userId = "1; DROP TABLE users; --";
// $sql = "SELECT * FROM users WHERE id = {$userId}";
// 这会变成：SELECT * FROM users WHERE id = 1; DROP TABLE users; --
// 攻击者可以删除整个表！

// ===== 安全做法 — 使用预处理语句 =====
$userId = "1 OR 1=1";  // 即使是恶意输入
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => $userId]);
// 实际执行的 SQL：SELECT * FROM users WHERE id = '1 OR 1=1'
// PDO 会将整个字符串作为普通参数值，不会解析为 SQL 代码
// 查询结果：空（因为不存在 id 为 "1 OR 1=1" 的记录）

// ===== 动态表名/列名的问题 =====
// 注意：预处理语句不能用于表名和列名（因为它们不是值）
// 如果需要动态表名，必须手动验证白名单：
function queryTable(PDO $pdo, string $tableName, int $limit = 10): array
{
    // 白名单验证表名
    $allowedTables = ['users', 'products', 'orders', 'categories'];
    if (!in_array($tableName, $allowedTables, true)) {
        throw new \InvalidArgumentException("不允许的表名: {$tableName}");
    }

    $limit = max(1, min(100, $limit)); // 验证限制值
    $stmt = $pdo->prepare("SELECT * FROM `{$tableName}` LIMIT :limit");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

echo "安全查询机制演示完成\n";

错误处理 ​

PDO 错误模式 ​

PDO 提供了三种错误处理模式：

错误模式常量说明
静默模式PDO::ERRMODE_SILENT默认模式，只设置错误代码，不抛出异常
警告模式PDO::ERRMODE_WARNING触发 PHP Warning
异常模式PDO::ERRMODE_EXCEPTION抛出 PDOException（推荐）
php
<?php
declare(strict_types=1);

/**
 * 错误处理最佳实践
 */
class DatabaseService
{
    public function __construct(
        private readonly PDO $pdo
    ) {
    }

    /**
     * 在事务中执行操作 — 完整的错误处理
     */
    public function transferUserStatus(int $fromUserId, int $toUserId): string
    {
        try {
            // 开启事务
            $this->pdo->beginTransaction();

            // 1. 检查源用户是否存在
            $stmt = $this->pdo->prepare("SELECT id, username, status FROM users WHERE id = :id FOR UPDATE");
            $stmt->execute([':id' => $fromUserId]);
            $fromUser = $stmt->fetch();

            if (!$fromUser) {
                throw new RuntimeException("源用户不存在: ID {$fromUserId}");
            }

            // 2. 检查目标用户是否存在
            $stmt = $this->pdo->prepare("SELECT id, username FROM users WHERE id = :id FOR UPDATE");
            $stmt->execute([':id' => $toUserId]);
            $toUser = $stmt->fetch();

            if (!$toUser) {
                throw new RuntimeException("目标用户不存在: ID {$toUserId}");
            }

            // 3. 更新源用户状态
            $stmt = $this->pdo->prepare("UPDATE users SET status = 'inactive' WHERE id = :id");
            $stmt->execute([':id' => $fromUserId]);

            // 4. 更新目标用户状态
            $stmt = $this->pdo->prepare("UPDATE users SET status = :status WHERE id = :id");
            $stmt->execute([':id' => $toUserId, ':status' => $fromUser['status']]);

            // 提交事务
            $this->pdo->commit();

            return "成功将用户 {$fromUser['username']} 的状态转移给 {$toUser['username']}";
        } catch (PDOException $e) {
            // 回滚事务
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }

            // 记录错误日志
            error_log("数据库错误: " . $e->getMessage());

            throw new RuntimeException("数据库操作失败: " . $e->getMessage());
        } catch (RuntimeException $e) {
            // 回滚事务
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }

            throw $e;
        }
    }
}

实战示例 ​

完整的 CRUD 示例 ​

php
<?php
declare(strict_types=1);

/**
 * 完整的用户管理 CRUD 类
 * 展示 PDO 在实际项目中的使用方式
 */
class UserRepository
{
    public function __construct(
        private readonly PDO $pdo
    ) {
    }

    // ========== CREATE ==========
    public function create(array $data): int
    {
        $sql = "INSERT INTO users (username, email, age, bio, status) 
                VALUES (:username, :email, :age, :bio, :status)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':username' => $data['username'],
            ':email' => $data['email'],
            ':age' => $data['age'] ?? null,
            ':bio' => $data['bio'] ?? null,
            ':status' => $data['status'] ?? 'active',
        ]);
        return (int) $this->pdo->lastInsertId();
    }

    // ========== READ ==========
    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $email]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public function findAll(int $page = 1, int $perPage = 20, string $status = ''): array
    {
        $offset = ($page - 1) * $perPage;
        $params = [];

        $where = '';
        if ($status !== '') {
            $where = "WHERE status = :status";
            $params[':status'] = $status;
        }

        // 获取分页数据
        $sql = "SELECT id, username, email, age, status, created_at FROM users {$where} 
                ORDER BY id DESC LIMIT :limit OFFSET :offset";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        $items = $stmt->fetchAll();

        // 获取总数
        $countSql = "SELECT COUNT(*) FROM users {$where}";
        $countStmt = $this->pdo->prepare($countSql);
        foreach ($params as $key => $value) {
            $countStmt->bindValue($key, $value);
        }
        $countStmt->execute();
        $total = (int) $countStmt->fetchColumn();

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => (int) ceil($total / $perPage),
        ];
    }

    public function search(string $keyword, int $limit = 10): array
    {
        $like = "%{$keyword}%";
        $stmt = $this->pdo->prepare(
            "SELECT id, username, email FROM users 
             WHERE username LIKE :keyword OR email LIKE :keyword 
             ORDER BY id DESC LIMIT :limit"
        );
        $stmt->bindValue(':keyword', $like);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    // ========== UPDATE ==========
    public function update(int $id, array $data): bool
    {
        $allowedFields = ['username', 'email', 'age', 'bio', 'status'];
        $setClauses = [];
        $params = [':id' => $id];

        foreach ($data as $field => $value) {
            if (in_array($field, $allowedFields, true)) {
                $setClauses[] = "`{$field}` = :{$field}";
                $params[":{$field}"] = $value;
            }
        }

        if (empty($setClauses)) {
            return false;
        }

        $sql = "UPDATE users SET " . implode(', ', $setClauses) . " WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }

    // ========== DELETE ==========
    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM users WHERE id = :id");
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    // ========== 统计 ==========
    public function countByStatus(): array
    {
        $stmt = $this->pdo->query(
            "SELECT status, COUNT(*) as count FROM users GROUP BY status"
        );
        $result = [];
        foreach ($stmt->fetchAll() as $row) {
            $result[$row['status']] = (int) $row['count'];
        }
        return $result;
    }
}

// ===== 使用示例 =====
// $pdo = Database::getInstance()->getConnection();
// $repo = new UserRepository($pdo);

// 创建用户
// $id = $repo->create([
//     'username' => 'wangwu',
//     'email' => 'wangwu@example.com',
//     'age' => 32,
//     'bio' => '全栈开发者',
// ]);
// echo "创建用户 ID: {$id}\n";

// 查询用户
// $user = $repo->findById($id);
// print_r($user);

// 更新用户
// $repo->update($id, ['age' => 33, 'bio' => '资深全栈开发者']);

// 搜索用户
// $results = $repo->search('wang', 5);
// print_r($results);

// 分页查询
// $page = $repo->findAll(page: 1, perPage: 10, status: 'active');
// echo "共 {$page['total']} 条，第 {$page['page']}/{$page['total_pages']} 页\n";

// 删除用户
// $repo->delete($id);

echo "CRUD 操作演示完成\n";

初始化数据库的脚本 ​

php
<?php
declare(strict_types=1);

/**
 * 数据库初始化脚本
 * 创建表并插入示例数据
 */
class DatabaseInitializer
{
    public function __construct(
        private readonly PDO $pdo
    ) {
    }

    public function initialize(): void
    {
        $this->createUsersTable();
        $this->createArticlesTable();
        $this->insertSampleData();
        echo "数据库初始化完成\n";
    }

    private function createUsersTable(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS `users` (
            `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            `username` VARCHAR(50) NOT NULL UNIQUE,
            `email` VARCHAR(100) NOT NULL UNIQUE,
            `age` TINYINT UNSIGNED DEFAULT NULL,
            `bio` TEXT DEFAULT NULL,
            `status` ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $this->pdo->exec($sql);
    }

    private function createArticlesTable(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS `articles` (
            `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            `user_id` INT UNSIGNED NOT NULL,
            `title` VARCHAR(200) NOT NULL,
            `content` TEXT NOT NULL,
            `status` ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $this->pdo->exec($sql);
    }

    private function insertSampleData(): void
    {
        // 检查是否已有数据
        $count = (int) $this->pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
        if ($count > 0) {
            echo "数据已存在，跳过初始化\n";
            return;
        }

        // 插入示例用户
        $stmt = $this->pdo->prepare("INSERT INTO users (username, email, age, bio, status) VALUES (?, ?, ?, ?, ?)");
        $users = [
            ['zhangsan', 'zhangsan@example.com', 28, 'PHP 开发者', 'active'],
            ['lisi', 'lisi@example.com', 32, '全栈工程师', 'active'],
            ['wangwu', 'wangwu@example.com', 25, '前端开发者', 'inactive'],
            ['zhaoliu', 'zhaoliu@example.com', 35, '架构师', 'active'],
            ['tianqi', 'tianqi@example.com', 22, '实习开发者', 'active'],
        ];
        foreach ($users as $user) {
            $stmt->execute($user);
        }

        // 插入示例文章
        $stmt = $this->pdo->prepare("INSERT INTO articles (user_id, title, content, status) VALUES (?, ?, ?, ?)");
        $articles = [
            [1, 'PHP 入门教程', '这是一篇关于 PHP 入门的教程...', 'published'],
            [1, 'MySQL 基础', '学习 MySQL 数据库的基础知识...', 'published'],
            [2, 'Docker 容器化部署', '使用 Docker 部署 PHP 应用...', 'draft'],
            [3, 'Vue.js 实战', 'Vue.js 前端框架的实战教程...', 'published'],
            [4, '系统架构设计', '大型系统的架构设计思路...', 'draft'],
        ];
        foreach ($articles as $article) {
            $stmt->execute($article);
        }

        echo "已插入 5 个用户和 5 篇文章\n";
    }
}

// CLI 模式下的演示
if (PHP_SAPI === 'cli') {
    echo "=== 数据库初始化脚本 ===\n";
    echo "在实际环境中，请确保 MySQL/MariaDB 服务已启动。\n";
    echo "创建数据库: CREATE DATABASE php_tutorial CHARACTER SET utf8mb4;\n";
}

注意事项 ​

  • 始终使用预处理语句：永远不要将用户输入直接拼接到 SQL 语句中，无论输入来源如何。预处理语句是防止 SQL 注入的最有效方式。
  • 连接安全：数据库凭证不应硬编码在源代码中。使用环境变量或配置文件来存储敏感信息。
  • 字符集：始终使用 utf8mb4 字符集（而不是 utf8），以支持完整的 Unicode 字符（包括 Emoji）。
  • 持久连接：不建议在普通 Web 应用中使用持久连接（PDO::ATTR_PERSISTENT => true），它可能导致连接耗尽等问题。
  • 事务使用：确保在 try/catch 中使用事务，并在异常时回滚。不要在事务中执行耗时操作。
  • NULL 处理：注意 null 和空字符串 '' 的区别。PDO 对 null 值会正确处理为 SQL 的 NULL。
  • LIMIT 参数：使用预处理语句时，LIMIT 和 OFFSET 参数必须使用 bindValue() 和 PDO::PARAM_INT 类型声明。

最佳实践 ​

  • 使用 DSN 配置数据库连接，便于切换数据库类型。
  • 始终设置 PDO::ERRMODE_EXCEPTION 为错误处理模式。
  • 始终设置 PDO::ATTR_EMULATE_PREPARES => false 禁用模拟预处理。
  • 使用 PDO::FETCH_ASSOC 作为默认获取模式。
  • 使用 Repository 模式封装数据库操作，将 SQL 逻辑集中在数据访问层。
  • 复杂查询使用事务（BEGIN/COMMIT/ROLLBACK）确保数据一致性。
  • 在生产环境中使用连接池（如 Swoole Database Proxy）管理高并发连接。
  • 使用 password_hash() 和 password_verify() 处理密码存储，而不是明文或 MD5。
  • 为查询添加适当的索引，提高查询性能。
  • 避免在循环中执行单条 SQL，使用批量操作（批量 INSERT/UPDATE）提高效率。

下一节

掌握了数据库交互之后，你已经具备了构建完整 Web 应用的基础能力。接下来可以继续学习 PHP 的面向对象编程、Composer 包管理和框架使用等进阶主题。

参考链接 ​