PHP 向 MySQL 寫入資料完整指南：INSERT、UPDATE、Prepared Statement 與安全實務

前言

PHP 寫入 MySQL 是後端開發核心技能。現代最佳實務是使用 PDO Prepared Statement，兼顧安全（防 SQL 注入）、效能（預處理重用）、可維護性。本文從連線設定、INSERT、UPDATE、批次寫入、交易控制、錯誤處理，到常見錯誤與安全清單完整說明。

一、資料庫連線設定（PDO 推薦配置）

<?php
// config/db.php
$dsn = 'mysql:host=127.0.0.1;dbname=my_app;charset=utf8mb4';
$user = 'app_user';
$pass = 'strong_password';

$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) {
    error_log('DB 連線失敗：' . $e->getMessage());
    // 生產環境回傳友善錯誤，不洩露細節
    die('系統暫時無法連線資料庫，請稍後再試');
}

二、INSERT 新增資料

基本單筆插入並取得自增 ID

$sql = 'INSERT INTO `users` (`username`, `email`, `password_hash`, `created_at`)
        VALUES (?, ?, ?, NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'wang_xm',
    'wang@example.com',
    password_hash('user_password', PASSWORD_DEFAULT) // 密碼必須雜湊
]);
$newId = $pdo->lastInsertId();
echo "新增成功，ID = $newId";

批次插入（效能優化）

單次 execute 只能插一筆。批次插入有兩種做法：

方法 A：單一 SQL 多組 VALUES（適合幾百筆內）

$users = [
    ['u1', 'e1@x.com', 'hash1'],
    ['u2', 'e2@x.com', 'hash2'],
    // ...
];

$placeholders = implode(', ', array_fill(0, count($users), '(?, ?, ?)'));
$sql = "INSERT INTO `users` (`username`, `email`, `password_hash`) VALUES $placeholders";
$stmt = $pdo->prepare($sql);

// 扁平化參數陣列
$params = [];
foreach ($users as $u) $params = array_merge($params, $u);

$stmt->execute($params);
echo "批次新增 " . $stmt->rowCount() . " 筆";

方法 B：交易 + 迴圈 execute（適合大量、需驗證每筆）

$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare('INSERT INTO `users` (`username`, `email`, `password_hash`) VALUES (?, ?, ?)');
    foreach ($users as $u) {
        // 可在這裡驗證每筆
        $stmt->execute($u);
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

三、UPDATE 更新資料

基本更新

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

條件更新多欄位（動態建構 SET 子句）

function updateUser($pdo, $id, array $data) {
    $allowed = ['username', 'email', 'nickname', 'avatar', 'status'];
    $sets = [];
    $params = [];
    foreach ($data as $key => $val) {
        if (in_array($key, $allowed, true)) {
            $sets[] = "`$key` = ?";
            $params[] = $val;
        }
    }
    if (!$sets) return 0;
    $params[] = $id;
    $sql = 'UPDATE `users` SET ' . implode(', ', $sets) . ' WHERE `id` = ?';
    $stmt = $pdo->prepare($sql);
    return $stmt->execute($params);
}

// 使用
updateUser($pdo, 1001, ['email' => 'new@x.com', 'nickname' => '新暱稱']);

原子增減（避免競態條件）

// 錯誤做法：先 SELECT 再 UPDATE，高併發會少扣
// 正確：單一 SQL 原子操作
$pdo->prepare('UPDATE `wallets` SET `balance` = `balance` - ? WHERE `user_id` = ? AND `balance` >= ?')
    ->execute([100, 1001, 100]); // 餘額不足時不更新

四、交易控制

try {
    $pdo->beginTransaction();

    // 扣款
    $pdo->prepare('UPDATE `wallets` SET `balance` = `balance` - ? WHERE `user_id` = ?')
        ->execute([500, 1001]);

    // 記帳
    $pdo->prepare('INSERT INTO `transactions` (`user_id`, `amount`, `type`, `created_at`)
                   VALUES (?, ?, "withdraw", NOW())')
        ->execute([1001, 500]);

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    error_log('交易失敗：' . $e->getMessage());
    // 回傳錯誤給呼叫端
}

五、PDO vs mysqli 寫入對照

六、常見錯誤與安全清單

七、完整範例：會員註冊流程

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

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    exit('Method Not Allowed');
}

$username = trim($_POST['username'] ?? '');
$email    = trim($_POST['email'] ?? '');
$password = $_POST['password'] ?? '';

$errors = [];
if ($username === '' || strlen($username) > 30) $errors[] = '帳號長度 1-30 字元';
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) $errors[] = 'Email 格式錯誤';
if (strlen($password) < 8) $errors[] = '密碼至少 8 字元';

if ($errors) {
    echo json_encode(['ok' => false, 'errors' => $errors], JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    // 檢查重複
    $stmt = $pdo->prepare('SELECT 1 FROM `users` WHERE `username` = ? OR `email` = ?');
    $stmt->execute([$username, $email]);
    if ($stmt->fetch()) {
        echo json_encode(['ok' => false, 'errors' => ['帳號或 Email 已存在']], JSON_UNESCAPED_UNICODE);
        exit;
    }

    // 寫入
    $hash = password_hash($password, PASSWORD_DEFAULT);
    $stmt = $pdo->prepare('INSERT INTO `users` (`username`, `email`, `password_hash`, `created_at`) VALUES (?, ?, ?, NOW())');
    $stmt->execute([$username, $email, $hash]);

    echo json_encode(['ok' => true, 'id' => $pdo->lastInsertId()], JSON_UNESCAPED_UNICODE);
} catch (PDOException $e) {
    error_log('註冊失敗：' . $e->getMessage());
    echo json_encode(['ok' => false, 'errors' => ['系統錯誤，請稍後再試']], JSON_UNESCAPED_UNICODE);
}

總結

1. PDO + Prepared Statement 是寫入 MySQL 的黃金標準。
2. 密碼必須雜湊（password_hash），永不存明文。
3. 批次寫入用多 VALUES 或 交易迴圈，視量級選擇。
4. 原子操作（balance = balance - ?）避免競態條件。
5. 錯誤記錄 log，使用者只看友善提示。
6. 最小權限帳號，嚴禁 root 供應用程式使用。

遵循以上原則，即能建立安全、高效、可維護的資料寫入層。