靜態PDO基礎操作封裝

假程式設計師發表於2019-02-16
封裝

環境要求:PHP7.1 PDO Extension

先貼程式碼

<?php
/**
 * PDO FOR MySQL Extension
 * Author 空城 <694623056@qq.com>
 * Copyright 2017 空城
 * CreateDate 2017-12-14
 */

class mysql_pdo
{
    // Host address
    public static $host     = `127.0.0.1`;
    // Host port
    public static $port     = 3306;
    // Username
    public static $user     = `root`;
    // Password
    public static $pwd      = `root`;
    // Database
    public static $db       = ``;
    // Character set
    public static $charset  = `utf8mb4`;
    // Persistent connection
    public static $pconnect = true;
    // Connection object
    public static $conn     = null;
    // Table name
    public static $table    = ``;

    // Core container
    public static $data     = ``;
    public static $field    = `*`;
    public static $where    = ``;
    public static $order    = ``;
    public static $group    = ``;
    public static $limit    = ``;
    public static $join     = ``;
    public static $bind     = [];
    public static $sql      = ``;

    // Initialization
    public static function init(array $conf = array(), bool $reconnect = false):void
    {
        class_exists(`PDO`) or exit("PDO: class not exists.");
        empty($conf[`host`])  or self::$host  = $conf[`host`];
        empty($conf[`port`])  or self::$port  = $conf[`port`];
        empty($conf[`user`])  or self::$user  = $conf[`user`];
        empty($conf[`pwd`])   or self::$pwd   = $conf[`pwd`];
        empty($conf[`db`])    or self::$db    = $conf[`db`];
        empty($conf[`table`]) or self::$table = $conf[`table`];
        if (is_null(self::$conn) || $reconnect) self::$conn = self::_connect();
    }

    // Query or Exec
    public static  function do(string $sql = ``, bool $flag = false)
    {
        empty($sql) or self::$sql = $sql;
        $preg = `INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK`; 
        if (preg_match(`/^s*"?(` . $preg . `)s+/i`, self::$sql)) return self::exec(``, $flag);
        else return self::query(``, self::$sql);
    }

    // Query
    public static function query(string $sql = ``, bool $flag = false):array
    {
        $statm = self::_start($sql);
        $result = $statm->fetchAll(PDO::FETCH_ASSOC);
        return $flag ? $result[0] : $result;
    }

    // Exec
    public static function exec(string $sql = ``, bool $flag = false):int
    {
        $statm = self::_start($sql);
        $row = $statm->rowCount();
        return $flag ? self::$conn->lastInsertId() : $row;
    }

