mysql_connect()、mysql_query() 等函數自 PHP 5.5 棄用,PHP 7.0 完全移除。 新專案請直接使用 PDO 或 MySQLi。
統一介面、支援多種數據庫、預處理語句防注入、異常模式、物件導向。
<?php
return [
'driver' => 'mysql',
'host' => '127.0.0.1',
'port' => 3306,
'database' => 'myapp',
'username' => 'db_user',
'password' => 'secure_password',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'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_PERSISTENT => true,
],
];
<?php
class Database
{
private static ?PDO $pdo = null;
public static function getConnection(): PDO
{
if (self::$pdo === null) {
$config = require __DIR__ . '/config/database.php';
$dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']};charset={$config['charset']}";
self::$pdo = new PDO(
$dsn,
$config['username'],
$config['password'],
$config['options']
);
}
return self::$pdo;
}
// 防止克隆/序列化
private function __construct() {}
private function __clone() {}
public function __wakeup() { throw new Exception("不支援反序列化"); }
}
<?php
$pdo = Database::getConnection();
// 單行
$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE id = ? AND status = ?');
$stmt->execute([1, 1]);
$user = $stmt->fetch(); // false 或關聯數組
// 多行
$stmt = $pdo->prepare('SELECT * FROM articles WHERE category_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?');
$stmt->execute([5, 10, 0]);
$articles = $stmt->fetchAll();
// 統計
$total = $pdo->query('SELECT COUNT(*) FROM users WHERE status = 1')->fetchColumn();
<?php
$pdo = Database::getConnection();
$sql = 'INSERT INTO users (username, email, password_hash, created_at) VALUES (?, ?, ?, NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute([
'zhangsan',
'zhang@example.com',
password_hash('user_password', PASSWORD_DEFAULT), // 密碼哈希
]);
$userId = $pdo->lastInsertId();
echo "新用戶 ID: $userId";
<?php
$stmt = $pdo->prepare('UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?');
$affected = $stmt->execute(['new@example.com', 1]);
echo "影響行數: $affected"; // 0 表示無匹配或數據未變
<?php
// 軟刪除
$stmt = $pdo->prepare('UPDATE users SET deleted_at = NOW() WHERE id = ?');
$stmt->execute([1]);
// 硬刪除 (謹慎)
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([1]);
<?php
$pdo = Database::getConnection();
try {
$pdo->beginTransaction();
// 扣減庫存
$stmt = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?');
$stmt->execute([2, 101, 2]);
if ($stmt->rowCount() === 0) {
throw new Exception('庫存不足');
}
// 創建訂單
$stmt = $pdo->prepare('INSERT INTO orders (user_id, total, status, created_at) VALUES (?, ?, 1, NOW())');
$stmt->execute([1, 198.00]);
$orderId = $pdo->lastInsertId();
// 訂單項
$stmt = $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)');
$stmt->execute([$orderId, 101, 2, 99.00]);
$pdo->commit();
echo "下單成功,訂單號: $orderId";
} catch (Throwable $e) {
$pdo->rollBack();
error_log("下單失敗: " . $e->getMessage());
throw new Exception('下單失敗,請稍後重試');
}
<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'db', 3306);
if ($mysqli->connect_errno) {
throw new Exception("連接失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');
// 預處理查詢
$stmt = $mysqli->prepare('SELECT id, username FROM users WHERE email = ?');
$stmt->bind_param('s', $email);
$email = 'test@example.com';
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$stmt->close();
$mysqli->close();
| 特性 | PDO | MySQLi |
|---|---|---|
| 數據庫支援 | 12+ (MySQL、PostgreSQL、SQLite、SQL Server...) | 僅 MySQL/MariaDB |
| 預處理 | 客戶端/服務端均支援 (ATTR_EMULATE_PREPARES) | 服務端預處理 |
| 命名參數 | 支援 :name | 不支援 (僅 ?) |
| 異常模式 | 預設可配置 ERRMODE_EXCEPTION | 需手動檢查 errno/error |
| 性能 | 略低 (抽象層) | 略高 (原生) |
| 推薦場景 | 多數據庫、現代框架、可移植性要求高 | 純 MySQL、極致性能、舊專案遷移 |
| 錯誤現象 | 可能原因 | 解決方案 |
|---|---|---|
| SQLSTATE[HY000] [2002] Connection refused | MySQL 服務未啟動/端口錯誤/防火牆 | systemctl start mysqld、檢查 3306 端口、bind-address=0.0.0.0 |
| SQLSTATE[HY000] [1045] Access denied | 用戶名/密碼錯誤、主機權限、密碼插件 | 檢查 user 表 host 欄位、FLUSH PRIVILEGES、caching_sha2_password 兼容性 |
| PDOException: could not find driver | 未安裝/啟用 pdo_mysql 擴展 | 安裝 php-mysql/php8.2-mysql、php.ini 啟用 extension=pdo_mysql |
| Character set 'utf8mb4' unsupported | MySQL 版本過舊 (< 5.5.3) | 升級 MySQL 或改用 utf8 (不支援 Emoji) |
| MySQL server has gone away | 連接超時 (wait_timeout)、封包過大 (max_allowed_packet) | 調大 wait_timeout/max_allowed_packet、啟用持久連接/重連邏輯 |
slow_query_log=1、long_query_time=1、EXPLAIN、pt-query-digest