PHP 操作 MySQL 入門實戰：PDO 預處理語句、事務控制、錯誤處理與安全最佳實踐

前言

PHP 連接 MySQL 歷經 mysql_* (已移除)、mysqli、PDO 三代。現代開發**強制使用 PDO**，支援多資料庫、真正預處理、異常模式、具名參數。本文以 PDO 為核心，示範連線配置、增刪改查、事務、批次操作、錯誤處理與防注入鐵律。

一、PDO 連線配置單例

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

    public static function getInstance(): PDO {
        if (self::$instance === null) {
            $dsn = 'mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8mb4';
            $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 COLLATE utf8mb4_general_ci",
            ];
            self::$instance = new PDO($dsn, 'app_user', 'AppPassw0rd!', $options);
        }
        return self::$instance;
    }

    // 禁止複製/序列化
    private function __construct() {}
    private function __clone() {}
    public function __wakeup() { throw new Exception('不支援反序列化'); }
}

二、核心 CRUD 操作

2.1 插入 (INSERT) - 回傳自增 ID

$pdo = Database::getInstance();
$sql = 'INSERT INTO user (username, email, password_hash, reg_at) VALUES (?, ?, ?, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'john_doe',
    'john@example.com',
    password_hash('secret123', PASSWORD_ARGON2ID),  // 現代密碼雜湊
    date('Y-m-d H:i:s')
]);
$userId = $pdo->lastInsertId();
echo "新用戶 ID: $userId";

2.2 查詢 (SELECT) - 參數化與獲取模式

// 單行
$stmt = $pdo->prepare('SELECT * FROM user WHERE id = ?');
$stmt->execute([$userId]);
$user = $stmt->fetch();  // FETCH_ASSOC

// 多行
$stmt = $pdo->prepare('SELECT id, username FROM user WHERE status = ? ORDER BY reg_at DESC LIMIT ?');
$stmt->execute([1, 20]);
$users = $stmt->fetchAll();  // 陣列 of 關聯陣列

// 大結果集串流處理 (避免記憶體爆炸)
$stmt = $pdo->prepare('SELECT * FROM article WHERE status = ?');
$stmt->execute([1]);
while ($row = $stmt->fetch()) {
    // 處理單行
}

2.3 更新 (UPDATE) - 影響行數

$stmt = $pdo->prepare('UPDATE user SET email = ?, updated_at = NOW() WHERE id = ?');
$stmt->execute(['new@example.com', $userId]);
$affected = $stmt->rowCount();
echo "更新行數: $affected";

2.4 刪除 (DELETE) - 軟刪除模式

// 推薦軟刪除：加 deleted_at 欄位
$stmt = $pdo->prepare('UPDATE user SET deleted_at = NOW() WHERE id = ?');
$stmt->execute([$userId]);

// 真刪除 (謹慎)
$stmt = $pdo->prepare('DELETE FROM user WHERE id = ?');
$stmt->execute([$userId]);

三、事務控制

$pdo = Database::getInstance();
try {
    $pdo->beginTransaction();

    // 扣款
    $pdo->prepare('UPDATE account SET balance = balance - ? WHERE user_id = ? AND balance >= ?')
        ->execute([100, $userId, 100]);
    if ($pdo->prepare('SELECT ROW_COUNT()')->fetchColumn() === 0) {
        throw new Exception('餘額不足');
    }

    // 記帳
    $pdo->prepare('INSERT INTO transaction (user_id, amount, type, created_at) VALUES (?, ?, ?, NOW())')
        ->execute([$userId, -100, 'debit']);

    $pdo->commit();
    echo "事務提交成功";
} catch (Throwable $e) {
    $pdo->rollBack();
    error_log("事務回滾: {$e->getMessage()}");
    throw $e;  // 向上拋出
}

3.1 事務隔離級別設定

// 僅對當前事務生效
$pdo->prepare("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")->execute();
$pdo->beginTransaction();
// ...
$pdo->commit();

四、批次操作優化

4.1 批次插入 (單條 SQL 多值)

$data = [
    ['user1', 'u1@test.com'],
    ['user2', 'u2@test.com'],
    // ... 1000 行
];

$placeholders = implode(',', array_fill(0, count($data), '(?, ?)'));
$sql = "INSERT INTO user (username, email) VALUES $placeholders";
$stmt = $pdo->prepare($sql);
$flat = array_merge(...$data);  // 展平為一維陣列
$stmt->execute($flat);

4.2 批次更新 (CASE WHEN)

$updates = [ [1, 'new1@test.com'], [2, 'new2@test.com'], [3, 'new3@test.com'] ];
$ids = implode(',', array_column($updates, 0));
$cases = implode(' ', array_map(fn($u) => "WHEN {$u[0]} THEN '{$u[1]}'", $updates));
$sql = "UPDATE user SET email = CASE id $cases END WHERE id IN ($ids)";
$pdo->exec($sql);

五、錯誤處理與日誌

try {
    $pdo->prepare('INSERT INTO user (username) VALUES (?)')->execute(['duplicate']);
} catch (PDOException $e) {
    // 23000: 完整性約束違反 (唯一鍵、外鍵)
    if ($e->getCode() === '23000') {
        error_log("資料重複: {$e->getMessage()}");
        throw new UserException('用戶名已存在');
    }
    // 其他錯誤
    error_log("資料庫錯誤: {$e->getMessage()}");
    throw new SystemException('系統繁忙，請稍後再試');
}

六、安全鐵律：防 SQL 注入

七、連線池與效能調優 (Swoole/RoadRunner)

// Swoole 協程環境下使用 PDO 連線池
$pool = new Swoole\Database\PDOPool((object)[
    'host' => '127.0.0.1', 'port' => 3306,
    'dbname' => 'test', 'charset' => 'utf8mb4',
    'user' => 'app_user', 'password' => 'AppPassw0rd!',
], 50);  // 50 條連線

// 協程中獲取
$pdo = $pool->get();
try {
    $stmt = $pdo->prepare('SELECT * FROM user WHERE id = ?');
    $stmt->execute([1]);
    $user = $stmt->fetch();
} finally {
    $pool->put($pdo);  // 歸還連線
}

結語： PDO + 預處理語句 + 異常模式是 PHP 操作 MySQL 的鐵三角。嚴守「參數化查詢、事務原子性、錯誤碼分層處理、連線池複用」，即能構建安全、高效、可維護的資料存取層。