PHP 連接 MySQL 與查詢操作完整入門教學

前言

PHP 操作 MySQL 是後端開發基礎。雖然舊版 mysql_* 函式已於 PHP 7.0 移除，但理解其流程有助於掌握現代 PDO 與 mysqli 的核心概念。本文以 PDO 為主（推薦用法），涵蓋環境設定、連線、查詢、預處理、錯誤處理、編碼與完整範例。

一、環境準備

1. PHP.ini 啟用擴充：

   ; Windows
   extension=pdo_mysql
   extension=mysqli
   
   ; Linux/macOS 通常預設已編譯內建，若無請安裝對應套件
   # Ubuntu
   sudo apt install php-mysql php-pdo

2. 確認載入：php -m | grep -E 'pdo_mysql|mysqli' 或 phpinfo() 查看。

二、PDO 連線與基本設定

<?php
// config/db.php
$dsn = 'mysql:host=127.0.0.1;dbname=my_database;charset=utf8mb4';
$user = 'db_user';
$pass = 'db_pass';

$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",    // 設定連線編碼
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    // 生產環境勿直接輸出錯誤細節，改記錄 log
    error_log('DB 連線失敗：' . $e->getMessage());
    die('資料庫連線失敗，請稍後再試');
}

關鍵選項說明

• ERRMODE_EXCEPTION：錯誤拋出 PDOException，避免靜默失敗。
• FETCH_ASSOC：預設回傳 ['欄位名' => 值]，方便存取。
• EMULATE_PREPARES = false：強制使用 MySQL 原生預處理，防 SQL 注入更可靠。
• MYSQL_ATTR_INIT_COMMAND：連線建立時自動執行 SET NAMES utf8mb4，解決中文亂碼（含 Emoji）。

三、執行查詢：query() vs prepare()

1. 靜態查詢（無變數）：query()

$stmt = $pdo->query('SELECT * FROM `users` WHERE `status` = 1 ORDER BY `created_at` DESC');
$users = $stmt->fetchAll(); // 取得所有列
foreach ($users as $u) {
    echo "{$u['name']} - {$u['email']}
";
}

2. 動態查詢（有變數）：prepare() + execute()【必學】

$keyword = '張三';
$stmt = $pdo->prepare('SELECT * FROM `users` WHERE `name` LIKE ? ORDER BY `id` DESC');
$stmt->execute(["%{$keyword}%"]);
$results = $stmt->fetchAll();

// 或命名佔位符（可讀性佳）
$stmt = $pdo->prepare('SELECT * FROM `users` WHERE `name` LIKE :kw AND `age` > :age');
$stmt->execute([':kw' => "%{$keyword}%", ':age' => 18]);

3. 取得單筆/單一欄位

$stmt = $pdo->prepare('SELECT `email` FROM `users` WHERE `id` = ?');
$stmt->execute([1001]);
$email = $stmt->fetchColumn(); // 直接取第一欄值

// 或 fetch() 取單列
$user = $stmt->fetch(); // false 表示無資料

四、新增/修改/刪除 (CRUD)

INSERT 並取得自增 ID

$sql = 'INSERT INTO `users` (`name`, `email`, `password_hash`) VALUES (?, ?, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute(['王小明', 'wang@example.com', password_hash('secret', PASSWORD_DEFAULT)]);
$newId = $pdo->lastInsertId();
echo "新增成功，ID = $newId";

UPDATE

$sql = 'UPDATE `users` SET `email` = ? WHERE `id` = ?';
$stmt = $pdo->prepare($sql);
$affected = $stmt->execute(['new@example.com', 1001]);
echo "影響列數：$affected";

DELETE

$sql = 'DELETE FROM `users` WHERE `id` = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([1001]);

五、交易控制

try {
    $pdo->beginTransaction();

    $pdo->prepare('INSERT INTO `orders` (`user_id`, `total`) VALUES (?, ?)')->execute([1001, 1500]);
    $pdo->prepare('UPDATE `wallets` SET `balance` = `balance` - ? WHERE `user_id` = ?')->execute([1500, 1001]);

    $pdo->commit();
    echo '交易成功';
} catch (Exception $e) {
    $pdo->rollBack();
    echo '交易失敗：' . $e->getMessage();
}

六、mysqli 程序式寫法（舊專案維護參考）

注意：新專案請優先用 PDO。mysqli 物件導向風格較接近 PDO。

<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'db_name');
if ($mysqli->connect_errno) die('連線失敗：' . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');

// 查詢
$sql = 'SELECT * FROM `users` WHERE `id` = ?';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id); // i=integer, s=string, d=double, b=blob
$stmt->execute();
$result = $stmt->get_result(); // 需 mysqlnd
while ($row = $result->fetch_assoc()) {
    print_r($row);
}
$stmt->close();
$mysqli->close();

七、常見錯誤與除錯

八、完整範例：簡易會員查詢頁

<?php
// index.php
require __DIR__ . '/config/db.php';

$keyword = $_GET['q'] ?? '';
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;

$where = '';
$params = [];
if ($keyword !== '') {
    $where = 'WHERE `name` LIKE ? OR `email` LIKE ?';
    $kw = "%{$keyword}%";
    $params = [$kw, $kw];
}

// 總筆數
$countStmt = $pdo->prepare("SELECT COUNT(*) FROM `users` $where");
$countStmt->execute($params);
$total = $countStmt->fetchColumn();
$totalPages = ceil($total / $perPage);

// 分頁資料
$dataStmt = $pdo->prepare("SELECT `id`, `name`, `email`, `created_at` FROM `users` $where ORDER BY `id` DESC LIMIT ? OFFSET ?");
$bindParams = array_merge($params, [$perPage, $offset]);
$dataStmt->execute($bindParams);
$users = $dataStmt->fetchAll();
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>會員查詢</title></head><body>
<form method="get">
  搜尋：<input name="q" value="<?= htmlspecialchars($keyword) ?>">
  <button>查詢</button>
</form>
<table border="1" cellpadding="5">
  <tr><th>ID</th><th>姓名</th><th>Email</th><th>建立時間</th></tr>
  <?php foreach ($users as $u): ?>
  <tr>
    <td><?= $u['id'] ?></td>
    <td><?= htmlspecialchars($u['name']) ?></td>
    <td><?= htmlspecialchars($u['email']) ?></td>
    <td><?= $u['created_at'] ?></td>
  </tr>
  <?php endforeach; ?>
</table>
<div>
  <?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <a href="?q=<?= urlencode($keyword) ?>&page=<?= $i ?>"><?= $i ?></a>
  <?php endfor; ?>
</div>
</body></html>

總結

1. 新專案一律用 PDO，設定 ERRMODE_EXCEPTION、FETCH_ASSOC、EMULATE_PREPARES=false、charset=utf8mb4。
2. 所有動態查詢必須用預處理prepare() + execute()，杜絕 SQL 注入。
3. 大量資料用 fetch() 迴圈或分頁，避免 fetchAll() 佔滿記憶體。
4. 交易操作用 beginTransaction()/commit()/rollBack() 確保資料一致性。
5. 錯誤訊息不要直接顯示給使用者，記錄 log 並回傳友善提示。