萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP MySQL 資料庫 CRUD 完整實作:連線、查詢、新增、修改、刪除

前言

資料庫 CRUD (Create, Read, Update, Delete) 是後端開發核心。本文使用現代 PDO (PHP Data Objects) 示範完整流程,包含預處理語句防 SQL 注入、例外處理、交易控制,並對比舊式 mysql_* 函數差異。

資料庫連線設定 (config.php)

<?php
return [
    'host'     => '127.0.0.1',
    'dbname'   => 'my_test',
    'user'     => 'root',
    'pass'     => '',
    'charset'  => 'utf8mb4',
    'options'  => [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,  // 使用真實預處理
    ],
];

PDO 連線單例類別

<?php
class Database {
    private static $instance = null;
    private $pdo;

    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], $config['options']);
    }

    public static function getInstance(array $config = null) {
        if (self::$instance === null) {
            if (!$config) throw new Exception('首次呼叫需提供設定陣列');
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    public function getPdo() {
        return $this->pdo;
    }

    // 禁止複製
    private function __clone() {}
    public function __wakeup() {}
}

// 使用方式
$config = require 'config.php';
$db = Database::getInstance($config)->getPdo();
?>

CRUD 完整範例 (users 表)

-- SQL 建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    gender ENUM('M','F') DEFAULT 'M',
    age TINYINT UNSIGNED DEFAULT 0,
    regdate DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. Create - 新增資料

<?php
require 'Database.php';
$db = Database::getInstance(require 'config.php')->getPdo();

$username = '張三';
$gender   = 'M';
$age      = 25;

$sql = "INSERT INTO users (username, gender, age) VALUES (?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute([$username, $gender, $age]);

$newId = $db->lastInsertId();
echo "新增成功,ID: {$newId}";
?>

2. Read - 查詢資料

<?php
// 查詢單筆
$stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();
if ($user) {
    echo "ID: {$user['id']}, 姓名: {$user['username']}, 性別: {$user['gender']}, 年齡: {$user['age']}<br>";
}

// 查詢多筆(分頁)
$page  = (int)($_GET['page'] ?? 1);
$limit = 10;
$offset = ($page - 1) * $limit;

$stmt = $db->prepare("SELECT * FROM users ORDER BY id DESC LIMIT ?, ?");
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->bindValue(2, $limit, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>
ID姓名性別年齡註冊時間";
foreach ($users as $u) {
    echo "<tr>
        <td>{$u['id']}</td>
        <td>" . htmlspecialchars($u['username']) . "</td>
        <td>{$u['gender']}</td>
        <td>{$u['age']}</td>
        <td>{$u['regdate']}</td>
    </tr>";
}
echo "</table>";
?>

3. Update - 修改資料

<?php
$id = 1;
$newData = [
    'username' => '李四',
    'gender'   => 'F',
    'age'      => 28,
];

$sql = "UPDATE users SET username = ?, gender = ?, age = ? WHERE id = ?";
$stmt = $db->prepare($sql);
$stmt->execute([$newData['username'], $newData['gender'], $newData['age'], $id]);

if ($stmt->rowCount() > 0) {
    echo "更新成功,影響 {$stmt->rowCount()} 筆";
} else {
    echo "資料未變更或 ID 不存在";
}
?>

4. Delete - 刪除資料

<?php
$id = 2;

$sql = "DELETE FROM users WHERE id = ?";
$stmt = $db->prepare($sql);
$stmt->execute([$id]);

if ($stmt->rowCount() > 0) {
    echo "刪除成功";
} else {
    echo "找不到該筆資料";
}
?>

進階:交易控制

<?php
try {
    $db->beginTransaction();

    // 扣款
    $db->prepare("UPDATE accounts SET balance = balance - 100 WHERE id = 1")->execute();
    // 存款
    $db->prepare("UPDATE accounts SET balance = balance + 100 WHERE id = 2")->execute();
    // 記錄流水
    $db->prepare("INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100)")->execute();

    $db->commit();
    echo "轉帳成功";
} catch (Exception $e) {
    $db->rollBack();
    echo "轉帳失敗: " . $e->getMessage();
}
?>

舊式 mysql_* vs PDO 對比

特性mysql_* (已移除)PDO
PHP 版本PHP 5.5 棄用,7.0 移除PHP 5.1+ 內建
預處理語句無原生支援完整支援,防 SQL 注入
資料庫支援僅 MySQL支援 12+ 種資料庫
物件導向程序式完整 OOP 介面
例外處理需手動檢查可拋出 PDOException
交易控制手動 SQLbeginTransaction/commit/rollBack

安全檢查清單

  1. ✅ 所有查詢使用 prepare() + 參數綁定
  2. ✅ 錯誤模式設為 PDO::ERRMODE_EXCEPTION
  3. ✅ 關閉預處理模擬 PDO::ATTR_EMULATE_PREPARES = false
  4. ✅ 輸出到 HTML 前用 htmlspecialchars()
  5. ✅ 敏感操作 (刪除/修改) 加入 CSRF Token 驗證
  6. ✅ 生產環境關閉錯誤顯示,改寫入日誌
copyright © 萬盛學電腦網 all rights reserved