PHP 操作 MySQL 基礎類（mysqli 面向物件風格）

原文使用已棄用的 mysql_* 函數（PHP 7.0 移除）。現代專案應改用 mysqli 或 PDO。本文提供一個基於 mysqli 面向物件風格的輕量封裝類，支援預處理語句、事務、錯誤處理。

Database 類別完整代碼

<?php
class Database {
    private $mysqli;
    private $host, $user, $pass, $dbname, $charset;

    public function __construct(array $config) {
        $this->host = $config['host'] ?? 'localhost';
        $this->user = $config['user'] ?? 'root';
        $this->pass = $config['pass'] ?? '';
        $this->dbname = $config['dbname'] ?? '';
        $this->charset = $config['charset'] ?? 'utf8mb4';
        $this->connect();
    }

    private function connect() {
        $this->mysqli = new mysqli($this->host, $this->user, $this->pass, $this->dbname);
        if ($this->mysqli->connect_errno) {
            throw new Exception("MySQL 連線失敗：{$this->mysqli->connect_error}");
        }
        $this->mysqli->set_charset($this->charset);
        // 開啟異常模式
        $this->mysqli->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
    }

    /** 執行 SELECT，回傳關聯陣列列表 */
    public function query(string $sql, array $params = []): array {
        $stmt = $this->prepare($sql, $params);
        $stmt->execute();
        $result = $stmt->get_result();
        $data = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
        $stmt->close();
        return $data;
    }

    /** 執行單筆 SELECT，回傳單行或 null */
    public function queryOne(string $sql, array $params = []) {
        $data = $this->query($sql, $params);
        return $data[0] ?? null;
    }

    /** 執行 INSERT/UPDATE/DELETE，回傳影響行數 */
    public function execute(string $sql, array $params = []): int {
        $stmt = $this->prepare($sql, $params);
        $stmt->execute();
        $affected = $stmt->affected_rows;
        $stmt->close();
        return $affected;
    }

    /** INSERT 並回傳自增 ID */
    public function insert(string $sql, array $params = []): int {
        $stmt = $this->prepare($sql, $params);
        $stmt->execute();
        $id = $stmt->insert_id;
        $stmt->close();
        return $id;
    }

    /** 建立預處理語句並綁定參數 */
    private function prepare(string $sql, array $params): mysqli_stmt {
        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare 失敗：{$this->mysqli->error} | SQL: $sql");
        }
        if ($params) {
            $types = $this->getTypes($params);
            $stmt->bind_param($types, ...$params);
        }
        return $stmt;
    }

    /** 根據值型別產生 bind_param 類型字串 */
    private function getTypes(array $params): string {
        $types = '';
        foreach ($params as $v) {
            if (is_int($v)) $types .= 'i';
            elseif (is_float($v)) $types .= 'd';
            elseif (is_string($v)) $types .= 's';
            else $types .= 'b'; // blob/null
        }
        return $types;
    }

    /** 事務控制 */
    public function beginTransaction() { $this->mysqli->begin_transaction(); }
    public function commit() { $this->mysqli->commit(); }
    public function rollback() { $this->mysqli->rollback(); }

    /** 關閉連線 */
    public function close() { $this->mysqli->close(); }
    public function __destruct() { $this->close(); }
}

使用範例

<?php
require 'Database.php';

$db = new Database([
    'host' => 'localhost',
    'user' => 'root',
    'pass' => '123456',
    'dbname' => 'products',
    'charset' => 'utf8mb4'
]);

// 查詢多筆
$categories = $db->query("SELECT id, name FROM category WHERE status = ?", [1]);
foreach ($categories as $row) {
    echo "ID: {$row['id']}, Name: {$row['name']}\n";
}

// 查詢單筆
$cat = $db->queryOne("SELECT * FROM category WHERE id = ?", [5]);
var_dump($cat);

// 插入
$newId = $db->insert("INSERT INTO category (name, status) VALUES (?, ?)", ['新分類', 1]);
echo "插入成功，ID: $newId\n";

// 更新
$affected = $db->execute("UPDATE category SET name = ? WHERE id = ?", ['更新分類', $newId]);
echo "更新行數: $affected\n";

// 刪除
$affected = $db->execute("DELETE FROM category WHERE id = ?", [$newId]);
echo "刪除行數: $affected\n";

// 事務範例
try {
    $db->beginTransaction();
    $db->execute("INSERT INTO logs (msg) VALUES (?)", ['操作A']);
    $db->execute("INSERT INTO logs (msg) VALUES (?)", ['操作B']);
    $db->commit();
    echo "事務提交成功";
} catch (Exception $e) {
    $db->rollback();
    echo "事務回滾: {$e->getMessage()}";
}

$db->close();

關鍵改進點

進階建議

• 生產環境建議使用連線池（如 ProxySQL）或持久連線（mysqli::real_connect with MYSQLI_CLIENT_PERSISTENT）。
• 大量資料寫入時使用 mysqli::multi_query 或批次 INSERT ... VALUES (...), (...)。
• 考慮引入成熟 ORM（Eloquent、Doctrine）或 Query Builder 進一步提升開發效率。

此類別作為輕量專案的資料庫存取層基礎，既保留 SQL 靈活性，又解決安全與錯誤處理痛點。