重要提示:mysql_* 函數已在 PHP 7.0 移除。本文使用現代 PDO(PHP Data Objects)和 mysqli 擴展,展示安全、高效的數據庫操作最佳實踐。
<?php
// config/database.php
return [
'driver' => 'mysql',
'host' => '127.0.0.1',
'port' => 3306,
'database' => 'my_app',
'username' => 'app_user',
'password' => 'secure_password',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'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 COLLATE utf8mb4_unicode_ci",
],
];
// 連接實例
function createPdoConnection(array $config): PDO {
$dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']};charset={$config['charset']}";
return new PDO($dsn, $config['username'], $config['password'], $config['options']);
}
$pdo = createPdoConnection(require 'config/database.php');
?>
<?php
// 物件模式(推薦)
$mysqli = new mysqli('127.0.0.1', 'app_user', 'secure_password', 'my_app', 3306);
if ($mysqli->connect_errno) {
throw new \RuntimeException("連接失敗:{$mysqli->connect_error}");
}
$mysqli->set_charset('utf8mb4');
$mysqli->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
// 面向過程模式
$link = mysqli_connect('127.0.0.1', 'app_user', 'secure_password', 'my_app', 3306);
if (!$link) {
die("連接失敗:" . mysqli_connect_error());
}
mysqli_set_charset($link, 'utf8mb4');
?>
<?php
// PDO
$pdo->exec("CREATE DATABASE IF NOT EXISTS `my_app` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
echo "數據庫創建成功\n";
// mysqli
$mysqli->query("CREATE DATABASE IF NOT EXISTS `my_app` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
if ($mysqli->errno) {
echo "錯誤:{$mysqli->error}\n";
} else {
echo "數據庫創建成功\n";
}
?>
<?php
$sql = <<exec($sql);
echo "表創建成功\n";
?>
<?php
// PDO 預處理
$stmt = $pdo->prepare("
INSERT INTO `users` (`uuid`, `username`, `email`, `password_hash`)
VALUES (:uuid, :username, :email, :password_hash)
");
$uuid = 'usr_' . bin2hex(random_bytes(16));
$passwordHash = password_hash('user_password123', PASSWORD_DEFAULT);
$stmt->execute([
':uuid' => $uuid,
':username' => 'zhangsan',
':email' => 'zhang@example.com',
':password_hash' => $passwordHash,
]);
$userId = $pdo->lastInsertId();
echo "插入成功,ID:$userId\n";
// 批量插入(事務包裹)
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare("INSERT INTO `users` (`uuid`, `username`, `email`, `password_hash`) VALUES (?, ?, ?, ?)");
$users = [
['usr_' . bin2hex(random_bytes(16)), 'lisi', 'li@example.com', password_hash('pass', PASSWORD_DEFAULT)],
['usr_' . bin2hex(random_bytes(16)), 'wangwu', 'wang@example.com', password_hash('pass', PASSWORD_DEFAULT)],
];
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
echo "批量插入成功\n";
} catch (\Throwable $e) {
$pdo->rollBack();
throw $e;
}
?>
<?php
// 單條查詢
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `id` = ?");
$stmt->execute([1]);
$user = $stmt->fetch(); // FETCH_ASSOC
if ($user) {
echo "用戶:{$user['username']} ({$user['email']})\n";
}
// 多條查詢
$stmt = $pdo->query("SELECT `id`, `username`, `email` FROM `users` WHERE `status` = 1 ORDER BY `created_at` DESC LIMIT 10");
$users = $stmt->fetchAll();
foreach ($users as $user) {
echo "- {$user['username']}\n";
}
// 分頁查詢
function getUsersPaginated(PDO $pdo, int $page = 1, int $perPage = 15): array {
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `status` = 1 ORDER BY `id` DESC LIMIT ? OFFSET ?");
$stmt->bindValue(1, $perPage, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll();
}
// 計算總數
$total = $pdo->query("SELECT COUNT(*) FROM `users` WHERE `status` = 1")->fetchColumn();
?>
<?php
$stmt = $pdo->prepare("
UPDATE `users`
SET `email` = :email, `updated_at` = CURRENT_TIMESTAMP
WHERE `id` = :id
");
$affected = $stmt->execute([
':id' => 1,
':email' => 'new_email@example.com',
]);
echo "影響行數:$affected\n";
// 批量更新(CASE WHEN)
$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("
UPDATE `users`
SET `status` = CASE `id` " .
implode(' ', array_map(fn($i) => "WHEN ? THEN 0", $ids)) .
" END
WHERE `id` IN ($placeholders)
");
// 參數:先是 CASE 值,再是 WHERE 值
$params = array_merge(array_fill(0, count($ids), 0), $ids);
$stmt->execute($params);
?>
<?php
// 軟刪除(推薦)
$stmt = $pdo->prepare("UPDATE `users` SET `status` = 0, `updated_at` = CURRENT_TIMESTAMP WHERE `id` = ?");
$stmt->execute([1]);
// 硬刪除(謹慎使用)
$stmt = $pdo->prepare("DELETE FROM `users` WHERE `id` = ?");
$stmt->execute([1]);
?>
<?php
function transfer(PDO $pdo, int $fromUserId, int $toUserId, float $amount): bool {
$pdo->beginTransaction();
try {
// 檢查餘額(行鎖)
$stmt = $pdo->prepare("SELECT `balance` FROM `accounts` WHERE `user_id` = ? FOR UPDATE");
$stmt->execute([$fromUserId]);
$balance = $stmt->fetchColumn();
if ($balance < $amount) {
throw new \DomainException('餘額不足');
}
// 扣減
$pdo->prepare("UPDATE `accounts` SET `balance` = `balance` - ? WHERE `user_id` = ?")
->execute([$amount, $fromUserId]);
// 增加
$pdo->prepare("UPDATE `accounts` SET `balance` = `balance` + ? WHERE `user_id` = ?")
->execute([$amount, $toUserId]);
// 記錄流水
$pdo->prepare("INSERT INTO `transactions` (`from_user`, `to_user`, `amount`, `type`) VALUES (?, ?, ?, 'transfer')")
->execute([$fromUserId, $toUserId, $amount]);
$pdo->commit();
return true;
} catch (\Throwable $e) {
$pdo->rollBack();
error_log("轉賬失敗:{$e->getMessage()}");
return false;
}
}
?>
prepare() + execute(),絕不拼接 SQL 字符串password_hash() + password_verify(),禁用 MD5/SHA1<?php
class Database {
private PDO $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
}
public function query(string $sql, array $params = []): array {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
public function find(string $sql, array $params = []): ?array {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetch() ?: null;
}
public function execute(string $sql, array $params = []): int {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->rowCount();
}
public function insert(string $table, array $data): int {
$fields = implode('`, `', array_keys($data));
$placeholders = ':' . implode(', :', array_keys($data));
$sql = "INSERT INTO `$table` (`$fields`) VALUES ($placeholders)";
$this->pdo->prepare($sql)->execute($data);
return (int)$this->pdo->lastInsertId();
}
public function update(string $table, array $data, string $where, array $whereParams = []): int {
$set = implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($data)));
$sql = "UPDATE `$table` SET $set WHERE $where";
$params = array_merge($data, $whereParams);
$this->pdo->prepare($sql)->execute($params);
return $this->pdo->rowCount();
}
public function transaction(callable $callback): mixed {
$this->pdo->beginTransaction();
try {
$result = $callback($this);
$this->pdo->commit();
return $result;
} catch (\Throwable $e) {
$this->pdo->rollBack();
throw $e;
}
}
}
// 使用
$db = new Database($pdo);
$userId = $db->insert('users', ['username' => 'test', 'email' => 'test@test.com', 'password_hash' => 'hash']);
$user = $db->find('SELECT * FROM users WHERE id = ?', [$userId]);
?>