静态PDO基础操作封装

摘要：环境要求先贴代码空城空城基本使用兼容两种写法自动绑定与参数形式参数形式写法优先于自动绑定自动绑定写法初始化引入文件取别名可省略连接信息可省略可在文件内写好配置绑定操作信息开始使用该操作最终执

环境要求: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);    //参数说明同绑定参数

本文出自个人博客 最好的安排 转载请注明出处!