基於 mysqli 繼承的輕量級 DB 類，支援預處理、字符集、異常處理

本類封裝 mysqli 原生接口，提供連接管理、預處理 CRUD、事務控制、編碼設置、版本信息等功能，適用於中小型 PHP 項目快速落地。

完整類代碼

<?php
class Mydb extends mysqli {
    protected $_databasehost = 'localhost';
    protected $_databaseuser = 'root';
    protected $_databasepass = '123456';
    protected $_databasename = 'test';
    protected $_databaseport = 3306;
    protected $_fetch_method = MYSQLI_ASSOC;
    protected $_databasechar = 'utf8mb4';

    public function __construct($host='', $user='', $pass='', $name='', $port='', $char='') {
        $this->_databasehost = $host ?: $this->_databasehost;
        $this->_databaseuser = $user ?: $this->_databaseuser;
        $this->_databasepass = $pass ?: $this->_databasepass;
        $this->_databasename = $name ?: $this->_databasename;
        $this->_databaseport = $port ?: $this->_databaseport;
        $this->_databasechar = $char ?: $this->_databasechar;

        try {
            parent::__construct($this->_databasehost, $this->_databaseuser,
                $this->_databasepass, $this->_databasename, $this->_databaseport);
            if (mysqli_connect_errno()) {
                throw new Exception("連接失敗: " . mysqli_connect_error());
            }
            if (!$this->set_charset($this->_databasechar)) {
                throw new Exception("無法設置字符集 {$this->_databasechar}");
            }
        } catch (Exception $e) {
            die($e->getMessage());
        }
    }

    public function selectDb($name) {
        $this->_databasename = $name ?: $this->_databasename;
        return $this->select_db($this->_databasename);
    }

    public function __destruct() { $this->close(); }

    public function setFetchMethod($type = MYSQLI_ASSOC) {
        $map = [MYSQLI_NUM, MYSQLI_ASSOC, MYSQLI_BOTH];
        $this->_fetch_method = in_array($type, $map) ? $type : MYSQLI_ASSOC;
    }

    // 參數轉義（支援數組）
    public function quoteInto($str) {
        if (is_array($str)) {
            foreach ($str as $k => $v) $str[$k] = $this->real_escape_string($v);
        } else {
            $str = $this->real_escape_string($str);
        }
        return $str;
    }

    // 插入：自動拼接字段與值，返回 insert_id
    public function insert($table, array $fields) {
        if (!$table || !$fields) return false;
        $cols = implode(',', array_keys($fields));
        $vals = "'" . implode("','", $this->quoteInto(array_values($fields))) . "'";
        $sql = "INSERT INTO `$table` ($cols) VALUES ($vals)";
        $this->query($sql);
        return $this->insert_id;
    }

    // 更新
    public function update($table, array $fields, $where) {
        if (!$table || !$fields || !$where) return false;
        $set = [];
        foreach ($fields as $k => $v) {
            $set[] = "`$k`='" . $this->quoteInto($v) . "'";
        }
        $sql = "UPDATE `$table` SET " . implode(',', $set) . " WHERE $where";
        return $this->query($sql);
    }

    // 查單行
    public function fetchRow($sql) {
        $res = $this->query($sql);
        return $res ? $res->fetch_array($this->_fetch_method) : null;
    }

    // 查所有
    public function fetchAll($sql) {
        $res = $this->query($sql);
        $data = [];
        while ($row = $res->fetch_array($this->_fetch_method)) $data[] = $row;
        return $data;
    }

    public function fetchFree() { return $this->free_result(); }

    public function getVersion($type = 'server') {
        return $type === 'client' ? $this->client_version : $this->server_version;
    }

    public function getInfo($type = 'server') {
        return $type === 'client' ? $this->client_info : $this->server_info;
    }
}

使用示例

$db = new Mydb('127.0.0.1', 'app_user', 'secret', 'myapp');

// 插入
$userId = $db->insert('users', ['name' => 'Alice', 'email' => 'alice@example.com']);

// 更新
$db->update('users', ['status' => 1], "id = $userId");

// 查詢
$user = $db->fetchRow("SELECT * FROM users WHERE id = $userId");
$list = $db->fetchAll("SELECT * FROM users WHERE status = 1 ORDER BY id DESC LIMIT 20");

// 事務（直接用 mysqli 原生）
$db->begin_transaction();
try {
    $db->query("INSERT INTO logs (msg) VALUES ('tx start')");
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

優化建議（生產環境必做）

1. 改用預處理：insert()/update() 現為拼接 SQL，存在注入風險。重構為 prepare() + bind_param()。
2. 連接池：PHP-FPM 下每請求新建連接，高併發建議用 Swoole/Workerman 長連接或 ProxySQL。
3. 錯誤處理：拋出異常而非 die()，統一由框架異常處理器捕獲。
4. 讀寫分離：擴展 getMaster()/getSlave() 返回不同連接實例。
5. 慢查詢日誌：類內記錄執行時間，超閾值寫入日志。

總結

此類為「教學/輕量級」可用；生產核心系統建議直接用 PDO 或成熟 ORM（Eloquent、Doctrine、Cycle ORM），獲得完整預處理、事務嵌套、遷移、實體映射能力。