萬盛學電腦網

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

PHP 連接 MySQL 完整指南:PDO 與 MySQLi 最佳實踐

棄用 mysql_* 函數

警告:mysql_connect、mysql_query 等函數自 PHP 5.5 棄用,PHP 7.0 完全移除。**請勿在新專案使用**。

推薦方案:PDO (PHP Data Objects)

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

連接配置 (config/database.php)

<?php
return [
    'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=myapp;charset=utf8mb4',
    'username' => 'db_user',
    'password' => 'secure_password',
    '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",
    ],
];

連接實例化 (單例模式/依賴注入)

<?php
class Database
{
    private static ?PDO $instance = null;

    public static function getInstance(): PDO
    {
        if (self::$instance === null) {
            $config = require __DIR__ . '/config/database.php';
            self::$instance = new PDO(
                $config['dsn'],
                $config['username'],
                $config['password'],
                $config['options']
            );
        }
        return self::$instance;
    }
}

核心操作範例

查詢 (SELECT)

<?php
$pdo = Database::getInstance();

// 單條記錄
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND status = ?');
$stmt->execute([1, 1]);
$user = $stmt->fetch();  // 關聯數組

// 多條記錄
$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE created_at > ? ORDER BY id DESC LIMIT 20');
$stmt->execute(['2024-01-01']);
$users = $stmt->fetchAll();

// 統計
$count = $pdo->query('SELECT COUNT(*) FROM users WHERE status = 1')->fetchColumn();

新增 (INSERT) - 返回自增 ID

<?php
$pdo = Database::getInstance();
$sql = 'INSERT INTO users (username, email, password_hash, created_at) VALUES (?, ?, ?, NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute(['zhangsan', 'zhang@example.com', password_hash('secret', PASSWORD_DEFAULT)]);
$userId = $pdo->lastInsertId();
echo "新用戶 ID: $userId";

修改 (UPDATE) / 刪除 (DELETE)

<?php
// 更新
$stmt = $pdo->prepare('UPDATE users SET email = ? WHERE id = ?');
$stmt->execute(['new@example.com', 1]);
echo "影響行數: " . $stmt->rowCount();

// 刪除 (軟刪除建議)
$stmt = $pdo->prepare('UPDATE users SET deleted_at = NOW() WHERE id = ?');
$stmt->execute([1]);

事務處理 (ACID)

<?php
$pdo = Database::getInstance();
try {
    $pdo->beginTransaction();
    
    $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)')->execute([1, 99.00]);
    $orderId = $pdo->lastInsertId();
    
    $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)')
        ->execute([$orderId, 101, 2, 49.50]);
    
    $pdo->prepare('UPDATE products SET stock = stock - 2 WHERE id = 101')->execute();
    
    $pdo->commit();
    echo "訂單創建成功";
} catch (Throwable $e) {
    $pdo->rollBack();
    error_log("訂單失敗: " . $e->getMessage());
    throw $e;
}

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 * FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    print_r($row);
}
$stmt->close();
$mysqli->close();

安全與效能鐵律

  1. 永遠使用預處理語句,嚴禁字串拼接 SQL
  2. 最小權限原則:資料庫帳號僅授予必要權限 (SELECT/INSERT/UPDATE),拒絕 DROP/ALTER
  3. 連接池/持久連接:高併發場景配合 Swoole/Workerman 或 PHP-FPM 持久連接 (PDO::ATTR_PERSISTENT => true),注意連接數上限
  4. 索引優化:EXPLAIN 分析慢查詢,避免全表掃描
  5. 讀寫分離:主庫寫、從庫讀,中間件 (ProxySQL、MyCat) 或代碼層路由

錯誤處理模式對比

模式PDO 設定特點
異常模式 (推薦)PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION拋出 PDOException,try-catch 統一處理,堆疊追蹤完整
警告模式PDO::ERRMODE_WARNING觸發 E_WARNING,不中斷執行,難以捕獲
靜默模式 (預設)PDO::ERRMODE_SILENT需手動檢查 errorCode()/errorInfo(),易遺漏
copyright © 萬盛學電腦網 all rights reserved