PHP MySQL 連線與基本 CRUD 操作快速範本 (mysqli/PDO)

⚠️ 重要：棄用 mysql_* 函數

原文使用 `mysql_connect` 等函數 **PHP 7.0 已移除**。現代專案**必須使用 `mysqli` 或 `PDO`**。

方案一：PDO (推薦、支援多資料庫)

<?php
// config/database.php
return [
    'dsn' => 'mysql:host=localhost;dbname=products;charset=utf8mb4',
    'user' => 'root',
    'pass' => '123456',
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
    ],
];

// Database.php (單例連線)
class Database
{
    private static ?PDO $instance = null;

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

// 使用範例
try {
    $pdo = Database::getConnection();

    // --- 查詢 (SELECT) ---
    $stmt = $pdo->query("SELECT id, name FROM category");
    while ($row = $stmt->fetch()) {
        echo "ID: {$row['id']}, Name: {$row['name']}\n";
    }

    // --- 新增 (INSERT) ---
    $name = 'New Category';
    $stmt = $pdo->prepare("INSERT INTO category (name) VALUES (?)");
    $stmt->execute([$name]);
    $newId = $pdo->lastInsertId();
    echo "插入成功，ID: $newId\n";

    // --- 修改 (UPDATE) ---
    $id = 1;
    $newName = 'Updated Name';
    $stmt = $pdo->prepare("UPDATE category SET name = ? WHERE id = ?");
    $affected = $stmt->execute([$newName, $id]);
    echo "影響列數: $affected\n";

    // --- 刪除 (DELETE) ---
    $id = 2;
    $stmt = $pdo->prepare("DELETE FROM category WHERE id = ?");
    $affected = $stmt->execute([$id]);
    echo "刪除列數: $affected\n";

    // --- 事務 ---
    $pdo->beginTransaction();
    try {
        $pdo->exec("INSERT INTO log (msg) VALUES ('tx start')");
        $pdo->exec("UPDATE account SET balance = balance - 100 WHERE id = 1");
        $pdo->exec("UPDATE account SET balance = balance + 100 WHERE id = 2");
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }

} catch (PDOException $e) {
    error_log("DB Error: " . $e->getMessage());
    die("資料庫操作失敗");
}
?>

方案二：mysqli (程序式/物件導向)

<?php
// 物件導向風格
$mysqli = new mysqli('localhost', 'root', '123456', 'products');
if ($mysqli->connect_errno) {
    die("連線失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');

// 查詢
$result = $mysqli->query("SELECT id, name FROM category");
while ($row = $result->fetch_assoc()) {
    echo "ID: {$row['id']}, Name: {$row['name']}\n";
}
$result->free();

// 預處理新增
$stmt = $mysqli->prepare("INSERT INTO category (name) VALUES (?)");
$name = 'Test Cat';
$stmt->bind_param('s', $name);
$stmt->execute();
echo "新增 ID: " . $mysqli->insert_id . "\n";
$stmt->close();

// 關閉
$mysqli->close();
?>

關鍵最佳實踐對照

常見錯誤避坑

<?php
// ❌ 錯誤：直接拼接變數 (SQL 注入漏洞)
$sql = "SELECT * FROM users WHERE name = '$name'";

// ✅ 正確：預處理 + 參數綁定
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$name]);

// ❌ 錯誤：LIKE 查詢參數化寫法
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ?");
$stmt->execute(["%$keyword%"]); // 正確：% 在 PHP 端加

// ❌ 錯誤：IN 子句直接綁定陣列
$ids = [1,2,3];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN (?)");
$stmt->execute([$ids]); // 錯誤！

// ✅ 正確：動態產生佔位符
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($placeholders)");
$stmt->execute($ids);
?>

連線池與效能優化 (高併發)

• **持久連線**：PDO `PDO::ATTR_PERSISTENT => true` (慎用，易連線洩漏)
• **連線池**：Swoole/RoadRunner + PDO 連線池、PHP-FPM 無原生連線池
• **讀寫分離**：主庫寫、從庫讀，應用層或中間件 (ProxySQL) 路由
• **慢查詢監控**：開啟 MySQL `slow_query_log`、定期 `EXPLAIN` 分析