萬盛學電腦網

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

PHP 連接 MySQL 數據庫完整教程:PDO 最佳實踐與常見錯誤排查

棄用 mysql_* 函數警告

mysql_connect()、mysql_query() 等函數自 PHP 5.5 棄用,PHP 7.0 完全移除。 新專案請直接使用 PDO 或 MySQLi。

推薦方案:PDO (PHP Data Objects)

統一介面、支援多種數據庫、預處理語句防注入、異常模式、物件導向。

1. 連接配置分離 (config/database.php)

<?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,
    ],
];

2. 建立連接實例 (單例/依賴注入)

<?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("不支援反序列化"); }
}

核心 CRUD 操作

查詢 (SELECT)

<?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();

新增 (INSERT) - 獲取自增 ID

<?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";

修改 (UPDATE)

<?php
$stmt = $pdo->prepare('UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?');
$affected = $stmt->execute(['new@example.com', 1]);
echo "影響行數: $affected"; // 0 表示無匹配或數據未變

刪除 (DELETE) - 建議軟刪除

<?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]);

事務處理 (ACID)

<?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('下單失敗,請稍後重試');
}

MySQLi 面向對象備選方案

<?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 vs MySQLi 選型

安全鐵律

  1. 永遠使用預處理語句,嚴禁字串拼接 SQL
  2. 最小權限帳號:僅授予 SELECT/INSERT/UPDATE/DELETE,拒絕 DROP/ALTER/GRANT
  3. 密碼哈希:password_hash() + password_verify(),禁用 MD5/SHA1
  4. 錯誤不回顯:生產環境 display_errors=Off,記錄日誌
  5. 防注入測試:單元測試覆蓋 SQL 注入 Payload

常見錯誤排查表

特性PDOMySQLi
數據庫支援12+ (MySQL、PostgreSQL、SQLite、SQL Server...)僅 MySQL/MariaDB
預處理客戶端/服務端均支援 (ATTR_EMULATE_PREPARES)服務端預處理
命名參數支援 :name不支援 (僅 ?)
異常模式預設可配置 ERRMODE_EXCEPTION需手動檢查 errno/error
性能略低 (抽象層)略高 (原生)
推薦場景多數據庫、現代框架、可移植性要求高純 MySQL、極致性能、舊專案遷移
錯誤現象可能原因解決方案
SQLSTATE[HY000] [2002] Connection refusedMySQL 服務未啟動/端口錯誤/防火牆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' unsupportedMySQL 版本過舊 (< 5.5.3)升級 MySQL 或改用 utf8 (不支援 Emoji)
MySQL server has gone away連接超時 (wait_timeout)、封包過大 (max_allowed_packet)調大 wait_timeout/max_allowed_packet、啟用持久連接/重連邏輯

進階優化

  • 連接池:Swoole/Workerman 協程池、ProxySQL、PgBouncer (PostgreSQL)
  • 讀寫分離:主庫寫、從庫讀,中間件或代碼層路由
  • 慢查詢分析:slow_query_log=1、long_query_time=1、EXPLAIN、pt-query-digest
  • 索引優化:覆蓋索引、最左前綴、避免 SELECT *、關聯查詢驅動表選擇
copyright © 萬盛學電腦網 all rights reserved