寫一個“特殊”的查詢構造器 – (二、第一條語句)

MrQin發表於2019-02-16

構造、執行第一條語句

上一篇完成了程式碼結構的搭建和 PDO 的基礎封裝，這一篇我們來講如何構造一個最基本的 SQL 語句，並執行得到結果。

query sql 構造目標： SELECT * FROM test_table;

查詢構造器執行語法構造目標： $drivers->table(`test_table`)->select(`*`)->get();

測試用的資料表請大家自己建立，這裡就不單獨演示了。

我們回顧下 PDO 執行這個 query 語句的基本用法：

1、PDO::query() 方法獲取結果集：

$pdo->query("SELECT * FROM test_table;");

2、PDO::prepare()、PDOStatement::execute() 方法：

$pdoSt = $pdo->prepare("SELECT * FROM test_table;");
$pdoSt->execute();
$pdoSt->fetchAll(PDO::FETCH_ASSOC);

PDO::prepare() 方法提供了防注入、引數繫結的機制，可以指定結果集的返回格式，更加靈活易於封裝，我們選這種。

query sql 字串構造：

要構造 query sql 語句，那麼不妨先觀察一下它的基本構造：

SELECT、要查詢的欄位(列)、 FROM、要查詢的表、關聯子句、條件子句、分組子句、排序子句、 LIMIT 子句。

除了 SELECT 和 FROM 是固定不變，我們只需構造好查詢欄位、表名和一系列子句的字串，然後按照 query sql 的語法拼接在一起即可。

在基類 PDODriver.php 中新增屬性作為構造字串：

protected $_table = ``;        // table 名
protected $_prepare_sql = ``;  // prepare 方法執行的 sql 語句
protected $_cols_str = ` * `;  // 需要查詢的欄位，預設為 * (全部)
protected $_where_str = ``;    // where 子句
protected $_orderby_str = ``;  // order by 子句
protected $_groupby_str = ``;  // group by 子句
protected $_having_str = ``;   // having 子句 (配合 group by 使用)
protected $_join_str = ``;     // join 子句
protected $_limit_str = ``;    // limit 子句

基礎方法的建立

有了基本的構造字串屬性，可以開始構造一條 sql 了。

新增 _buildQuery() 方法，用來構造 sql 字串：

protected function _buildQuery()
{
    $this->_prepare_sql = `SELECT `.$this->_cols_str.` FROM `.$this->_table.
        $this->_join_str.
        $this->_where_str.
        $this->_groupby_str.$this->_having_str.
        $this->_orderby_str.
        $this->_limit_str;
}

新增 table() 方法，用來設定表名：

public function table($table)
{
    $this->_table = $table;

    return $this; // 為了鏈式呼叫，返回當前例項
}

新增 select() 方法，這裡使用可變引數靈活處理傳入：

public function select()
{
    // 獲取傳入方法的所有引數
    $cols = func_get_args();

    if( ! func_num_args() || in_array(`*`, $cols)) {
        // 如果沒有傳入引數，預設查詢全部欄位
        $this->_cols_str = ` * `;
    } else {

        $this->_cols_str = ``; // 清除預設的 * 值
        // 構造 "field1, filed2 ..." 字串
        foreach ($cols as $col) {
            $this->_cols_str .= ` `.$col.`,`;
        }
        $this->_cols_str = rtrim($this->_cols_str, `,`);
    }

    return $this;
}

構造、執行

sql 字串構造完畢，接下來就需要一個執行 sql 並取得結果的方法來收尾。

新增 get() 方法：

public function get()
{
    try {
        $this->_buildQuery();   // 構建 sql
        // prepare 預處理
        $pdoSt = $this->_pdo->prepare($this->_prepare_sql);
        // 執行
        $pdoSt->execute();
    } catch (PDOException $e) {
        throw $e;
    }

    return $pdoSt->fetchAll(PDO::FETCH_ASSOC); // 獲取一個以鍵值陣列形式的結果集
}

測試

修改 test/test.php：

require_once dirname(dirname(__FILE__)) . `/vendor/autoload.php`;

use DriversMysql;

$config = [
    `host`        => `localhost`,
    `port`        => `3306`,
    `user`        => `username`,
    `password`    => `password`,
    `dbname`      => `database`,
    `charset`     => `utf8`,
    `timezone`    => `+8:00`,
    `collection`  => `utf8_general_ci`,
    `strict`      => false,
];

$driver = new Mysql($config);
// 執行 SELECT * FROM test_table; 的查詢
$results = $driver->table(`test_table`)->select(`*`)->get();

var_dump($results);

注：上述程式碼中由於 _cols_str 屬性預設為 ` * `，所以在查詢全部欄位時省略 select() 方法的呼叫也是可以的。

之後為了節省篇幅，一些通用的方法只使用 Mysql 驅動類作為測試物件，PostgreSql 和 Sqlite 請讀者自己進行測試，之後不會再單獨說明。

優化

