您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP MySQL 數據庫操作類：鏈式調用與分頁封裝實戰

編輯：PHP 相關知識

封裝一個功能完善、API 友好的數據庫操作類，是 PHP 開發者進階的必修課。本文深度解析一個支持鏈式調用、自動分頁、表單處理、SQL 防注入的 MySQL 操作類，並給出生產環境優化建議。

類設計核心特性

鏈式調用：$db->Table('user')->Where('id>10')->Order('id DESC')->Select()
自動分頁：內置分頁邏輯，自動計算總頁數、偏移量，生成導航 HTML
多種查詢模式：關聯數組、索引數組、單條記錄、自定義 SQL
表單數據流：_post()/_get() 自動過濾 XSS，直接映射到 INSERT/UPDATE
安全機制：參數綁定預防注入、錯誤異常處理、連接資源自動釋放

核心方法詳解

1. 連接管理與配置

public function Parameter($Loca, $Root, $Pass, $Base, $Code, $PreFix = '') {
    $this->LocalHost = $Loca;
    $this->LoaclUser = $Root;  // 注意：原代碼有拼寫錯誤 LoaclUser
    $this->LocalPass = $Pass;
    $this->LocalBase = $Base;
    $this->LocalCode = $Code;
    $this->PreFix = $PreFix;
    return $this;
}

private function Connection($Sql) {
    !function_exists('mysqli_connect') ? die('無法加載 mysqli 擴展') : null;
    $this->Conn = @new mysqli($this->LocalHost, $this->LoaclUser, $this->LocalPass, $this->LocalBase);
    $this->Error = mysqli_connect_errno() == 0;
    !$this->Error ? die('數據庫連接失敗：' . mysqli_connect_error()) : null;
    $this->Conn->query('SET NAMES ' . $this->LocalCode);
    $this->Rest = $this->Conn->query($Sql);
    $this->Err = mysqli_error($this->Conn);
    $this->Instr_ID = mysqli_insert_id($this->Conn);
    return $this;
}

改進點：建議改用單例模式管理連接，避免每次查詢重新連接；引入預處理語句替代字符串拼接。

2. 查詢構建器核心

public function Table($TableName) {
    $this->null(); // 重置狀態
    $this->Table = '`' . $this->PreFix . $TableName . '`';
    return $this;
}

public function Field($Array = '*') {
    $this->Field = '';
    $Array = explode(',', $Array);
    foreach ($Array as $field) {
        $this->Field .= '`' . trim($field) . '`,';
    }
    $this->Field = rtrim($this->Field, ',');
    return $this;
}

public function Where($Where) {
    $this->Where = ' WHERE ' . $Where;
    return $this;
}

public function Select($Type = true, $ListNum = 1) {
    $this->Select = 'SELECT ' . $this->Field . ' FROM ' . $this->Table . $this->Where . $this->Order;
    
    // 分頁邏輯
    if (is_numeric($ListNum) && $this->PageSize > 0) {
        $this->Connection($this->Select);
        $this->PageCount = $this->Rest->num_rows;
        $this->PageNum = ceil($this->PageCount / $this->PageSize);
        $this->PageNo = max(1, min($this->PageNo, $this->PageNum));
        $this->PageStart = ($this->PageNo - 1) * $this->PageSize;
        $this->Select .= " LIMIT {$this->PageStart}, {$this->PageSize}";
    } elseif (is_numeric($ListNum)) {
        $this->Select .= " LIMIT {$ListNum}";
    } else {
        $this->Select .= " LIMIT 1";
    }
    
    $this->Connection($this->Select);
    // ... 獲取結果並返回
}

3. 寫操作封裝

// 插入數據
public function Into($Mesg = '') {
    !is_array($this->FormArray) ? die($Mesg) : null;
    $fields = array_keys($this->FormArray);
    $values = array_values($this->FormArray);
    
    $fieldStr = '`' . implode('`, `', $fields) . '`';
    $valueStr = '';
    foreach ($values as $v) {
        $valueStr .= is_numeric($v) ? $v . ',' : "'" . $this->Conn->real_escape_string($v) . "',";
    }
    $valueStr = rtrim($valueStr, ',');
    
    $Sql = "INSERT INTO {$this->Table} ({$fieldStr}) VALUES ({$valueStr})";
    $this->Connection($Sql);
    return empty($this->Err);
}

// 更新數據
public function Edit($Array = array()) {
    $Array = empty($Array) ? $this->FormArray : $Array;
    if (!is_array($Array) || empty($Array)) return false;
    
    $setParts = [];
    foreach ($Array as $field => $val) {
        $val = is_numeric($val) ? $val : "'" . $this->Conn->real_escape_string($val) . "'";
        $setParts[] = "`{$field}` = {$val}";
    }
    $Sql = "UPDATE {$this->Table} SET " . implode(', ', $setParts) . $this->Where;
    $this->Connection($Sql);
    return empty($this->Err);
}

// 刪除數據
public function Del() {
    $Sql = "DELETE FROM {$this->Table} {$this->Where}";
    $this->Connection($Sql);
    return empty($this->Err);
}

4. 分頁導航生成

public function PageNav($NumNav = false) {
    $PageHtml = '<div class="pagenav">';
    $PageHtml .= '<span>' . $this->PageCount . ' 條記錄  ' . $this->PageNo . '/' . $this->PageNum . ' 頁</span> ';
    
    if ($this->PageNo > 1) {
        $PageHtml .= '<a href="' . $this->getPageUrl(1) . '">首頁</a>';
        $PageHtml .= '<a href="' . $this->getPageUrl($this->PageNo - 1) . '">上一頁</a>';
    }
    if ($NumNav) $PageHtml .= $this->NumPage($this->getPageUrl(''));
    if ($this->PageNo < $this->PageNum) {
        $PageHtml .= '<a href="' . $this->getPageUrl($this->PageNo + 1) . '">下一頁</a>';
        $PageHtml .= '<a href="' . $this->getPageUrl($this->PageNum) . '">尾頁</a>';
    }
    $PageHtml .= '</div>';
    return $PageHtml;
}

使用示例

<?php
// 初始化
$Conn = new Mysql();
$Conn->Parameter('localhost', 'root', 'password', 'dbname', 'UTF8', 'prefix_');

// 1. 簡單查詢
$users = $Conn->Table('user')->Select();

// 2. 條件查詢 + 分頁
$list = $Conn->Table('article')
    ->Where("status=1 AND cat_id=5")
    ->Order('created_at DESC')
    ->Page(20)
    ->Select();

// 3. 插入數據
$Conn->Table('user')
    ->_post('username')
    ->_post('email')
    ->_post('password')
    ->Into();

// 4. 更新數據
$Conn->Table('user')
    ->Where("id=10")
    ->Edit(['status' => 1, 'last_login' => date('Y-m-d H:i:s')]);

// 5. 獲取分頁 HTML
echo $Conn->PageNav(true);

生產環境重構建議

1. 引入 PDO 預處理語句

private function executePrepared($sql, $params = []) {
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

徹底解決 SQL 注入風險，提升重複查詢性能。

2. 連接池與持久連接

$this->pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

3. 查詢緩存層

為只讀查詢添加 Redis 緩存：

public function SelectCached($ttl = 300) {
    $cacheKey = 'sql:' . md5($this->Select);
    if ($cached = $this->redis->get($cacheKey)) return json_decode($cached, true);
    $result = $this->Select();
    $this->redis->setex($cacheKey, $ttl, json_encode($result));
    return $result;
}