PHP MySQL 基礎操作完整指南：連線、查詢、新增、修改、刪除與安全實踐

雖然現代框架多封裝了 ORM/Query Builder，但理解原生 mysqli/PDO 操作對排錯、效能調優、舊專案維護仍至關重要。本文提供一份完整、安全、可直接落地的 MySQL 基礎操作範本，已棄用 老舊 mysql_* 函式，改用 mysqli 面向物件風格並採用預處理語句防止 SQL 注入。

一、資料庫連線配置

<?php
// config/db.php
return [
    'host'     => 'localhost',
    'username' => 'db_user',
    'password' => 'secure_password_123',
    'database' => 'products',
    'charset'  => 'utf8mb4',
    'port'     => 3306,
];

二、連線建立與錯誤處理

<?php
require_once 'config/db.php';
$cfg = require 'config/db.php';

$mysqli = new mysqli($cfg['host'], $cfg['username'], $cfg['password'], $cfg['database'], $cfg['port']);

if ($mysqli->connect_errno) {
    // 生產環境請記錄日誌而非直接輸出
    error_log("MySQL 連線失敗: {$mysqli->connect_error}");
    exit('資料庫連線失敗，請稍後再試');
}

$mysqli->set_charset($cfg['charset']);
// 開啟異常模式（PHP 8.1+ 預設開啟），方便 try-catch 捕獲
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

三、核心 CRUD 操作（預處理語句）

1. 查詢

// 單筆查詢
$stmt = $mysqli->prepare("SELECT id, name, price FROM `category` WHERE id = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// 列表查詢
$stmt = $mysqli->prepare("SELECT id, name FROM `category` ORDER BY id DESC LIMIT ?, ?");
$stmt->bind_param('ii', $offset, $limit);
$stmt->execute();
$list = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

2. 新增

$name = '新分類';
$stmt = $mysqli->prepare("INSERT INTO `category` (`name`) VALUES (?)");
$stmt->bind_param('s', $name);
$stmt->execute();

if ($stmt->affected_rows > 0) {
    $insertId = $stmt->insert_id; // 獲取自增 ID
    echo "新增成功，ID: $insertId";
} else {
    echo "新增失敗";
}

3. 修改

$id = 5;
$newName = '更新後名稱';
$stmt = $mysqli->prepare("UPDATE `category` SET `name` = ? WHERE `id` = ?");
$stmt->bind_param('si', $newName, $id);
$stmt->execute();

echo $stmt->affected_rows > 0 ? '修改成功' : '修改失敗或數據無變化';

4. 刪除

$id = 5;
$stmt = $mysqli->prepare("DELETE FROM `category` WHERE `id` = ?");
$stmt->bind_param('i', $id);
$stmt->execute();

echo $stmt->affected_rows > 0 ? '刪除成功' : '刪除失敗或記錄不存在';

5. 關閉連線

$stmt->close();
$mysqli->close();

四、進階最佳實踐

1. 事務處理

$mysqli->begin_transaction();
try {
    $stmt1 = $mysqli->prepare("UPDATE `account` SET balance = balance - ? WHERE id = ?");
    $stmt1->bind_param('di', 100, 1);
    $stmt1->execute();

    $stmt2 = $mysqli->prepare("UPDATE `account` SET balance = balance + ? WHERE id = ?");
    $stmt2->bind_param('di', 100, 2);
    $stmt2->execute();

    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
    error_log("轉賬失敗: {$e->getMessage()}");
}

2. 批量插入優化

$data = [['name' => 'A'], ['name' => 'B'], ['name' => 'C']];
$placeholders = implode(',', array_fill(0, count($data), '(?)'));
$sql = "INSERT INTO `category` (`name`) VALUES $placeholders";

$stmt = $mysqli->prepare($sql);
// 動態綁定參數
$types = str_repeat('s', count($data));
$names = array_column($data, 'name');
$stmt->bind_param($types, ...$names);
$stmt->execute();

3. 防注入核心原則

• 永遠不拼接 SQL 字串，所有動態值均使用 ? 佔位符。
• 識別符（表名、欄位名）不可參數化，需透過白名單驗證或 mysqli::real_escape_string 轉義後拼接（極少數場景）。
• 錯誤資訊不輸出給前端，記錄至日誌系統。

五、從 mysql_* 遷移對照表

六、總結

採用 mysqli + 預處理語句是 PHP 操作 MySQL 的現代基準。本文提供的範本涵蓋連線管理、CRUD、事務、批量操作等核心場景，並強調安全與錯誤處理規範。建議新專案直接使用 PDO（支援多資料庫）或成熟 ORM（如 Eloquent、Doctrine），但在理解底層原理、維護舊系統、編寫高效能原生查詢時，本指南仍具備極高參考價值。