PHP 資料庫開發五大常見錯誤與最佳實務解決方案

資料庫操作是 PHP 專案的核心，但許多效能瓶頸、安全漏洞與維護噩夢皆源於幾個固定模式的錯誤。本文深度剖析五大經典錯誤：直接使用 mysql_* 函式、手動管理 ID、多資料庫分散、忽略關聯設計、N+1 查詢問題，提供 PDO 重構範例、Schema 修正建議與效能優化策略。

錯誤 1：直接使用已棄用的 mysql_* 函式

問題程式碼

<?php
// 危險：SQL 注入風險、無預處理、PHP 7.0+ 已移除
function getUserId($name) {
    $db = mysql_connect('localhost', 'root', 'password');
    mysql_select_db('users');
    $res = mysql_query("SELECT id FROM users WHERE login = '$name'"); // 字串拼接！
    $row = mysql_fetch_array($res);
    return $row[0];
}
?>

解決方案：PDO + 預處理語句

<?php
require_once 'Db.php'; // 封裝 PDO 連線單例

function getUserId(string $name): ?int {
    $pdo = Db::getInstance();
    $stmt = $pdo->prepare("SELECT id FROM users WHERE login = ?");
    $stmt->execute([$name]);
    return $stmt->fetchColumn() ?: null; // 直接取單欄值
}

// 進階：封裝通用查詢方法
class Db {
    private static ?PDO $instance = null;
    
    public static function getInstance(): PDO {
        if (self::$instance === null) {
            $dsn = "mysql:host=localhost;dbname=myapp;charset=utf8mb4";
            self::$instance = new PDO($dsn, 'user', 'pass', [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false, // 真實預處理
            ]);
        }
        return self::$instance;
    }
}
?>

錯誤 2：手動計算 MAX(id)+1 當作新 ID

問題 Schema

CREATE TABLE users (
    id MEDIUMINT,           -- 無 PRIMARY KEY、無 AUTO_INCREMENT
    login TEXT,
    password TEXT
);

問題程式碼：競態條件、效能差

<?php
$res = $pdo->query("SELECT MAX(id) FROM users");
$maxId = $res->fetchColumn();
$newId = ($maxId ?? 0) + 1; // 並發時極易重複！
$pdo->execute("INSERT INTO users VALUES (?, ?, ?)", [$newId, $name, $pass]);

解決方案：資料庫原生 AUTO_INCREMENT

-- 正確 Schema
CREATE TABLE users (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    login VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_login (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
// PHP 端只需傳 null 或略過 id 欄位
$stmt = $pdo->prepare("INSERT INTO users (login, password_hash) VALUES (?, ?)");
$stmt->execute([$name, password_hash($pass, PASSWORD_DEFAULT)]);
$newId = $pdo->lastInsertId(); // 取得剛插入的 ID
?>

錯誤 3：單一應用拆分多個資料庫

問題場景

• users 資料庫、files 資料庫、logs 資料庫分離
• 跨庫 JOIN 不支援，需 PHP 端多次查詢組合
• 連線管理複雜、交易無法跨庫 (分散式交易極複雜)

解決方案：單一資料庫 + Schema 隔離

-- 全部在 myapp 資料庫，以資料表前綴或 Schema 分組
CREATE DATABASE myapp;
USE myapp;

CREATE TABLE app_users ( ... );
CREATE TABLE app_files ( ..., user_id INT, FOREIGN KEY (user_id) REFERENCES app_users(id) );
CREATE TABLE app_logs ( ... );

<?php
-- 單一查詢完成關聯 (利用索引、JOIN 最佳化)
$stmt = $pdo->prepare("
    SELECT f.* 
    FROM app_files f
    JOIN app_users u ON u.id = f.user_id
    WHERE u.login = ?
");
$stmt->execute([$name]);
$files = $stmt->fetchAll();

錯誤 4：用逗號分隔字串模擬陣列 (違反第一正規化)

問題 Schema

CREATE TABLE users (
    id INT PRIMARY KEY,
    login VARCHAR(50),
    files TEXT  -- 存 "1,2,5,8" 等檔案 ID 字串
);

問題程式碼：N 次查詢、無法索引、解析痛苦

<?php
$filesStr = $pdo->query("SELECT files FROM users WHERE login='john'")->fetchColumn();
$fileIds = explode(',', $filesStr);
foreach ($fileIds as $id) {
    $stmt = $pdo->prepare("SELECT * FROM files WHERE id = ?");
    $stmt->execute([$id]);
    $rows[] = $stmt->fetch();
}

解決方案：正規化關聯設計 (一對多)

CREATE TABLE files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(255),
    path VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

<?php
-- 單一 JOIN 查詢
$stmt = $pdo->prepare("
    SELECT f.* FROM files f
    JOIN users u ON u.id = f.user_id
    WHERE u.login = ?
");
$stmt->execute([$name]);
$files = $stmt->fetchAll();

錯誤 5：N+1 查詢模式 (效能殺手)

問題程式碼：作者 → 書單 → 每本書詳情

<?php
$authorId = $pdo->query("SELECT id FROM authors WHERE name='Jack'")->fetchColumn();
$bookIds = $pdo->query("SELECT id FROM books WHERE author_id=$authorId")->fetchAll(PDO::FETCH_COLUMN);

foreach ($bookIds as $bookId) {
    $book = $pdo->query("SELECT * FROM books WHERE id=$bookId")->fetch(); // N 次查詢！
    echo $book['name'] . "
";
}
// 1 作者 + 1 書單 + N 書詳情 = N+2 次查詢

解決方案：單一 JOIN 查詢或批次載入

<?php
// 方案 A：單一 JOIN (最推薦，資料庫最擅長)
$stmt = $pdo->prepare("
    SELECT b.* FROM books b
    JOIN authors a ON a.id = b.author_id
    WHERE a.name = ?
");
$stmt->execute(['Jack']);
$books = $stmt->fetchAll();

// 方案 B：批次載入 (需 ORM 或手動實作 IN 查詢)
$bookIds = $pdo->query("SELECT id FROM books WHERE author_id = $authorId")->fetchAll(PDO::FETCH_COLUMN);
if ($bookIds) {
    $placeholders = implode(',', array_fill(0, count($bookIds), '?'));
    $stmt = $pdo->prepare("SELECT * FROM books WHERE id IN ($placeholders)");
    $stmt->execute($bookIds);
    $books = $stmt->fetchAll();
    // 建立 id => book 映射，保持原順序
    $bookMap = array_column($books, null, 'id');
    $books = array_replace(array_flip($bookIds), $bookMap); // 略...
}
?>

綜合最佳實務清單

總結

這五大錯誤貫穿了資料庫設計、存取層實作與查詢優化全生命週期。核心原則只有一個：信任資料庫、善用資料庫、別在 PHP 重造資料庫功能。掌握 PDO 預處理、正規化 Schema 設計、JOIN 查詢思維與 N+1 識別能力，即可避開 90% 的資料庫坑，打造高效、安全、可維護的資料存取層。