    // Insert 
    public static function insert(string $table = ``, array $data = [], bool $flag = false):int
    {

        $table = !empty($data) ? $table : self::$table;
        $data = !empty($data) ? $data : self::$data;
        $insertData = [];
        if ( count($data) == count($data, 1) ) $insertData[0] = $data;
        else $insertData = $data;
        $lastId = 0;
        $row = 0;
        foreach ($insertData as $key => $data) {
            $data = self::_format($table, $data);
            $vals = [];
            foreach ($data as $key => $value) {
                $vals[] = self::_setBind(str_replace(```, ``, $key), $value);
            }
            $keys = array_keys($data);
            
            self::$sql = `INSERT INTO `` . trim($table) . `` (` . implode(`,`, $keys) . `) VALUES(` . implode(`,`, $vals) . `)`;
            self::exec() && $flag && $row += 1;
        }
        
        $lastId = self::$conn->lastInsertId();
        unset($insertData,$data);
        return $flag ? $row : $lastId;
    }

    // Delete
    public static function del(string $table = ``, array $where = []):int
    {
        $table = !empty($data) ? $table : self::$table;
        $where = !empty($where) ? self::_where($where) : self::_where(self::$where);
        if (`` === $where) return 0;
        self::$sql = `DELETE FROM ``.trim($table).`` `.$where;
        unset($table, $where);
        return self::exec();
    }

    // Update
    public static function save(string $table = ``, array $data = [], $where = []):int
    {
        $table = !empty($table) ? $table : self::$table;
        $data = !empty($data) ? $data : self::$data;
        $where = !empty($where) ? $where : self::$where;
        if (false == $where) {
            $key = self::_tbKey($table);
            $where = [];
            foreach ($key as $k => $v) {
                empty($data[$k]) or $where[$k] = $data[$k];
            }
            $where = self::_where($where);
        } else $where = self::_where($where);
        $data = self::_format($table, $data);
        $kv = [];
        foreach ($data as $key => $value) {
            $k = str_replace(```, ``, $key);
            $k = self::_setBind($k, $value);
            $kv[] = $key.`=`.$k;
        }
        $kv_str = implode(`,`, $kv);
        self::$sql = `UPDATE ``.trim($table).`` SET `.trim($kv_str).` `.trim($where);
        unset($kv_str, $data, $kv, $table);
        if (`` === $where) return 0;
        return self::exec();
    }

    // Select
    public static function select(string $table = ``, array $opt = []):array
    {
        $opt = self::_condition($table, $opt);
        $field = $opt[`field`] = !empty($opt[`field`]) ? $opt[`field`] : self::$field;
        if (is_array($field)) {
            foreach ($field as $key => $value) $field[$key] = self::_avoidKey($value);
            $field = implode(`,`, $field);
        }
        elseif(is_string($field) && $field != ``);
        else $field = `*`;
        self::$sql = `SELECT `.$field.` FROM ``.$opt[`table`].`` `.$opt[`join`].$opt[`where`].$opt[`group`].$opt[`order`].$opt[`limit`];
        unset($opt);
        return self::query();
    }

    // Get a line
    public static function first(string $table = ``, array $opt = []):array
    {
        self::$limit = `1`;
        $result = self::select($table, $opt);
        return $result[0];
    }

    // Count
    public static function count(string $table = ``, array $opt = []):array
    {
        $option = self::_condition($table,$opt);
        return self::_common($option, `count`);
    }

    // Avg
    public static function avg(string $table = ``, array $opt = []):array
    {
        $option = self::_condition($table,$opt);
        return self::_common($option, `avg`);
    }

    // Sum
    public static function sum(string $table = ``, array $opt = []):array
    {
        $option = self::_condition($table,$opt);
        return self::_common($option, `sum`);
    }

    // Min
    public static function min(string $table = ``, array $opt = []):array
    {
        $option = self::_condition($table,$opt);
        return self::_common($option, `min`);
    }

    // Max
    public static function max(string $table = ``, array $opt = []):array
    {
        $option = self::_condition($table,$opt);
        return self::_common($option, `max`);
    }

    // Dec
    public static function dec(string $table = ``, $data = [], $where = []):int
    {
        return self::_setCol($table, $data, $where,`-`);
    }

    // Inc
    public static function inc(string $table = ``, $data = [], $where = []):int
    {
        return self::_setCol($table, $data, $where,`+`);
    }

    // Clear
    public static function clear():void
    {
        self::$data  = ``;
        self::$field = `*`;
        self::$where = ``;
        self::$order = ``;
        self::$group = ``;
        self::$limit = ``;
        self::$bind  = [];
    }

    // SetAttribute
    public static function setAttr($key, $val):bool
    {
        !empty(self::$conn) or self::_connect();
        return self::$conn->setAttribute($key, $val);
    }

    // BeginTransaction
    public static function begin():bool
    {
        !empty(self::$conn) or self::_connect();
        return self::$conn->beginTransaction();
    }

    // Commit
    public static function commit():bool
    {
        !empty(self::$conn) or self::_connect();
        return self::$conn->commit();
    }

    // RollBack
    public static function rollBack():bool
    {
        !empty(self::$conn) or self::_connect();
        return self::$conn->rollBack();
    }

    // Connect
    protected static function _connect():void
    {
        $dsn = `mysql:host=`.self::$host.`;port=`.self::$port.`;dbname=`.self::$db;
        $options = [
                PDO::MYSQL_ATTR_INIT_COMMAND => `SET NAMES ` . self::$charset,
                PDO::ATTR_PERSISTENT         => (bool)self::$pconnect
            ];
        try { 
            $dbh = new PDO($dsn, self::$user, self::$pwd, $options);
            $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $dbh->exec(`SET NAMES ` . self::$charset);
        } catch (PDOException $e) { 
            exit(`Connection failed: ` . $e->getMessage());
        }
        self::$conn = $dbh;
        unset($dsn, $dbh, $options);
    }

    // Mosaic SQL
    protected static function _condition(string $table, array $opt):array
    {
        $option = [];
        $option[`table`] = !empty($table) ? $table : self::$table;
        $option[`field`] = !empty($opt[`field`]) ? $opt[`field`] : self::$field;
        $option[`join`] = !empty($opt[`join`]) ? self::_join($opt[`join`]) :self::_join(self::$join);
        $option[`where`] = !empty($opt[`where`]) ? self::_where($opt[`where`]) : self::_where(self::$where);
        $option[`order`] = !empty($opt[`order`]) ? self::_order($opt[`order`]) : self::_order(self::$order);
        $option[`group`] = !empty($opt[`group`]) ? self::_group($opt[`group`]) : self::_group(self::$group);
        $option[`limit`] = !empty($opt[`limit`]) ? self::_limit($opt[`limit`]) : self::_limit(self::$limit);
        return $option;
    }

    // Exec SQL common function
    protected static function _start(string $sql = ``)
    {
        empty($sql) or self::$sql = $sql;
        !empty(self::$conn) or self::_connect();
        $statm = self::$conn->prepare(self::$sql);
        $statm->execute(self::$bind);
        self::clear();
        return $statm;
    }

    // Common
    protected static function _common(array $opt, string $func):array
    {
        if (is_string($opt[`field`]) && $opt[`field`] != "") {
            $strField = $opt[`field`];
            $fieldArr = explode(",", $strField);
            $strField = `_`.implode("_,_", $fieldArr).`_`;
        } elseif (is_array($opt[`field`])) {
            $fieldArr = $opt[`field`];
            $strField = `_`.implode("_,_", $opt[`field`]).`_`;
        } else return false;

        foreach ($fieldArr as $v) {
            $val = self::_avoidKey($v);
            $alias = str_replace(`.`, `_`, $val);
            $alias = ` AS `.(false === strpos($val, `*`) ? $alias : ```. $alias .```);
            $strField = str_replace(`_`.$v.`_`, $func . `(` . $val . `) `.$alias, $strField);
        }
        self::$sql = `SELECT `.$strField.` FROM ``.$opt[`table`].`` `.$opt[`join`].$opt[`where`].$opt[`group`].$opt[`order`].$opt[`limit`];
        unset($opt, $func, $fieldArr, $strField, $alias);
        $result = self::query();
        return count($result) == 1 && !empty($result[0]) ? $result[0] : $result;
    }

    // Set field
    protected static function _setCol(string $table = ``, $data = ``, $where = [], string $type):int
    {
        $table = !empty($table) ? $table : self::$table;
        $data  = !empty($data) ? $data : self::$data;
        $where = !empty($where) ? self::_where($where) : self::_where(self::$where);

        if (is_array($data)) {
            $new_data = [];
            foreach ($data as $key => $value) {
                if (is_string($key)) $new_data[$key] = $key.$type.abs($value);
                else $new_data[$value] = $value.$type.`1`;
            }
        } elseif (is_string($data)) $new_data[$data] = $data.$type.`1`;
        $kv = [];
        foreach ($new_data as $key => $value) {
            $kv[] = self::_avoidKey($key).`=`.$value;
        }
        $kv_str = implode(`,`, $kv);
        self::$sql = `UPDATE ``.trim($table).`` SET `.trim($kv_str).` `.trim($where);
        unset($data);
        if (`` === $where) return 0;
        return self::exec();
    }

    // Preprocessing
    protected static function _setBind(string $key, $value):string
    {
        if (empty(self::$bind[`:`.$key])) {
            $k = `:`.$key;
            self::$bind[$k] = $value;
        } else {
            $k = `:`.$key.`_`.mt_rand(1,9999);
            while (!empty(self::$bind[`:`.$k])) {
                $k = `:`.$key.`_`.mt_rand(1,9999);
            }  
            self::$bind[$k] = $value;
        }
        unset($key, $value);
        return $k;
    }

    // Join
    protected static function _join($opt):string
    {
        $join = ``;
        if (is_string($opt) && `` !== trim($opt)) return $opt;
        elseif (is_array($opt)) {
            foreach($opt as $key => $value) {
                $mode = `INNER`;
                if (is_array($value)) {
                    if (!empty($value[2]) && 0 === strcasecmp($value[2], `LEFT`)) $mode = `LEFT`;
                    elseif (!empty($value[2]) && 0 === strcasecmp($value[2], `RIGHT`)) $mode = `RIGHT`;
                    $relative = !empty($value[3]) ? $value[3] : `=`;
                    $condition = ` `.$mode.` JOIN `.$key.` ON `.self::_avoidKey($value[0]).$relative.self::_avoidKey($value[1]).` `;
                } else {
                    $condition = ` `.$mode.` JOIN `.$key.` ON `.$value.` `;
                }
                $join .= $condition;
            }
        }
        unset($opt);
        return $join;
    }

    // Where
    protected static function _where($opt):string
    {
        $where = ``;
        if (is_string($opt) && `` !== trim($opt)) return ` WHERE `.$opt;
        elseif (is_array($opt)) {
            foreach($opt as $key => $value) {
                $k = self::_avoidKey($key);
                if (is_array($value)) {
                    $key = self::_setBind($key,$value[0]);
                    $relative = !empty($value[1]) ? $value[1] : `=`;
                    $link    = !empty($value[2]) ? $value[2] : `AND`;
                    $condition = ` (`.$k.` `.$relative.` `.$key.`) `;
                } else {
                    $key = self::_setBind($key,$value);
                    $link = `AND`;
                    $condition = ` (`.$k.`=`.$key.`) `;
                }
                $where .= $where !== `` ? $link.$condition : ` WHERE `.$condition;
            }
        }
        unset($opt);
        return $where;
    }

    // Order
    protected static function _order($opt):string
    {
        $order = ``;
        if (is_string($opt) && `` !== trim($opt)) return ` ORDER BY `._avoidKey($opt);
        elseif (is_array($opt)) {
            foreach($opt as $key => $value) {
                $link = `,`;
                if (is_string($key)) {
                    if (0 === strcasecmp($value, `DESC`)) $condition = ` `.self::_avoidKey($key).` DESC `;
                    else $condition = ` `.self::_avoidKey($key).` ASC `;
                }  else $condition = ` `.self::_avoidKey($value).` ASC `;
                $order .= $order !== `` ? $link.addslashes($condition) : ` ORDER BY `.addslashes($condition);
            }
        }
        unset($opt);
        return $order;
    }

    // Limit
    protected static function _limit($opt):string
    {
        $limit = ``;
        if (is_string($opt) && `` !== trim($opt)) return ` LIMIT `.$opt;
        elseif (is_array($opt) && 2 == count($opt)) $limit = ` LIMIT `.(int)$opt[0].`,`.(int)$opt[1];
        elseif (is_array($opt) && 1 == count($opt)) $limit = ` LIMIT `.(int)$opt[0];
        unset($opt);
        return $limit;
    }

    // Group
    protected static function _group($opt):string
    {
        $group = ``;
        if (is_string($opt) && `` !== trim($opt)) return ` GROUP BY `._avoidKey($opt);
        elseif (is_array($opt)) {
            foreach($opt as $key => $value) {
                $link = `,`;
                $condition = ` `.self::_avoidKey($value).` `;
                $group .= $group !== `` ? $link.addslashes($condition) : ` GROUP BY `.addslashes($condition);
            }
        }
        unset($opt);
        return $group;
    }

    // Format data
    protected static function _format(string $table, $data):array
    {
        if (!is_array($data)) return array();

        $tbColumn = self::_tbInfo($table);
        $res = [];
        foreach ($data as $key => $val) {
            if (!is_scalar($val)) continue;
            if (!empty($tbColumn[$key])) {
                $key = self::_avoidKey($key);
                if (is_int($val)) $val = intval($val);
                elseif (is_float($val)) $val = floatval($val); 
                elseif (preg_match(`/^(w*(+|-|*|/)?w*)$/i`, $val)) $val = $val;
                elseif (is_string($val)) $val = addslashes($val);
                $res[$key] = $val;
            }
        }
        unset($data);
        return $res;
    }

    // Table info
    protected static function _tbInfo(string $table = ``):array
    {
        $table = !empty($table) ? $table : self::$table;
        $sql = `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="`.$table.`" AND TABLE_SCHEMA="`.self::$db.`"`;
        !empty(self::$conn) or self::_connect();
        $statm = self::$conn->prepare($sql);
        $statm->execute();
        $result = $statm->fetchAll(PDO::FETCH_ASSOC);
        $res = [];
        foreach ($result as $key=>$value) {
            $res[$value[`COLUMN_NAME`]] = 1;
        }
        // unset($result, $statm);
        return $res;
    }

    // Get primary key
    protected static function _tbKey(string $table):array
    {
        $sql = `SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name,table_schema,table_name) WHERE t.constraint_type="PRIMARY KEY" AND t.table_schema="`.self::$db.`" AND t.table_name="`.$table.`"`;
        !empty(self::$conn) or self::_connect();
        $statm = self::$conn->prepare($sql);
        $statm->execute();
        $result = $statm->fetchAll(PDO::FETCH_ASSOC);
        $res = [];
        foreach ($result as $key=>$value) {
            $res[$value[`column_name`]] = 1;
        }
        unset($result, $statm);
        return $res;
    }

    // Avoid mistakes
    protected static function _avoidKey(string $value):string
    { 
        if (`*` == $value || false !== strpos($value,`(`) || false !== strpos($value,`.`));
        elseif (false === strpos($value,```)) $value = ```.trim($value).```;
        return $value; 
    }
}

基本使用

相容兩種寫法,自動繫結與引數形式,引數形式寫法優先於自動繫結

自動繫結寫法:

Example-1:初始化

// step_1:引入檔案
require_once `mysql.php`;
// 取別名(可省略)
use mysql_pdo as db;
// 連線資訊(可省略,可在檔案內寫好配置)
db::$host = `127.0.0.1`;
db::$user = `root`;
db::$pwd = `root`;
db::$db = `test`;
// step_2:繫結操作資訊
db::$tbale = `tablename`;
// step_3:開始使用
db::select();  // 該操作最終執行的語句是:SELECT * FROM `tablename`

Example-2:Insert操作

// 繫結操作表
db::$table = `tablename`;
// 繫結資料,如需插入多行資料,構建的資料為二維陣列即可
db::$data = [
    `col_1` => `value_1`,
    `col_2` => `value_2`,
    `col_3` => `value_3`,
    `col_4` => `value_4`
];
// 執行
db::insert();  
// 返回值為最後插入的ID,如果要獲取插入的行數請使用傳參寫法 db::insert(`tablename`,$data,true) 引數形式無須繫結引數

Example-3:Delete操作

// 操作表,如果每次都對同一張表進行操作,則不需要重新繫結
db::$table = `tablename`;
// 繫結where條件,防止刪表操作,在沒有where條件下不會執行delete操作
db::$where = [`id` => 1] // 陣列形式一 最終解析:[AND] `id` = 1 預設對比關係為 =,邏輯 AND
db::$where = [`id` => [`1`,`>`,`AND`]] // 陣列形式二 最終解析:[AND] `id` > 1
db::$where = `id = 1`  //字串形式 最終解析為: WHERE id = 1
// 執行
db::del();

Example-4:Update操作

 // 在繫結表的基礎上繫結資料即可
db::$data = [
    `col_1` => `value_1`,
    `col_2` => `update_2`,
    `col_3` => `update_3`,
    `col_4` => `update_4`
];
// 當上述上中col_1為主鍵時,value_1,將自動當WHERE條件,解析為: WHERE `col_1` = value_1,至少要存在一個主鍵

// 更新
db::save();
// 如果繫結$where條件,則上述主鍵效果將消失
db::$where = [opt...];  // 值的寫法同上

Example-5:Select操作

// join表,繫結操作表與where繫結不再演示
db::$join = [`table_b`=>[`table_a.id`,`table_b.aid`,`INNER`,`=`]];
// 一維陣列中的鍵名為連線表的表名,二維陣列中前兩個值為連線條件-必填項,第三個預設為INNER,第四個預設為=

db::$join = [`table_b`=>`LEFT JOIN table_b ON table_a.id=table_b.aid`];
//當值為一個字串時,將直接拼接

// 分組查詢 
db::$group = [`sex`,`age`];  // 值為要分組的列名字即可
db::$group = `sex`           // 值為字串時直接拼接為 GROUP BY sex

// 排序
db::$order = [`id` => `DESC`];  // 關聯陣列將解析成 ORDER BY `id` DESC
db::$order = [`id`];              // 索引陣列將解析成 ORDER BY `id` ASC,預設為ASC排序
db::$order = `id`;              // 字串將解析成 ORDER BY id 自動拼接

// 分頁
db::$limit = [0,10]  // 存在兩個值時將解析為 LIMIT 0,10;
db::$limit = [1]     // 存在一個值時將解析為 LIMIT 1;
db::$limit = `0,20`  // 字串將解析為 LIMIT 0,20;

// 查詢
db::select();  // 多條
db::first();   // 第一條

Example-6:聚合函式

db::count(); // count

db::sum();   // sum

db::avg();   // avg

db::min();   // min

db::max();   // max

Example-7:附加1

db::begin();              // 開啟事務

db::commit();              // 提交事務

db::rollBack();          // 回滾事務

db::setAttr($key, $val); // 設定PDO屬性

db::dec();                  // 欄位自增,需繫結資料 db::$data = [`age`=>2]該自增步長為2,省略則預設為1

db::inc();                  // 欄位自增,需繫結資料 db::$data = [`age`=>2]該自減步長為2,省略則預設為1

db::do($sql, [$flag]);      //執行一條SQL語句,不分查詢還是執行,自動分配.第二個引數同下

db::query($sql,[$flag]); //執行一條有結果集的SQL語句, $flag 預設為false,為true是返回一條記錄

db::exec($sql, [$flag]); //執行一條無結果集的SQL語句, $flag 預設為false,為true時返回insert語句的插入的ID,其他語句無效

// 執行拼接SQL可使用預處理繫結引數形式,然後繫結$bind即可
$sql = `UPDATE `tablename` SET col_a=:col_a WHERE id=:id`;
db::$bind = [`:col_a` => `update_a`,`:id`=>1];
db::do($sql); //或者 db::exec($sql);

db::clear() // 該方法將重置$data,$field,$where,$order,$group,$limit,$join,$bind資料,預設每執行一次語句將自動重置

db::init([conf...], $reconnect = false); // conf陣列的值可為資料庫基本配置(host,user,pwd等) $reconnext為true是將重新連線mysql

傳參寫法

Example-8:附加2

// insert
db::inster(`tablename`,[data...],$where);

// delete
db::del(`tablename`, $where);

// update
db::save(`tablename`, [data...], $where);

// select
db::select(`tablename`,[opt..]);  //opt引數可傳入field,where,join,order,group,limit等資訊

// first
db::first(`tablename`,[opt..]);   //opt引數同上

// avg
db::avg(`tablename`,[opt..]);        //opt引數同上

// count
db::count(`tablename`,[opt..]);   //opt引數同上

// sum
db::sum(`tablename`,[opt..]);     //opt引數同上

// min
db::min(`tablename`,[opt..]);     //opt引數同上

// max
db::max(`tablename`,[opt..]);     //opt引數同上

// dec
db::dec(`tablename`,[data..],$where);    //引數說明同繫結引數

// inc
db::inc(`tablename`,[data..],$where);    //引數說明同繫結引數

本文出自個人部落格 最好的安排 轉載請註明出處!

相關文章