警告:mysql_connect、mysql_query 等函數自 PHP 5.5 棄用,PHP 7.0 完全移除。**請勿在新專案使用**。
統一介面支援多種數據庫、預處理語句防注入、異常處理、物件導向風格。
<?php
return [
'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=myapp;charset=utf8mb4',
'username' => 'db_user',
'password' => 'secure_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 COLLATE utf8mb4_unicode_ci",
],
];
<?php
class Database
{
private static ?PDO $instance = null;
public static function getInstance(): PDO
{
if (self::$instance === null) {
$config = require __DIR__ . '/config/database.php';
self::$instance = new PDO(
$config['dsn'],
$config['username'],
$config['password'],
$config['options']
);
}
return self::$instance;
}
}
<?php
$pdo = Database::getInstance();
// 單條記錄
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND status = ?');
$stmt->execute([1, 1]);
$user = $stmt->fetch(); // 關聯數組
// 多條記錄
$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE created_at > ? ORDER BY id DESC LIMIT 20');
$stmt->execute(['2024-01-01']);
$users = $stmt->fetchAll();
// 統計
$count = $pdo->query('SELECT COUNT(*) FROM users WHERE status = 1')->fetchColumn();
<?php
$pdo = Database::getInstance();
$sql = 'INSERT INTO users (username, email, password_hash, created_at) VALUES (?, ?, ?, NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute(['zhangsan', 'zhang@example.com', password_hash('secret', PASSWORD_DEFAULT)]);
$userId = $pdo->lastInsertId();
echo "新用戶 ID: $userId";
<?php
// 更新
$stmt = $pdo->prepare('UPDATE users SET email = ? WHERE id = ?');
$stmt->execute(['new@example.com', 1]);
echo "影響行數: " . $stmt->rowCount();
// 刪除 (軟刪除建議)
$stmt = $pdo->prepare('UPDATE users SET deleted_at = NOW() WHERE id = ?');
$stmt->execute([1]);
<?php
$pdo = Database::getInstance();
try {
$pdo->beginTransaction();
$pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)')->execute([1, 99.00]);
$orderId = $pdo->lastInsertId();
$pdo->prepare('INSERT INTO order_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)')
->execute([$orderId, 101, 2, 49.50]);
$pdo->prepare('UPDATE products SET stock = stock - 2 WHERE id = 101')->execute();
$pdo->commit();
echo "訂單創建成功";
} catch (Throwable $e) {
$pdo->rollBack();
error_log("訂單失敗: " . $e->getMessage());
throw $e;
}
<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'pass', 'db', 3306);
if ($mysqli->connect_errno) {
throw new Exception("連接失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');
// 預處理
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$stmt->close();
$mysqli->close();
PDO::ATTR_PERSISTENT => true),注意連接數上限| 模式 | PDO 設定 | 特點 |
|---|---|---|
| 異常模式 (推薦) | PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION | 拋出 PDOException,try-catch 統一處理,堆疊追蹤完整 |
| 警告模式 | PDO::ERRMODE_WARNING | 觸發 E_WARNING,不中斷執行,難以捕獲 |
| 靜默模式 (預設) | PDO::ERRMODE_SILENT | 需手動檢查 errorCode()/errorInfo(),易遺漏 |