早期 PHP 代碼常見 link_mysql($type, $sql) 這類簡單封裝。本文分析其缺陷,並演進至支援預處理、事務、異常處理、連接復用的現代數據庫類。
<?php
// 原始代碼(不推薦使用)
function link_mysql($opt, $str_sql) {
$con = mysql_connect("localhost", "root", "root") or die('Not connected: ' . mysql_error());
mysql_set_charset("gbk", $con);
mysql_select_db("website", $con);
switch ($opt) {
case "read": case "update": case "delete": case "insert":
$res = mysql_query($str_sql);
break;
}
mysql_close();
return $res;
}
?>
致命缺陷:
mysql_* 擴展(PHP 7.0+ 移除)。<?php
class Db
{
private static ?mysqli $instance = null;
private mysqli $link;
private array $config;
private bool $inTransaction = false;
private function __construct(array $config)
{
$this->config = $config;
$this->connect();
}
public static function getInstance(array $config = []): self
{
if (self::$instance === null) {
if (empty($config)) throw new \Exception('首次調用需傳入配置');
self::$instance = new self($config);
}
return self::$instance;
}
private function connect(): void
{
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$this->link = new mysqli(
$this->config['host'],
$this->config['user'],
$this->config['pass'],
$this->config['db'],
$this->config['port'] ?? 3306
);
$this->link->set_charset($this->config['charset'] ?? 'utf8mb4');
}
/** 確保連接存活 */
private function ping(): void
{
if (!$this->link->ping()) $this->connect();
}
/** 執行 SELECT,返回關聯數組數組 */
public function query(string $sql, array $params = []): array
{
$this->ping();
$stmt = $this->prepare($sql);
if ($params) $stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
return $result;
}
/** 執行 INSERT/UPDATE/DELETE,返回影響行數 */
public function execute(string $sql, array $params = []): int
{
$this->ping();
$stmt = $this->prepare($sql);
if ($params) $stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$affected = $stmt->affected_rows;
$insertId = $stmt->insert_id;
$stmt->close();
return $affected;
}
/** 獲取最後插入 ID */
public function lastInsertId(): int
{
return $this->link->insert_id;
}
/** 事務控制 */
public function beginTransaction(): void
{
$this->ping();
if ($this->inTransaction) throw new \Exception('不支持嵌套事務');
$this->link->begin_transaction();
$this->inTransaction = true;
}
public function commit(): void
{
$this->link->commit();
$this->inTransaction = false;
}
public function rollback(): void
{
$this->link->rollback();
$this->inTransaction = false;
}
/** 事務執行回調(自動提交/回滾) */
public function transactional(callable $callback): mixed
{
$this->beginTransaction();
try {
$result = $callback($this);
$this->commit();
return $result;
} catch (\Throwable $e) {
$this->rollback();
throw $e;
}
}
private function prepare(string $sql): mysqli_stmt
{
return $this->link->prepare($sql);
}
public function __destruct()
{
if ($this->link) $this->link->close();
}
// 禁止克隆/序列化
private function __clone() {}
public function __wakeup() {}
}
// ===== 使用示例 =====
$config = ['host'=>'localhost','user'=>'root','pass'=>'root','db'=>'website','charset'=>'utf8mb4'];
$db = Db::getInstance($config);
// 查詢
$users = $db->query("SELECT * FROM userinfo WHERE status = ?", [1]);
foreach ($users as $u) echo $u['username'] . "
";
// 寫入
$db->execute("INSERT INTO userinfo (username, email) VALUES (?, ?)", ['newuser', 'new@example.com']);
$id = $db->lastInsertId();
// 事務
$db->transactional(function($db) {
$db->execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$db->execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
});
?>
<?php
class PdoDb
{
private static ?PDO $instance = null;
private PDO $pdo;
private function __construct(array $config)
{
$dsn = "mysql:host={$config['host']};dbname={$config['db']};charset={$config['charset'] ?? 'utf8mb4'}";
$this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
PDO::ATTR_STRINGIFY_FETCHES => false,
]);
}
public static function getInstance(array $config = []): self { /* ... */ }
public function query(string $sql, array $params = []): array
{
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
public function execute(string $sql, array $params = []): int
{
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->rowCount();
}
public function lastInsertId(): string { return $this->pdo->lastInsertId(); }
public function transactional(callable $callback): mixed
{
$this->pdo->beginTransaction();
try {
$result = $callback($this);
$this->pdo->commit();
return $result;
} catch (\Throwable $e) {
$this->pdo->rollBack();
throw $e;
}
}
}
?>
DB::select(), DB::transaction(), Eloquent ORM,無需手寫封裝。Db::query(), Db::execute(), Db::transaction()。Db 或 PdoDb 單例模式,或引入輕量庫 illuminate/database (Laravel Database 組件) 獨立使用。拋棄 mysql_* 與簡陋封裝,擁抱 mysqli/PDO + 預處理 + 單例連接 + 事務封裝 + 異常模式,是 PHP 數據庫訪問層的專業標準。