類特性
- 單例模式管理 PDO 連線,首次使用時建立(Lazy Connect)。
- 全面採用預處理(Prepared Statement)防止 SQL 注入。
- 支援事務:
begin()/commit()/rollback()。
- 統一錯誤處理:
debug 開啟時拋出 PDOException 詳細資訊。
- 封裝常用 CRUD:
query/execute/insert/insertBatch/updateByPrimaryKey/deleteByPrimaryKey。
完整程式碼
<?php
/**
* MySQL PDO 操作類
* 依賴 Yaf_Registry 讀取配置,可根據實際框架調整
*/
class MyMysql
{
static private $dns = null;
static private $username = null;
static private $password = null;
static private $pdo = null;
public $debug = false;
private $_beginTransaction = false;
public function __construct($debug = true, $database = 'default')
{
$this->debug = $debug;
$config = Yaf_Registry::get('config')->db->$database;
self::$dns = $config->dns;
self::$username = $config->username;
self::$password = $config->password;
}
static private function instance()
{
if (is_null(self::$pdo)) {
try {
self::$pdo = new PDO(self::$dns, self::$username, self::$password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
self::$pdo->exec('SET NAMES utf8mb4');
} catch (PDOException $e) {
exit('PDOException: ' . $e->getMessage());
}
}
return self::$pdo;
}
// ====== 查詢 ======
public function query($sql, array $params = [], $fetchMode = PDO::FETCH_ASSOC)
{
self::instance();
$stmt = self::$pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll($fetchMode);
if ($this->debug) $this->error($stmt);
return $rows;
}
// ====== 增刪改(回傳影響行數) ======
public function execute($sql, array $params = [])
{
self::instance();
$stmt = self::$pdo->prepare($sql);
$stmt->execute($params);
if ($this->debug) $this->error($stmt);
return $stmt->rowCount();
}
public function exec($sql)
{
self::instance();
$rows = self::$pdo->exec($sql);
if ($this->debug) $this->error();
return $rows;
}
// ====== 單筆新增(回傳自增 ID) ======
public function insert($table, array $data)
{
self::instance();
$fields = '`' . implode('`,`', array_keys($data)) . '`';
$placeholders = ':' . implode(',:', array_keys($data));
$sql = "INSERT INTO `{$table}` ({$fields}) VALUES ({$placeholders})";
$stmt = self::$pdo->prepare($sql);
$stmt->execute($data);
if ($this->debug) $this->error($stmt);
return self::$pdo->lastInsertId();
}
// ====== 批次新增 ======
public function insertBatch($table, array $rows)
{
if (empty($rows)) return 0;
self::instance();
$fields = '`' . implode('`,`', array_keys($rows[0])) . '`';
$placeholders = [];
foreach ($rows as $i => $row) {
$ph = [];
foreach (array_keys($row) as $col) {
$ph[] = ":{$col}_{$i}";
}
$placeholders[] = '(' . implode(',', $ph) . ')';
// 重新索引參數陣列
$params = [];
foreach ($rows as $i => $row) {
foreach ($row as $col => $val) {
$params[":{$col}_{$i}"] = $val;
}
}
}
$sql = "INSERT INTO `{$table}` ({$fields}) VALUES " . implode(',', $placeholders);
$stmt = self::$pdo->prepare($sql);
$stmt->execute($params);
if ($this->debug) $this->error($stmt);
return $stmt->rowCount();
}
// ====== 依主鍵更新 ======
public function updateByPrimaryKey($table, array $where, array $data)
{
self::instance();
$set = [];
foreach ($data as $col => $val) {
$set[] = "`{$col}` = :{$col}";
}
$whereCol = key($where);
$whereVal = current($where);
$sql = "UPDATE `{$table}` SET " . implode(',', $set) . " WHERE `{$whereCol}` = :where_val";
$params = $data;
$params['where_val'] = $whereVal;
$stmt = self::$pdo->prepare($sql);
$stmt->execute($params);
if ($this->debug) $this->error($stmt);
return $stmt->rowCount();
}
// ====== 依主鍵刪除 ======
public function deleteByPrimaryKey($table, array $where)
{
self::instance();
$whereCol = key($where);
$whereVal = current($where);
$sql = "DELETE FROM `{$table}` WHERE `{$whereCol}` = :where_val";
$stmt = self::$pdo->prepare($sql);
$stmt->execute(['where_val' => $whereVal]);
if ($this->debug) $this->error($stmt);
return $stmt->rowCount();
}
public function getLastInsertId()
{
self::instance();
return self::$pdo->lastInsertId();
}
// ====== 事務控制 ======
public function begin()
{
self::instance();
if ($this->_beginTransaction) $this->rollback();
$this->_beginTransaction = self::$pdo->beginTransaction();
return $this->_beginTransaction;
}
public function commit()
{
if ($this->_beginTransaction) {
$this->_beginTransaction = false;
self::$pdo->commit();
}
return true;
}
public function rollback()
{
if ($this->_beginTransaction) {
$this->_beginTransaction = false;
self::$pdo->rollBack();
}
return false;
}
// ====== 錯誤處理 ======
private function error($stmt = null)
{
$err = $stmt ? $stmt->errorInfo() : self::$pdo->errorInfo();
$msg = "SQLSTATE:{$err[0]}";
if ($err[1]) $msg .= " - ERRORCODE:{$err[1]}";
if ($err[2]) $msg .= " - ERROR:{$err[2]}";
if ($err[1] || $err[2]) {
throw new PDOException($msg);
}
}
public function close()
{
self::$pdo = null;
}
}
使用範例
$db = new MyMysql(true, 'default');
// 查詢
$users = $db->query('SELECT * FROM users WHERE status = ? AND role = ?', [1, 'admin']);
// 新增
$id = $db->insert('users', ['name' => 'John', 'email' => 'john@example.com']);
// 批次新增
$db->insertBatch('logs', [
['user_id' => 1, 'action' => 'login'],
['user_id' => 2, 'action' => 'logout'],
]);
// 事務範例
$db->begin();
try {
$db->execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
$db->execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
$db->commit();
} catch (Exception $e) {
$db->rollback();
throw $e;
}
遷移建議
- 若非 Yaf 框架,將建構子中的配置讀取改為傳入陣列或環境變數。
- 生產環境建議關閉
debug,改用統一日誌記錄器(Monolog 等)。
- 大量寫入場景可考慮
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 或分批提交。