資料庫 CRUD (Create, Read, Update, Delete) 是後端開發核心。本文使用現代 PDO (PHP Data Objects) 示範完整流程,包含預處理語句防 SQL 注入、例外處理、交易控制,並對比舊式 mysql_* 函數差異。
<?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, // 使用真實預處理
],
];
<?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();
?>
-- 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;
<?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}";
?>
<?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>";
?>
<?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 不存在";
}
?>
<?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_* (已移除) | PDO |
|---|---|---|
| PHP 版本 | PHP 5.5 棄用,7.0 移除 | PHP 5.1+ 內建 |
| 預處理語句 | 無原生支援 | 完整支援,防 SQL 注入 |
| 資料庫支援 | 僅 MySQL | 支援 12+ 種資料庫 |
| 物件導向 | 程序式 | 完整 OOP 介面 |
| 例外處理 | 需手動檢查 | 可拋出 PDOException |
| 交易控制 | 手動 SQL | beginTransaction/commit/rollBack |
prepare() + 參數綁定PDO::ERRMODE_EXCEPTIONPDO::ATTR_EMULATE_PREPARES = falsehtmlspecialchars()