1、解耦

get 方法中的 prepare、execute 過程是通用的 (查詢、插入、刪除、更新等操作)，我們可以將這部分程式碼提出來，在其它執行 sql 取結果的方法中複用。

基類中新建 _execute() 方法：

protected function _execute()
{
    try {
        $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
        $this->_pdoSt->execute();

    } catch (PDOException $e) {
        throw $e;
    }
}

由於將邏輯分離到另一個方法中，get() 方法獲取不到 PDOStatement 例項，因此將 PDOStatement 例項儲存到基類的屬性中：

protected $_pdoSt = NULL;

修改後的 get() 方法：

public function get()
{
    $this->_buildQuery();
    $this->_execute();

    return $this->_pdoSt->fetchAll(PDO::FETCH_ASSOC);
}

2、引數重置

使用查詢構造器一次查詢後，各個構造字串的內容已經被修改，為了不影響下一次查詢，需要將這些構造字串恢復到初始狀態。

注：在常駐記憶體單例模式下，這種多次用一個類進行查詢的情形很常見。

新增 _reset() 方法：

protected function _reset()
{   
    $this->_table = ``;
    $this->_prepare_sql = ``;
    $this->_cols_str = ` * `;
    $this->_where_str = ``;
    $this->_orderby_str = ``;
    $this->_groupby_str = ``;
    $this->_having_str = ``;
    $this->_join_str = ``;
    $this->_limit_str = ``;
    $this->_bind_params = [];
}

修改 _execute() 方法：

protected function _execute()
{
    try {
        $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
        $this->_pdoSt->execute();
        $this->_reset(); // 每次執行 sql 後將各構造字串恢復初始狀態，保證下一次查詢的正確性
    } catch (PDOException $e) {
        throw $e;
    }

}

row() 方法

上述的 get() 方法是直接取得整個結果集。而有一些業務邏輯希望只取一行結果，那麼就需要一個 row() 方法來實現這個需求了。

row() 方法並不難，只需把 get() 方法中的 PDOStatement::fetchAll() 方法改為 PDOStatement::fetch() 方法即可：

public function row()
{
    $this->_buildQuery();
    $this->_execute();

    return $this->_pdoSt->fetch(PDO::FETCH_ASSOC);
}

這裡就不多說了，大家可以自己測試一下結果。

斷線重連

對於典型 web 環境而言，一次 sql 的查詢已經隨著 HTTP 的請求而結束，PHP 的垃圾回收功能會回收一次請求週期內的資料。而一次 HTTP 請求的時間也相對較短，基本不用考慮資料庫斷線的問題。

但在常駐記憶體的環境下，尤其是單例模式下，資料庫驅動類可能一直在記憶體中不被銷燬。如果很長時間內沒有對資料庫進行訪問的話，由資料庫驅動類建立的資料庫連線會被資料庫作為空閒連線切斷 (具體時間由資料庫設定決定)，此時如果依舊使用舊的連線物件，會出現持續報錯的問題。也就是說，我們要對資料庫斷線的情況進行處理，在檢測到斷線的同時新建一個連線代替舊的連線繼續使用。【1】

在 PDO 中，資料庫斷線後繼續訪問會相應的丟擲一個 PDOException 異常 (也可以是一個錯誤，由 PDO 的錯誤處理設定決定)。

當資料庫出現錯誤時，PDOException 例項的 errorInfo 屬性中儲存了錯誤的詳細資訊陣列，第一個元素返回 SQLSTATE error code，第二個元素是具體驅動錯誤碼，第三個元素是具體的錯誤資訊。參見 PDO::errorInfo

Mysql 斷線相關的錯誤碼有兩個：

PostgreSql 斷線相關的錯誤碼有一個：

當具體驅動錯誤碼為 7 時 PostgreSql 斷線 (此驅動錯誤碼根據 PDOException 實測得出，暫時未找到相關文件)

Sqlite 基於記憶體和檔案，不存在斷線一說，不做考慮。

這裡我們使用 PDO 的具體驅動錯誤碼作為判斷斷線的依據。

基類新增 _isTimeout() 方法：

protected function _isTimeout(PDOException $e)
{
    // 異常資訊滿足斷線條件，則返回 true
    return (
        $e->errorInfo[1] == 2006 ||   // MySQL server has gone away (CR_SERVER_GONE_ERROR)
        $e->errorInfo[1] == 2013 ||   // Lost connection to MySQL server during query (CR_SERVER_LOST)
        $e->errorInfo[1] == 7         // no connection to the server (for postgresql)
    );
}

修改 _execute() 方法，新增斷線重連功能：

