在 PHP 開發中,資料庫操作是核心功能之一。雖然現代框架多使用 PDO 或 ORM,但理解原生 MySQL 操作類的設計有助於掌握底層邏輯。本文將重構一個經典的 PHP MySQL 操作類,涵蓋連線管理、各類查詢、錯誤處理與資源釋放,並說明關鍵方法的使用場景。
<?php
class OpMysql {
// 資料庫連線參數
private $host = 'localhost';
private $user = 'root';
private $pass = '';
private $dbName = 'test_db';
// 內部資源
private $conn = null;
private $result = null;
// 統計屬性
private $fieldCount = 0;
private $rowCount = 0;
private $fields = [];
private $rows = [];
// 建構子:初始化連線
public function __construct($host = '', $user = '', $pass = '', $dbName = '') {
if ($host) $this->host = $host;
if ($user) $this->user = $user;
if ($pass) $this->pass = $pass;
if ($dbName) $this->dbName = $dbName;
$this->connect();
}
// 建立資料庫連線
private function connect() {
$this->conn = @mysqli_connect($this->host, $this->user, $this->pass, $this->dbName);
if (!$this->conn) {
throw new Exception('資料庫連線失敗: ' . mysqli_connect_error());
}
mysqli_set_charset($this->conn, 'utf8mb4');
}
// 執行 SQL 查詢(SELECT)
public function query($sql) {
if (!$this->conn) $this->connect();
$this->result = mysqli_query($this->conn, $sql);
if (!$this->result) {
throw new Exception('查詢失敗: ' . mysqli_error($this->conn));
}
return $this->result;
}
// 取得欄位數
public function getFieldCount($sql) {
$this->query($sql);
$this->fieldCount = mysqli_num_fields($this->result);
return $this->fieldCount;
}
// 取得資料筆數
public function getRowCount($sql) {
$this->query($sql);
return mysqli_num_rows($this->result);
}
// 取得單筆資料(關聯陣列)
public function fetchOne($sql) {
$this->query($sql);
return mysqli_fetch_assoc($this->result);
}
// 取得所有資料(關聯陣列)
public function fetchAll($sql) {
$this->query($sql);
$this->rows = [];
while ($row = mysqli_fetch_assoc($this->result)) {
$this->rows[] = $row;
}
return $this->rows;
}
// 執行增、刪、改(回傳影響筆數)
public function execute($sql) {
if (!$this->conn) $this->connect();
mysqli_query($this->conn, $sql);
$affected = mysqli_affected_rows($this->conn);
if (mysqli_errno($this->conn)) {
throw new Exception('執行失敗: ' . mysqli_error($this->conn));
}
return $affected;
}
// 取得指定欄位值
public function getFieldValue($sql, $fieldIndex) {
$this->query($sql);
$row = mysqli_fetch_row($this->result);
return $row[$fieldIndex] ?? null;
}
// 取得錯誤訊息
public function getError() {
return $this->conn ? mysqli_error($this->conn) : '未連線';
}
// 釋放結果集
public function freeResult() {
if ($this->result) {
mysqli_free_result($this->result);
$this->result = null;
}
$this->fieldCount = 0;
$this->rowCount = 0;
$this->fields = [];
$this->rows = [];
}
// 關閉連線
public function close() {
$this->freeResult();
if ($this->conn) {
mysqli_close($this->conn);
$this->conn = null;
}
}
// 解構子自動關閉
public function __destruct() {
$this->close();
}
}
?>
<?php
require_once 'OpMysql.php';
try {
$db = new OpMysql('localhost', 'root', 'password', 'my_database');
// 查詢所有使用者
$users = $db->fetchAll("SELECT * FROM users ORDER BY id DESC LIMIT 10");
foreach ($users as $user) {
echo "ID: {$user['id']}, Name: {$user['name']}<br>";
}
// 新增資料
$insertSql = "INSERT INTO users (name, email) VALUES ('張三', 'zhang@example.com')";
$affected = $db->execute($insertSql);
echo "新增 {$affected} 筆資料<br>";
// 更新資料
$updateSql = "UPDATE users SET email='new@example.com' WHERE id=1";
$db->execute($updateSql);
// 刪除資料
$deleteSql = "DELETE FROM users WHERE id=2";
$db->execute($deleteSql);
} catch (Exception $e) {
echo "錯誤: " . $e->getMessage();
}
?>
| 方法 | 用途 | 回傳值 |
|---|---|---|
| query($sql) | 執行 SELECT 查詢 | mysqli_result 物件 |
| fetchOne($sql) | 取得單筆關聯陣列 | array / false |
| fetchAll($sql) | 取得所有筆數關聯陣列 | array |
| execute($sql) | 執行 INSERT/UPDATE/DELETE | 影響筆數 (int) |
| getFieldValue($sql, $index) | 取得單一欄位值 | mixed |
| getError() | 取得最後錯誤訊息 | string |
| close() | 關閉連線並釋放資源 | void |
SUM()、COUNT() 等聚合函數進行統計ORDER BY id DESC LIMIT 10, 10 實現分頁(跳過前 10 筆,取 10 筆)JOIN 關聯查詢多張表mysql_* 函數,本文已改用 mysqli_* 並加入例外處理