PHP mysqli 連接 MySQL 資料庫完整實作：連接、查詢、預處理、交易、錯誤處理與安全最佳實務

PHP 連接 MySQL 官方推薦使用 mysqli (MySQL Improved) 或 PDO，已淘汰 mysql_* 函數 (PHP 7.0 移除)。本文提供 mysqli 物件導向與程序式完整範例，涵蓋連接配置、查詢執行、預處理語句 (防 SQL 注入)、交易控制、錯誤處理、字元集設定與安全最佳實務。

基礎連接配置

 '127.0.0.1',      // 或 localhost (Unix socket)
    'port'     => 3306,
    'username' => 'app_user',       // 專用應用帳號，非 root
    'password' => 'Str0ngP@ssw0rd!', // 強密碼，勿硬編碼 (建議用環境變數)
    'database' => 'myapp_db',
    'charset'  => 'utf8mb4',        // 完整 Unicode 支援
];

物件導向風格 (推薦)

connect_errno) {
    error_log("MySQL 連接失敗: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
    die('資料庫連接失敗，請稍後再試');
}

// 設定字元集 (關鍵！防止中文亂碼)
if (!$mysqli->set_charset($cfg['charset'])) {
    error_log("字元集設定失敗: {$mysqli->error}");
}

// 成功連接
echo '資料庫連接成功！
';

// 1. 簡單查詢 (無參數、單次執行)
$sql = "SELECT id, class, score FROM scores WHERE score > 60";
if ($result = $mysqli->query($sql)) {
    echo "ID   班級   分數
";
    while ($row = $result->fetch_assoc()) {
        echo "{$row['id']}   {$row['class']}   {$row['score']}
";
    }
    $result->free(); // 釋放結果集記憶體
}

// 關閉連接
$mysqli->close();

程序式風格 (相容舊代碼)

 60");
while ($row = mysqli_fetch_assoc($result)) {
    // ...
}
mysqli_free_result($result);
mysqli_close($link);

核心進階：預處理語句 (Prepared Statements) - 防 SQL 注入必備

prepare("SELECT id, class, score FROM scores WHERE score > ? AND class = ?");
if (!$stmt) {
    die("Prepare 失敗: {$mysqli->error}");
}

// 綁定參數: 'id' = integer, 's' = string, 'd' = double, 'b' = blob
$minScore = 60;
$className = 'A班';
$stmt->bind_param('is', $minScore, $className);

$stmt->execute();
$result = $stmt->get_result(); // 需 mysqlnd 驅動

while ($row = $result->fetch_assoc()) {
    echo "{$row['id']} {$row['class']} {$row['score']}
";
}

$stmt->close();

預處理寫入 (INSERT/UPDATE/DELETE)

prepare("INSERT INTO scores (class, score, student_name) VALUES (?, ?, ?)");
$stmt->bind_param('sis', $class, $score, $name);

$data = [
    ['B班', 85, '陳七'],
    ['B班', 92, '林八'],
    ['C班', 78, '黃九'],
];

foreach ($data as $row) {
    [$class, $score, $name] = $row;
    $stmt->execute();
    echo "新增 ID: {$stmt->insert_id}
";
}
$stmt->close();

交易控制

autocommit(false); // 關閉自動提交

try {
    $mysqli->begin_transaction();
    
    // 扣款
    $stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE user_id = ? AND balance >= ?");
    $stmt->bind_param('dii', $amount, $fromUser, $amount);
    $stmt->execute();
    if ($stmt->affected_rows === 0) throw new Exception("餘額不足或用戶不存在");
    $stmt->close();
    
    // 入帳
    $stmt = $mysqli->prepare("UPDATE accounts SET balance = balance + ? WHERE user_id = ?");
    $stmt->bind_param('di', $amount, $toUser);
    $stmt->execute();
    $stmt->close();
    
    // 記錄流水
    $stmt = $mysqli->prepare("INSERT INTO transactions (from_user, to_user, amount, type) VALUES (?, ?, ?, 'TRANSFER')");
    $stmt->bind_param('iid', $fromUser, $toUser, $amount);
    $stmt->execute();
    $stmt->close();
    
    $mysqli->commit();
    echo "轉帳成功";
} catch (Exception $e) {
    $mysqli->rollback();
    error_log("轉帳失敗: {$e->getMessage()}");
    echo "操作失敗: {$e->getMessage()}";
} finally {
    $mysqli->autocommit(true);
}

錯誤處理與除錯最佳實務

set_charset('utf8mb4');
    // ...
} catch (mysqli_sql_exception $e) {
    error_log("DB Error: {$e->getMessage()} | Code: {$e->getCode()}");
    http_response_code(500);
    die('系統暫時無法使用，請稍後再試');
}

安全最佳實務清單

1. 專用資料庫帳號：僅授予必要權限 (SELECT, INSERT, UPDATE, DELETE, EXECUTE)，無 DROP/ALTER/GRANT
2. 預處理語句：所有動態參數查詢**強制使用** prepare/bind_param
3. 字元集 utf8mb4：完整 Unicode、Emoji 支援，避免截斷/注入繞過
4. 密碼不硬編碼：環境變數、配置檔 (權限 600)、密碼管理服務
5. 最小權限原則：讀寫分離帳號、僅限特定 IP 連線
6. SSL 連接：生產環境啟用 MYSQLI_CLIENT_SSL、驗證伺服器憑證
7. 錯誤資訊不外洩：生產關閉 display_errors，自定義錯誤頁面
8. 定期輪換密碼：配合密碼策略、審計日誌

常見問題排查

mysqli vs PDO 選擇建議

**單一 MySQL 專案、需異步/多結果集 → mysqli**；**多資料庫、可攜性、命名參數 → PDO**。兩者皆為現代 PHP 標準，核心原則：**永遠使用預處理語句**。