protected function _execute()
{
    try {
        $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
        $this->_pdoSt->execute();
        $this->_reset();
    } catch (PDOException $e) {
        // PDO 丟擲異常，判斷是否是資料庫斷線引起
        if($this->_isTimeout($e)) { 
            // 斷線異常，清除舊的資料庫連線，重新連線
            $this->_closeConnection();
            $this->_connect();
            // 重試異常前的操作    
            try {
                $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
                $this->_pdoSt->execute();
                $this->_reset();
            } catch (PDOException $e) {
                // 還是失敗、向外丟擲異常
                throw $e;
            }
        } else {
            // 非斷線引起的異常，向外丟擲，交給外部邏輯處理
            throw $e;
        }
    }
}

順便把之前暴露的 PDO 的原生介面也支援斷線重連：

public function query($sql)
{
    try {
        return $this->_pdo->query($sql);
    } catch (PDOException $e) {
        // when time out, reconnect
        if($this->_isTimeout($e)) {

            $this->_closeConnection();
            $this->_connect();

            try {
                return $this->_pdo->query($sql);
            } catch (PDOException $e) {
                throw $e;
            }

        } else {
            throw $e;
        }
    }
}

public function exec($sql)
{
    try {
        return $this->_pdo->exec($sql);
    } catch (PDOException $e) {
        // when time out, reconnect
        if($this->_isTimeout($e)) {

            $this->_closeConnection();
            $this->_connect();

            try {
                return $this->_pdo->exec($sql);
            } catch (PDOException $e) {
                throw $e;
            }

        } else {
            throw $e;
        }
    }
}

public function prepare($sql, array $driver_options = [])
{
    try {
        return $this->_pdo->prepare($sql, $driver_options);
    } catch (PDOException $e) {
        // when time out, reconnect
        if($this->_isTimeout($e)) {

            $this->_closeConnection();
            $this->_connect();

            try {
                return $this->_pdo->prepare($sql, $driver_options);
            } catch (PDOException $e) {
                throw $e;
            }

        } else {
            throw $e;
        }
    }
}

如何模擬斷線？

在記憶體常駐模式中 (如 workerman 的 server 監聽環境下)：

訪問資料庫
重啟伺服器的資料庫軟體
再次訪問資料庫，看看是否能正常獲取資料。

Just do it

參考

【1】Workerman mysql

寫一個“特殊”的查詢構造器 – (七、DML 語句、事務)
2019-02-16
寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)
2019-02-16
一條查詢語句的執行流程
2020-07-16
Func<T,T>應用之Elasticsearch查詢語句構造器的開發
2016-12-21
Elasticsearch
一條SQL語句查詢塊分解及查詢轉換
2015-03-11
SQL
Laravel 文件閱讀：資料庫之查詢語句構造器（上篇）
2017-08-26
Laravel資料庫
SQL SERVER 條件語句的查詢
2016-05-21
SQLServer
一條 SQL 查詢語句是如何執行的？
2020-07-01
SQL
01 | 基礎架構：一條SQL查詢語句是如何執行的？
2019-03-26
架構SQL
Go語言ORM包：使用worm構造查詢條件
2022-07-18
GoWorm
請教一個mysql查詢語句！！
2021-10-22
MySql
一條SQL語句的書寫
2008-05-19
SQL
記一個實用的sql查詢語句
2019-02-07
SQL
一個connect by查詢語句的優化
2014-04-22
優化
一個經典的查詢及其SQL語句
2007-05-05
SQL
記錄一篇關於條件查詢語句的用法:1.通過QueryWrapper進行條件構造2.通過Example進行條件構造
2020-11-09
APP
Oracle分頁查詢語句（二）
2017-06-20
Oracle
面試官：MySQL 是如何執行一條查詢語句的？
2022-03-01
面試MySql
使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用
2024-06-13
SQL
ORACLE結構化查詢語句
2020-10-18
Oracle
SQL語句查詢表結構
2017-06-28
SQL
MySQL語句第二高的薪水查詢
2020-10-25
MySql
深入理解mongodb查詢條件語句
2018-11-18
MongoDB
MySQL系列之一條SQL查詢語句的執行過程
2018-11-21
MySql
MyBatis-Plus條件構造器：構建安全、高效的資料庫查詢
2024-11-04
MyBatis資料庫
理解 sole() 查詢構造器方法
2021-09-26
是的沒錯，我就是抄的。一個像 Laravel 那樣的 go語言的查詢構造器
2022-01-20
LaravelGo
一文讀懂一條 SQL 查詢語句是如何執行的
2021-06-07
SQL
react 寫的一個快遞查詢、電影頭條
2017-09-18
React
幾個定位、查詢session的sql語句
2010-08-26
SessionSQL
一條簡單SQL語句的構成及語句解析
2013-10-30
SQL
Oracle分頁查詢語句（一）
2017-06-20
Oracle
PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）
2018-08-21
SQL原始碼
C++物件模型之二構造語句 (轉)
2007-08-16
C++物件模型
es的複雜查詢測試，使用jest的dsl工具寫查詢語句
2020-12-19
Hql查詢語句
2011-12-07
mysql查詢語句
2024-04-04
MySql
從一個MysqL的例子來學習查詢語句(轉)
2007-08-16
MySql