PHP 存取 MySQL 完整流程指南：連線、查詢、結果處理、資源釋放與 PDO 現代化重構

前言

PHP 操作 MySQL 經歷 `mysql_*`（已移除）、`mysqli`、到 `PDO` 三代演進。現代專案應全面採用 `PDO`，享受參數綁定、異常模式、多資料庫抽象等優勢。本文對比新舊 API，給出標準化操作流程與最佳實踐範本。

一、標準操作流程六步驟

1. 建立連線：DSN、認證、屬性設定。
2. 準備語句：`prepare()` 參數化 SQL。
3. 綁定參數/執行：`execute([...])` 或 `bindParam()` + `execute()`。
4. 取得結果：`fetch()` 單行、`fetchAll()` 全部、`fetchColumn()` 單值。
5. 釋放資源：`closeCursor()`、變數設 `null`。
6. 關閉連線：腳本結束自動關閉，長程序顯式 `$pdo = null`。

二、PDO 完整範本

<?php
// config.php
return [
    'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=mydb;charset=utf8mb4',
    'user' => 'app_user',
    'pass' => 'StrongPassword',
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false, // 真實預備語句
        PDO::MYSQL_ATTR_SSL_CA => __DIR__ . '/ssl/ca.pem', // 生產環境
    ],
];

// db.php - 連線單例
class Database {
    private static ?PDO $instance = null;
    public static function get(): PDO {
        if (self::$instance === null) {
            $config = require __DIR__ . '/config.php';
            self::$instance = new PDO(
                $config['dsn'],
                $config['user'],
                $config['pass'],
                $config['options']
            );
        }
        return self::$instance;
    }
}

// 使用範例
$pdo = Database::get();

// 1. 查詢單筆
$stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->execute([42]);
$user = $stmt->fetch(); // false 或陣列
$stmt->closeCursor();

// 2. 查詢多筆
$stmt = $pdo->prepare("SELECT * FROM articles WHERE status = ? ORDER BY created_at DESC LIMIT ?");
$stmt->execute(['published', 10]);
$articles = $stmt->fetchAll();
$stmt->closeCursor();

// 3. 新增 (回傳自增 ID)
$pdo->prepare("INSERT INTO logs (message, level) VALUES (?, ?)")
    ->execute(['User login', 'info']);
$logId = $pdo->lastInsertId();

// 4. 更新/刪除 (受影響行數)
$affected = $pdo->prepare("UPDATE users SET last_login = NOW() WHERE id = ?")
    ->execute([42])
    ->rowCount();

// 5. 交易控制
try {
    $pdo->beginTransaction();
    $pdo->prepare("INSERT INTO orders ...")->execute([...]);
    $pdo->prepare("UPDATE inventory SET qty = qty - 1 WHERE sku = ?")->execute([$sku]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

三、mysqli 程序式對照（僅供維護舊專案參考）

<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'mydb', 3306);
if ($mysqli->connect_errno) die("Connect failed: " . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');

// 準備語句
$stmt = $mysqli->prepare("SELECT name FROM users WHERE id = ?");
$stmt->bind_param('i', $id);
$id = 42;
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch(); // 逐行
$stmt->close();

$mysqli->close();

四、關鍵差異對照

五、效能與安全最佳實踐

• 永不拼接 SQL：所有動態值皆用參數綁定，含 `LIMIT`、`OFFSET`、`IN (...)` 需動態產生佔位符字串。
• 關閉模擬預備：`PDO::ATTR_EMULATE_PREPARES => false` 使用真實預備語句，防止邊緣注入、提升重複執行效能。
• 僅選需要欄位：避免 `SELECT *`，減少網路傳輸、記憶體佔用、鎖定範圍。
• 大量資料分批處理：`LIMIT 1000` 迴圈、`yield` 生成器、`PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false` 非緩衝查詢（需謹慎）。
• 連線復用：PHP-FPM 工作進程生命週期內重用單例 PDO，避免頻繁握手。
• 嚴格模式：MySQL `sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION` 確保資料完整性。

六、常見陷阱

1. `fetchAll()` 記憶體爆炸 → 大結果集用 `fetch()` 迴圈或 `yield` 生成器。
2. 忘記 `closeCursor()` 導致「Cannot execute queries while other unbuffered queries are active」。
3. 交易中執行 DDL（`CREATE TABLE`、`TRUNCATE`）隱含提交，破壞原子性。
4. `lastInsertId()` 在交易回滾後仍返回 ID，勿用於業務邏輯判斷成功與否。
5. 字元集不一致：DSN `charset=utf8mb4` + 資料庫/表/欄位 `utf8mb4` + 檔案儲存 UTF-8 無 BOM。

結語

PDO 是 PHP 存取 MySQL 的現代標準。掌握「單例連線、參數化查詢、例外錯誤、交易控制、資源釋放」五大核心模式，配合嚴格 SQL 模式與字元集一致性，即能構建安全、高效、可維護的資料存取層。