萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 操作 MySQL 基礎教程:mysqli/PDO 對比、預處理防注入、事務、錯誤處理

PHP 操作 MySQL 已經**完全淘汰 mysql_* 函數**(PHP 7.0 移除)。現代開發只用 **mysqli** 或 **PDO**。本文對比兩者優劣,詳細講解預處理語句防 SQL 注入、事務控制、錯誤處理、連接池等生產級最佳實踐。

mysqli vs PDO 選型指南

維度mysqliPDO
數據庫支持僅 MySQL/MariaDB12+ 驅動
API 風格面向過程 + 面向對象純面向對象
預處理支持 (stmt)支持 (核心優勢)
命名參數不支持 (只能 ?)支持 (:name)
獲取結果集fetch_assoc, fetch_allfetch(PDO::FETCH_ASSOC), fetchAll
事務$mysqli->begin_transaction()$pdo->beginTransaction()
異步查詢支持 (mysqli::poll)不支持
推薦場景純 MySQL、高性能異步、舊項目維護**新項目首選**、多數據庫、現代框架

PDO 完整最佳實踐模板

<?php
// config/database.php
return [
    'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=app;charset=utf8mb4',
    'user' => 'app_user',
    'pass' => 'secret',
    '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_unicode_ci",
        PDO::ATTR_STRINGIFY_FETCHES  => false,                  // 保持原生類型
        PDO::ATTR_PERSISTENT         => false,                  // 長連接慎用
    ],
];

// bootstrap.php
$config = require 'config/database.php';
$pdo = new PDO($config['dsn'], $config['user'], $config['pass'], $config['options']);

// 封裝為服務類
class Database
{
    private PDO $pdo;

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

    /** 查詢單行 */
    public function fetchOne(string $sql, array $params = []): array|false
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch();
    }

    /** 查詢多行 */
    public function fetchAll(string $sql, array $params = []): array
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** 查詢單值 */
    public function fetchValue(string $sql, array $params = []): mixed
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }

    /** 執行增刪改,返回影響行數 */
    public function execute(string $sql, array $params = []): int
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    /** 獲取最後插入 ID */
    public function lastInsertId(string $name = null): string
    {
        return $this->pdo->lastInsertId($name);
    }

    /** 事務執行 (自動回滾) */
    public function transaction(callable $callback): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    /** 批量插入 (構建 VALUES (?,?), (?,?) ) */
    public function bulkInsert(string $table, array $columns, array $rows): int
    {
        if (!$rows) return 0;
        $placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
        $sql = "INSERT INTO `{$table}` (`" . implode('`,`', $columns) . "`) VALUES " .
               implode(',', array_fill(0, count($rows), $placeholders));

        $flatParams = [];
        foreach ($rows as $row) {
            $flatParams = array_merge($flatParams, array_values($row));
        }
        return $this->execute($sql, $flatParams);
    }
}

實戰示例

1. 安全查詢 (預處理防注入)

$db = new Database($pdo);

// 危險 ❌
// $db->fetchOne("SELECT * FROM users WHERE name = '{$name}'");

// 安全 ✅ 參數綁定
$user = $db->fetchOne('SELECT * FROM users WHERE name = ? AND status = ?', [$name, 1]);

// 命名參數 (更易讀)
$user = $db->fetchOne('SELECT * FROM users WHERE name = :name AND status = :status', [
    'name' => $name,
    'status' => 1,
]);

2. 事務:轉賬操作

try {
    $db->transaction(function($db) use ($from, $to, $amount) {
        // 扣款
        $db->execute('UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?', [$amount, $from, $amount]);
        if ($db->lastInsertId() === '0') throw new Exception('Insufficient balance');

        // 入賬
        $db->execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [$amount, $to]);

        // 記錄流水
        $db->execute('INSERT INTO transactions (from_id, to_id, amount) VALUES (?, ?, ?)', [$from, $to, $amount]);
    });
    echo "Transfer success";
} catch (Exception $e) {
    echo "Transfer failed: " . $e->getMessage();
}

3. 批量插入優化

$data = [];
for ($i = 0; $i < 10000; $i++) {
    $data[] = ['user_id' => $i, 'action' => 'login', 'ip' => '127.0.0.1', 'created_at' => date('Y-m-d H:i:s')];
}

// 分批 1000 條,避免 SQL 過長 / 內存峰值
$chunked = array_chunk($data, 1000);
foreach ($chunked as $chunk) {
    $db->bulkInsert('user_logs', ['user_id', 'action', 'ip', 'created_at'], $chunk);
}

mysqli 面向對象寫法 (對比參考)

$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'app', 3306);
$mysqli->set_charset('utf8mb4');
$mysqli->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // 拋出異常

// 預處理
$stmt = $mysqli->prepare('SELECT * FROM users WHERE name = ?');
$stmt->bind_param('s', $name); // 類型: s/i/d/b
$stmt->execute();
$result = $stmt->get_result(); // 需 mysqlnd
$user = $result->fetch_assoc();
$stmt->close();

// 事務
$mysqli->begin_transaction();
try {
    $mysqli->query('UPDATE ...');
    $mysqli->commit();
} catch (mysqli_sql_exception $e) {
    $mysqli->rollback();
    throw $e;
}

關鍵最佳實踐總結

  1. 永遠使用預處理語句 (prepare/execute),**絕不**拼接 SQL 字符串。設置 ATTR_EMULATE_PREPARES => false 確保真正服務端預處理。
  2. 字符集統一 utf8mb4:DSN、數據庫、表、列、連接全部 utf8mb4,支持 Emoji。
  3. 錯誤模式設置為異常:PDO::ERRMODE_EXCEPTION / MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT,不要用 if ($res === false) 判斷。
  4. 釋放資源:PDOStatement 自動釋放,長運行腳本手動 $stmt = null。mysqli 需 $stmt->close()。
  5. 大數據集用游標/分塊:fetchAll() 會撐爆內存,用 while ($row = $stmt->fetch()) 或生成器封裝。
  6. 連接池:PHP-FPM 無原生連接池,依賴持久連接 ATTR_PERSISTENT 或中間件 (Swoole/RoadRunner/PHP-PM) 實現真正連接池。
  7. SQL 注入測試:提交 ' OR '1'='1、'; DROP TABLE users;-- 驗證預處理生效。

遷移建議

  • 舊項目 mysql_* → 先改 mysqli 面向過程風格 (改動最小),再逐步重構為 PDO。
  • 新項目**直接用 PDO** 或框架 DBAL (Laravel Eloquent, Doctrine DBAL, ThinkPHP Db, Yii ActiveRecord)。
  • 封裝統一 Database 服務類,業務層只調用 fetchOne/fetchAll/execute/transaction,屏蔽底層細節。

掌握 PDO 預處理、事務、錯誤處理三大核心,即可應對 99% 的 MySQL 操作場景,寫出安全、高效、可維護的數據層代碼。

copyright © 萬盛學電腦網 all rights reserved