摘要：環境要求先貼代碼空城空城基本使用兼容兩種寫法自動綁定與參數形式參數形式寫法優先于自動綁定自動綁定寫法初始化引入文件取別名可省略連接信息可省略可在文件內寫好配置綁定操作信息開始使用該操作最終執

環境要求:PHP7.1 PDO Extension

 * 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);    //參數說明同綁定參數

本文出自個人博客 最好的安排 轉載請注明出處!