PHP 操作 MySQL 已經**完全淘汰 mysql_* 函數**(PHP 7.0 移除)。現代開發只用 **mysqli** 或 **PDO**。本文對比兩者優劣,詳細講解預處理語句防 SQL 注入、事務控制、錯誤處理、連接池等生產級最佳實踐。
| 維度 | mysqli | PDO |
|---|---|---|
| 數據庫支持 | 僅 MySQL/MariaDB | 12+ 驅動 |
| API 風格 | 面向過程 + 面向對象 | 純面向對象 |
| 預處理 | 支持 (stmt) | 支持 (核心優勢) |
| 命名參數 | 不支持 (只能 ?) | 支持 (:name) |
| 獲取結果集 | fetch_assoc, fetch_all | fetch(PDO::FETCH_ASSOC), fetchAll |
| 事務 | $mysqli->begin_transaction() | $pdo->beginTransaction() |
| 異步查詢 | 支持 (mysqli::poll) | 不支持 |
| 推薦場景 | 純 MySQL、高性能異步、舊項目維護 | **新項目首選**、多數據庫、現代框架 |
<?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);
}
}
$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,
]);
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();
}
$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 = 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;
}
prepare/execute),**絕不**拼接 SQL 字符串。設置 ATTR_EMULATE_PREPARES => false 確保真正服務端預處理。PDO::ERRMODE_EXCEPTION / MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT,不要用 if ($res === false) 判斷。$stmt = null。mysqli 需 $stmt->close()。fetchAll() 會撐爆內存,用 while ($row = $stmt->fetch()) 或生成器封裝。ATTR_PERSISTENT 或中間件 (Swoole/RoadRunner/PHP-PM) 實現真正連接池。' OR '1'='1、'; DROP TABLE users;-- 驗證預處理生效。mysql_* → 先改 mysqli 面向過程風格 (改動最小),再逐步重構為 PDO。Database 服務類,業務層只調用 fetchOne/fetchAll/execute/transaction,屏蔽底層細節。掌握 PDO 預處理、事務、錯誤處理三大核心,即可應對 99% 的 MySQL 操作場景,寫出安全、高效、可維護的數據層代碼。