PHP 操作 MySQL 基礎 CRUD 完整範例（mysqli 版）

前言

mysql_* 系列函數自 PHP 5.5 棄用、PHP 7.0 移除。現代專案請使用 mysqli (MySQL Improved) 或 PDO。本文提供 mysqli 面向物件風格的完整 CRUD 範例，涵蓋預處理語句、事務、錯誤處理等最佳實踐。

資料庫準備

CREATE DATABASE `demo` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `demo`;

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('user','admin') DEFAULT 'user',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

連線封裝類別

<?php
declare(strict_types=1);

class Database {
    private mysqli $mysqli;
    
    public function __construct(
        string $host = '127.0.0.1',
        string $user = 'root',
        string $pass = '',
        string $db   = 'demo',
        int    $port = 3306,
        string $charset = 'utf8mb4'
    ) {
        $this->mysqli = new mysqli($host, $user, $pass, $db, $port);
        if ($this->mysqli->connect_errno) {
            throw new RuntimeException("MySQL 連線失敗: {$this->mysqli->connect_error}");
        }
        $this->mysqli->set_charset($charset);
        // 開啟異常模式：錯誤自動拋出 mysqli_sql_exception
        $this->mysqli->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
    }
    
    public function getConnection(): mysqli {
        return $this->mysqli;
    }
    
    // 禁止複製
    private function __clone() {}
    public function __wakeup() {}
}

CRUD 核心操作

Create - 新增資料 (預處理語句防 SQL 注入)

<?php
$db = new Database();
$mysqli = $db->getConnection();

$username = 'john_doe';
$email = 'john@example.com';
$password = password_hash('secret123', PASSWORD_DEFAULT); // 密碼雜湊
$role = 'user';

$stmt = $mysqli->prepare("INSERT INTO `users` (`username`, `email`, `password_hash`, `role`) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssss', $username, $email, $password, $role);
$stmt->execute();

$newId = $mysqli->insert_id;
echo "新增成功，ID: $newId";
$stmt->close();

Read - 查詢資料

<?php
// 單筆查詢
$stmt = $mysqli->prepare("SELECT `id`, `username`, `email`, `role`, `created_at` FROM `users` WHERE `id` = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc(); // 關聯陣列
$stmt->close();

if ($user) {
    echo "用戶: {$user['username']} ({$user['email']})";
} else {
    echo "查無此用戶";
}

// 多筆查詢 + 分頁
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 15;
$offset = ($page - 1) * $perPage;

$stmt = $mysqli->prepare("SELECT `id`, `username`, `email`, `role` FROM `users` ORDER BY `id` DESC LIMIT ?, ?");
$stmt->bind_param('ii', $offset, $perPage);
$stmt->execute();
$users = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

foreach ($users as $u) {
    echo "{$u['username']} - {$u['email']}
";
}

Update - 更新資料

<?php
$stmt = $mysqli->prepare("UPDATE `users` SET `email` = ?, `role` = ? WHERE `id` = ?");
$stmt->bind_param('ssi', $newEmail, $newRole, $userId);

$newEmail = 'john.new@example.com';
$newRole = 'admin';
$userId = 1;

$stmt->execute();
$affected = $stmt->affected_rows;
echo "更新了 $affected 筆資料";
$stmt->close();

Delete - 刪除資料

<?php
$stmt = $mysqli->prepare("DELETE FROM `users` WHERE `id` = ?");
$stmt->bind_param('i', $deleteId);
$deleteId = 999; // 不存在的 ID
$stmt->execute();
echo "刪除 {$stmt->affected_rows} 筆";
$stmt->close();

進階：事務處理

<?php
$mysqli->begin_transaction();
try {
    // 操作 1：扣款
    $stmt = $mysqli->prepare("UPDATE `accounts` SET `balance` = `balance` - ? WHERE `user_id` = ? AND `balance` >= ?");
    $stmt->bind_param('dii', $amount, $fromId, $amount);
    $stmt->execute();
    if ($stmt->affected_rows === 0) throw new Exception("餘額不足");
    $stmt->close();
    
    // 操作 2：入帳
    $stmt = $mysqli->prepare("UPDATE `accounts` SET `balance` = `balance` + ? WHERE `user_id` = ?");
    $stmt->bind_param('di', $amount, $toId);
    $stmt->execute();
    $stmt->close();
    
    // 記錄流水
    $stmt = $mysqli->prepare("INSERT INTO `transactions` (`from_id`, `to_id`, `amount`) VALUES (?, ?, ?)");
    $stmt->bind_param('iid', $fromId, $toId, $amount);
    $stmt->execute();
    $stmt->close();
    
    $mysqli->commit();
    echo "轉帳成功";
} catch (Throwable $e) {
    $mysqli->rollback();
    echo "轉帳失敗: " . $e->getMessage();
}

錯誤處理最佳實踐

<?php
// 1. 開啟異常模式（推薦）
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli('localhost', 'user', 'pass', 'db');
    // ... 所有操作若失敗自動拋出 mysqli_sql_exception
} catch (mysqli_sql_exception $e) {
    // 記錄日誌
    error_log("DB Error [{$e->getCode()}]: {$e->getMessage()}");
    // 回應用戶友善訊息
    http_response_code(500);
    die('系統暫時無法處理請求，請稍後再試');
}

// 2. 手動檢查錯誤（不開啟異常模式時）
if ($mysqli->errno) {
    error_log("MySQL Error {$mysqli->errno}: {$mysqli->error}");
}

安全檢查清單

PDO 版本速查（同樣推薦）

<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);

// Insert
$stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
$stmt->execute([$username, $email, $hash]);
$id = $pdo->lastInsertId();

// Select
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch();

// Update
$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->execute([$newEmail, $id]);

// Delete
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$id]);

// Transaction
$pdo->beginTransaction();
try {
    // ... operations
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

總結

• 棄用 mysql_*，改用 mysqli (OO 風格) 或 PDO
• 所有變數輸入 SQL 必須用預處理語句 綁定參數
• 善用 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) 讓錯誤自動拋出異常
• 密碼永遠用 password_hash() 儲存，password_verify() 驗證
• 事務確保多步操作原子性，配合 try-catch + rollback()