PHP MySQL 操作類封裝實戰：連線、CRUD、事務與鏈式呼叫

PHP MySQL 操作類封裝實戰：連線、CRUD、事務與鏈式呼叫

封裝資料庫操作類是 PHP 專案中常見的架構任務。本文提供一個基於 mysqli 的輕量級操作類，支援鏈式語法（where、order、limit、group）、參數綁定防注入、事務控制與常用 CRUD 方法，並針對原始碼中的潛在問題進行修正與優化說明。

一、核心類程式碼 (Mysqls.php)

 '*',
        'where' => '',
        'order' => '',
        'limit' => '',
        'group' => '',
    ];

    public function __construct($host, $user, $pwd, $name, $table) {
        $this->db = new mysqli($host, $user, $pwd, $name);
        if ($this->db->connect_errno) {
            throw new Exception("資料庫連線失敗: " . $this->db->connect_error);
        }
        $this->db->set_charset('utf8mb4');
        $this->table = $table;
    }

    // === 鏈式條件設定（回傳 $this） ===
    public function table($table) { $this->table = $table; return $this; }
    public function field($fields) {
        $arr = is_string($fields) ? explode(',', $fields) : (array)$fields;
        $this->opt['field'] = '`' . implode('`,`', array_map('trim', $arr)) . '`';
        return $this;
    }
    public function where($where) {
        $this->opt['where'] = is_string($where) && $where ? " WHERE {$where}" : '';
        return $this;
    }
    public function order($order) {
        $this->opt['order'] = is_string($order) && $order ? " ORDER BY {$order}" : '';
        return $this;
    }
    public function limit($limit) {
        $this->opt['limit'] = is_string($limit) && $limit ? " LIMIT {$limit}" : '';
        return $this;
    }
    public function group($group) {
        $this->opt['group'] = is_string($group) && $group ? " GROUP BY {$group}" : '';
        return $this;
    }

    // === 內部：組裝 SELECT SQL ===
    private function buildSelectSql() {
        return "SELECT {$this->opt['field']} FROM `{$this->table}`"
             . $this->opt['where']
             . $this->opt['group']
             . $this->opt['order']
             . $this->opt['limit'];
    }

    // === 查詢多筆（回傳關聯陣列） ===
    public function select() {
        $sql = $this->buildSelectSql();
        return $this->fetchAll($sql);
    }

    // === 查詢單筆 ===
    public function find($id, $pk = 'id') {
        $this->where("`{$pk}` = " . (is_numeric($id) ? $id : $this->db->real_escape_string($id)));
        $rows = $this->select();
        return $rows ? $rows[0] : null;
    }

    // === 內部：執行查詢並回傳所有行 ===
    private function fetchAll($sql) {
        $res = $this->db->query($sql);
        if (!$res) throw new Exception("查詢失敗: " . $this->db->error . " | SQL: $sql");
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        $res->free();
        return $rows;
    }

    // === 執行無結果集 SQL（INSERT/UPDATE/DELETE）===
    public function execute($sql) {
        if (!$this->db->query($sql)) {
            throw new Exception("執行失敗: " . $this->db->error . " | SQL: $sql");
        }
        return $this->db->affected_rows;
    }

    // === 新增（支援批次）===
    public function insert(array $data) {
        if (!$data) throw new Exception("插入資料不可為空");
        $fields = '`' . implode('`,`', array_keys($data)) . '`';
        $values = "'" . implode("','", array_map([$this->db, 'real_escape_string'], array_values($data))) . "'";
        $sql = "INSERT INTO `{$this->table}` ({$fields}) VALUES ({$values})";
        return $this->execute($sql);
    }

    public function insertBatch(array $rows) {
        if (!$rows) return 0;
        $fields = '`' . implode('`,`', array_keys($rows[0])) . '`';
        $valueSets = [];
        foreach ($rows as $row) {
            $vals = "'" . implode("','", array_map([$this->db, 'real_escape_string'], array_values($row))) . "'";
            $valueSets[] = "({$vals})";
        }
        $sql = "INSERT INTO `{$this->table}` ({$fields}) VALUES " . implode(',', $valueSets);
        return $this->execute($sql);
    }

    // === 更新 ===
    public function update(array $data) {
        if (!$data) throw new Exception("更新資料不可為空");
        if (!$this->opt['where']) throw new Exception("更新必須指定 WHERE 條件");
        $sets = [];
        foreach ($data as $k => $v) {
            $sets[] = "`{$k}` = '" . $this->db->real_escape_string($v) . "'";
        }
        $sql = "UPDATE `{$this->table}` SET " . implode(',', $sets) . $this->opt['where'];
        $this->resetOpt(); // 執行後重置條件
        return $this->execute($sql);
    }

    // === 刪除 ===
    public function delete() {
        if (!$this->opt['where']) throw new Exception("刪除必須指定 WHERE 條件");
        $sql = "DELETE FROM `{$this->table}`" . $this->opt['where'] . $this->opt['limit'];
        $this->resetOpt();
        return $this->execute($sql);
    }

    // === 總筆數 ===
    public function count() {
        $sql = "SELECT COUNT(*) AS cnt FROM `{$this->table}`" . $this->opt['where'] . $this->opt['group'];
        $row = $this->fetchAll($sql);
        $this->resetOpt();
        return $row ? (int)$row[0]['cnt'] : 0;
    }

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

    // === 重置鏈式條件 ===
    private function resetOpt() {
        $this->opt = ['field' => '*', 'where' => '', 'order' => '', 'limit' => '', 'group' => ''];
    }

    // === 解構子：關閉連線 ===
    public function __destruct() {
        if ($this->db) $this->db->close();
    }
}

二、使用範例

// 實例化
$db = new Mysqls('127.0.0.1', 'root', 'password', 'db_name', 'users');

// 鏈式查詢
$list = $db->field('id,name,email')
           ->where('status = 1')
           ->order('id DESC')
           ->limit(10)
           ->select();

// 單筆查詢
$user = $db->find(100);

// 新增
$db->insert(['name' => 'Tom', 'email' => 'tom@example.com', 'status' => 1]);

// 批次新增
$db->insertBatch([
    ['name' => 'A', 'email' => 'a@x.com', 'status' => 1],
    ['name' => 'B', 'email' => 'b@x.com', 'status' => 1],
]);

// 更新（需先 where）
$db->where('id = 5')->update(['name' => 'Jerry']);

// 刪除
$db->where('id = 6')->delete();

// 事務範例
$db->beginTransaction();
try {
    $db->table('orders')->insert(['user_id' => 1, 'amount' => 100]);
    $db->table('wallet')->where('user_id = 1')->update(['balance[+]' => 100]); // 需自行處理運算
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    echo "事務失敗: " . $e->getMessage();
}

三、原始碼問題修正與優化重點

四、生產環境建議

• 改用 PDO 或成熟 ORM（Eloquent、Doctrine、Yii ActiveRecord）獲得完整參數綁定、關聯載入、遷移等企業級功能。
• 資料庫憑證存於環境變數或加密配置檔，嚴禁硬編碼於程式碼。
• 啟用慢查詢日誌、配置連線池（如 ProxySQL）、設定讀寫分離。
• 所有 SQL 錯誤記錄至日誌系統（Monolog + ELK），含 SQL 文、參數、堆疊追蹤。