php pdo mysql数据库操作类

<?php
namespace iphp\core;
use iphp\App;
/**
 * 数据库操作基类 基于pdo
 * @author xuen
 *    支持链式操作，支持参数绑定
 *　说明1　只有在绑定了参数后，pdo才会自动处理单引号。
 *  说明2　关闭连接时，要设置pdo null
 *  说明3　在脚本处理完后，最好手动关闭连接。不然mysql并不一定会关闭连接。
 *@example
 *            $res=$db->findAll(array(
                    'field'=>'ID,albumName,albumImage,
                            mainActor,directors,tags,info,area,
                            keywords,wflag,year,mod_version,totalDuration',
                    'where'=>$where,
                    'order'=>'flag desc,hit_count desc',
                    'limit'=>"{$limit['offset']},{$limit['pagesize']}"
            ));
            $res=$db->field('ID,stitle,jisu,liuchang,gaoqing,chaoqing, 
                    totalDuration,bTime,eTime')
                    ->where($where)
                    ->limit("{$limit['offset']},{$limit['pagesize']}")
                    ->findAll();    
 */
final class MyDb
{
    private $pdo;
    public $tableName;//表名
    private $sql;//当前执行的SQL语句
    public $error='';//当前执行sql的错误消息 
    public $prefix='';//前缀
    public $charset='utf8';
    public $sqlQuery=array(
            'field'=>'*',
            'where'=>'1',
            'join'=>'',
            'group'=>'',
            'having'=>'',
            'order'=>'',
            'limit'=>'',
            'union'=>'',
            'params'=>array()
    );
    public $fields=array();//得到当前表所有的字段名称
    
    private static $_instance=array();
    
    protected $dbName;
    
    //用户名
    protected $username;
    
    private $k;
    //密码
    protected $password;
    
    //主机名
    protected $host;
    
    //端口号
    protected $port;
    
    //一个时间戳，表示当前链接在什么时候过期，过期后，将重建一个对象。
    protected $expireTime;
    
    //PDO链接属性数组
    protected $attr=array(
        //这个超时参数，实际上mysql服务器上的配置为准的。这里用于什么时候重建对象
        //说明如是设置了这个参数，如果不显式的将pdo设为null，可能造成连接资源在mysql上不被释放。
        \PDO::ATTR_TIMEOUT=>30,
        \PDO::ATTR_ERRMODE=>\PDO::ERRMODE_SILENT,
        \PDO::ATTR_ORACLE_NULLS=>\PDO::NULL_NATURAL,
        //如果设置了这个参数为true,inndob，需要手动commit
        //\PDO::ATTR_AUTOCOMMIT=>false,
        \PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_ASSOC,
        \PDO::ATTR_PERSISTENT=>FALSE
    );
    
    /**
     * 构造方法
     * @param array $config　配置文件
     * @param array $attr 数组选项  
     */
    private  function __construct($config,$attr)
    {
        //索引数组合并不能使用merge
        foreach ($this->attr as $key=>$row)
        {
            if(isset($attr[$key]))
                $this->attr[$key]=$attr[$key];
        }
        $this->pdo=new \PDO($config['dsn'], $config['username'], 
            $config['password'],$this->attr);
        
        if(isset($config['prefix']) && $config['prefix']!='')
            $this->prefix=$config['prefix'];
        if(isset($config['charset']) && $config['charset']!='')
            $this->charset=$config['charset'];
        $this->pdo->exec("set names {$this->charset}");
        
        //保存当前数据库名称，主机，端口。
        preg_match('/dbname=(\w+)/', $config['dsn'],$ma);
        preg_match('/host=(.*?);/', $config['dsn'],$ma1);
        preg_match('/port=(\w+)/', $config['dsn'],$ma2);
        $this->dbName=$ma[1];
        $this->host=$ma1[1];
        $this->port=$ma2[1]?$ma2[1]:3306;
    
        $this->username=$config['username'];
        $this->password=$config['password'];
        
        //设置链接过期时间
        $timeout=$this->attr[\PDO::ATTR_TIMEOUT];
        $this->expireTime=time()+$timeout;
    }
    
    private function __clone(){}

    /**
     * @param $config
     * @param array $attr
     * @return \iphp\core\MyDb
     */
    static public function  getInstance($config,$attr=array())
    {
        if(!is_array($config))
            $config=App::getApp()->getConfig($config);
        $k=md5(implode('', $config));       
         
        //如果连接没有创建，或者连接已经失效
        if( !(static::$_instance[$k] instanceof self))
        {
            static::$_instance[$k]=new self($config,$attr);    
            static::$_instance[$k]->k=$k;
        }
        //如果连接超时。
        elseif(time()>static::$_instance[$k]->expireTime)
        {
            static::$_instance[$k]->close();
            static::$_instance[$k]=new self($config,$attr);
            static::$_instance[$k]->k=$k;
        }        
        return static::$_instance[$k];
    }
    /**
     * 
     * @param unknown_type $tableName
     * @return $this
     */
    public function tableName($tableName)
    {
        $this->tableName=$this->prefix.$tableName;
        //$this->setField();
        return $this;
    }

    /**
     * @return \PDO
     */
    public function getPdo()
    {
        return $this->pdo;
    }
    /**
     * 得到当前sql语句，并完成参数替换
     * @return string
     */
    public function getSql()
    {
        return $this->sql;
    }

    /**
     * @param string $sql
     * @return bool
     */
    public function findAll($sql='')
    {
        $stmt=$this->query($sql);
        if(!$stmt)
            return false;
        return $stmt->fetchAll();
    }

    /**
     * @param string $sql
     * @return bool
     */
    public function findOne($sql='')
    {
        $this->sqlQuery['limit']=1;
        $stmt=$this->query($sql);
        if($stmt===false)
            return false;
        return $stmt->fetch();
    }
    /**
     * 根据主键查找记录
     * @param mixed $ids 
     * @return 返回一维或二维数组
     */
    public function find($ids)
    {
        $num=count(explode(',', $ids));
        $this->setField();
        if($num==1)
        {
            $res= $this->where("{$this->fields[$this->tableName]['pk']}='{$ids}'")
            ->findOne();
        }
        else
        {
            //如果是字符串主键，要加引号
            $tmp=explode(',', $ids);
            $tmp=array_map(function($item){
                return "'".$item."'";
            }, $tmp);
            $ids=implode(',', $tmp);
            $res= $this->where("{$this->fields[$this->tableName]['pk']} in ({$ids})")
            ->findAll();
        }
        return $res;
    }

    /**
     * 插入数据的方法，自动完成参数绑定
     * @param  array $data 一维数组 array(Field=>value)
     * @return boolean | int
     */
    public function insert($data)
    {
        $this->setField();
        $params=array();
        $field=array();
        $placeholder=array();
        foreach($data as $key=>$row)
        {
            //删除非法字段信息
            if(!in_array($key, $this->fields[$this->tableName]))
                continue;
            $params[':'.$key]=$row;
            $field[]=$key;
            $placeholder[]=':'.$key;
        }
        //插入当前记录
        $sql="insert into {$this->tableName} (".implode(', ', $field).') values ('.
            implode(', ', $placeholder).')';
        $this->sqlQuery['params']=$params;
        $this->sql=$sql;
        return $this->exec($sql,$this->sqlQuery['params']);
    }
    /**
     * 删除记录
     * @param string $where where条件
     * @param array $params 绑定参数
     * @return bool
     */
    public function delete($where = '',$params = array())
    {
        if($where!='')
            $this->sqlQuery['where']=$where;
        if($params!='')
            $this->sqlQuery['params']=$params;
        $sql="delete from {$this->tableName} where {$this->sqlQuery['where']}";
        $this->sql=$sql;
        return $this->exec($sql,$this->sqlQuery['params']);
    }

    /**
     * 简化的delete()方法，基于主键的删除
     */
    public function del($ids)
    {
        $this->setField();
        $tmp=explode(',', $ids);
        $tmp=array_map(function($item){
            return "'".$item."'";
        }, $tmp);
        $ids=implode(',', $tmp);
        $sql="delete from {$this->tableName} where {$this->fields[$this->tableName]['pk']}".
        " in ($ids)";
        $this->sql=$sql;
        return $this->exec($sql);
    }

    /**
     * 得到插入的最后ID号
     */
    public function lastId()
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * 修改数据 update 支持参数绑定 只支持where参数
     * @param array $data 要改变的列的值数组 array(列名=>值)
     * @param  string $where where条件
     * @param  array $params 绑定参数
     * @return boolean ｜ int 受影响的行数
      */
    public function update($data,$where='',$params= array())
    {
        $this->setField();
        if(!is_array($data))
            return false;
        if($where!='')
            $this->sqlQuery['where']=$where;
        if($params!='')
            $this->sqlQuery['params']=$params;
        $updateField=array();
        foreach($data as $key=>$value)
        {
            //不合法的字段不要
            if(!in_array($key, $this->fields[$this->tableName]))
                continue;
            $updateField[]="{$key}=:{$key}";
            $this->sqlQuery['params'][":{$key}"]=$value;
        }
        $sql="update {$this->tableName} set ".implode(',', $updateField)
            ." where {$this->sqlQuery['where']}";
        $this->sql=$sql;
        return $this->exec($sql,$this->sqlQuery['params']);
    }

    /**
     * 得到数据表的所有字段信息
     */
    public function setField()
    {
        if(is_array($this->fields[$this->tableName]))
            return;
        $sql="desc {$this->tableName} ";
        $res=$this->findAll($sql);
        foreach ($res as $row)
        {
            if($row['Key']=='PRI')
                $this->fields[$this->tableName]['pk']=$row['Field'];
            $this->fields[$this->tableName][]=$row['Field'];
        }
    }

    //得到当前操作表的字段信息
    public function getField()
    {
        if(!$this->fields[$this->tableName])
            $this->setField();
        return $this->fields[$this->tableName];
    }

    //得到记录总数
    public function count($sql='')
    {
        $this->sqlQuery['field']='count(*) as c';
        $stmt=$this->query($sql);
        if(!$stmt)
            return false;
        $res=$stmt->fetch();
        //执行完之后要重置查询字段
        return $res['c'];
    }

    //得到sql执行错误
    public function getError()
    {
        return $this->error;
    }

    public function setError($error)
    {
        $this->error=$error;
    }

    /**
     * 扫行有结果集的查询，支持参数绑定
     * 如果你需要遍历数据库，请使用query方法，然后foreach 返回的stmt对象便可。
     * @param mixed $sql 
     * @return boolean|PDOStatement
     */
    public function query($sql='')
    {
        $sql=$this->joinSql($sql);
        $stmt=$this->pdo->prepare($sql);
        $errorInfo=$stmt->errorInfo();
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);
        $stmt->execute($this->sqlQuery['params']);
        //清除sql条件值，desc类部执行的sql语句，不用清楚缓存
        if(strpos($sql,'desc')!==0)
            $this->clearSqlQuery();
        $errorInfo=$stmt->errorInfo();
        if($errorInfo[0]!='00000')
        {
            $this->setError($errorInfo[2]);
            return false;
        }
        return $stmt;
    }

    /**
     * 执行没有结果集的查询,支持参数绑定
     * @param string $sql
     * @param array $params
     * @return 返回受影响行数或false
     */
    public function exec($sql,$params = array())
    {
        $stmt=$this->pdo->prepare($sql);
        if($params!='')
            $this->sqlQuery['params']=$params;
        $stmt->execute($this->sqlQuery['params']);
        $this->clearSqlQuery();
        $errorInfo=$stmt->errorInfo();
        if($errorInfo[0]!='00000')
        {
            $this->setError($errorInfo[2]);
            return false;
        }
        return $stmt->rowCount();
    }

    //设定绑定参数
    public function params($params)
    {
        $this->sqlQuery['params']=empty($params)?'':$params;
        return $this;
    }

    /**
     * 自动绑定参数
     * @param $params
     * @return $this
     */
    public function autoParams($params)
    {
        $this->setField();
        foreach ($params as $key => $row) {
            if(in_array($key, $this->fields[$this->tableName])) {
                $this->sqlQuery['params'][":{$key}"] = $row;
            }
        }
        return $this;
    }

    /**
     * 组合sql语句
     * @param mixed $sql
     * @return 返回组合的sql语句
     */
    public function joinSql($sql)
    {
        if(is_string($sql) && $sql!='')
        {
            $this->sql=$sql;
            return $sql;
        }
        elseif(is_array($sql) && $sql!='')
        {
            foreach ($sql as $key=>$row)
            {
                if(!array_key_exists($key, $this->sqlQuery))
                    continue;
                $this->sqlQuery[$key]=$row;
            }
        }
        else {}
        $this->sql="select {$this->sqlQuery['field']} from {$this->tableName}\n";
        if($this->sqlQuery['join']!='')
            $this->sql.="{$this->sqlQuery['join']} ";
        $this->sql.="where {$this->sqlQuery['where']}\n";
        if($this->sqlQuery['group']!='')
            $this->sql.="group by {$this->sqlQuery['group']}\n";
        if($this->sqlQuery['having']!='')
            $this->sql.="having {$this->sqlQuery['having']}\n";
        if($this->sqlQuery['order']!='')
            $this->sql.="order by {$this->sqlQuery['order']}\n";
        if($this->sqlQuery['limit']!='')
            $this->sql.="limit {$this->sqlQuery['limit']}\n";
        if($this->sqlQuery['union']!='')
            $this->sql.="union {$this->sqlQuery['union']}\n";
        return $this->sql;
    }

    //设定字段的方法
    public function field($field)
    {
        $this->sqlQuery['field']=empty($field)?'*':$field;
        return $this;
    }

    /**
     * 
     * @param unknown_type $where
     * @return \iphp\core\MyDb
     */
    public function where($where)
    {
        $this->sqlQuery['where']=empty($where)?'1':$where;
        return $this;
    }

    /**
     * @param $tableName
     * @param $condition
     * @return $this
     */
    public function join($tableName,$condition)
    {
        $this->sqlQuery['join'].="join {$tableName} on {$condition}\n";
        return $this;
    }

    /**
     * @param $tableName
     * @param $condition
     * @return $this
     */
    public function leftjoin($tableName,$condition)
    {
        $this->sqlQuery['join'].="left join {$tableName} on {$condition}\n";
        return $this;
    }

    /**
     * @param $tableName
     * @param $condition
     * @return $this
     */
    public function rightjoin($tableName,$condition)
    {
        $this->sqlQuery['join'].="right join {$tableName} on {$condition}\n";
        return $this;
    }

    /**
     * @param $group
     * @return $this
     */
    public function group($group)
    {
        $this->sqlQuery['group']=empty($group)?'':$group;
        return $this;
    }

    /**
     * @param $having
     * @return $this
     */
    public function having($having)
    {
        $this->sqlQuery['having']=empty($having)?'':$having;
        return $this;
    }

    /**
     * @param $order
     * @return $this
     */
    public function order($order)
    {
        $this->sqlQuery['order']=empty($order)?'':$order;
        return $this;
    }

    /**
     * @param $limit
     * @return $this
     */
    public function limit($limit)
    {
        $this->sqlQuery['limit']=empty($limit)?'':$limit;
        return $this;
    }

    /**
     * @param $union
     * @return $this
     */
    public function union($union)
    {
        $this->sqlQuery['union']=empty($union)?'':$union;
        return $this;
    }

    /**
     * 清除sql缓存
     */
    public function clearSqlQuery()
    {
        //清除缓存前，先保存当前sql语句。
        if(!empty($this->sqlQuery['params']))
        {
            foreach ($this->sqlQuery['params'] as $key=>$param)
                $this->sql=str_replace($key, '"'.$param.'"', $this->sql);
        }
        $this->sql=nl2br($this->sql);
        foreach ($this->sqlQuery as $key=>$row)
        {
            if($key=='where')
                $this->sqlQuery[$key]='1';
            elseif ($key=='field')
                $this->sqlQuery[$key]='*';
            elseif ($key=='params')
                $this->sqlQuery[$key]=array();
            else 
                $this->sqlQuery[$key]='';
        }
    }

    //再执行findone findall方法之前，得到当前要执行的sql语句,
    public function getSqlCache()
    {
        $sql=$this->joinSql('');
        if(!empty($this->sqlQuery['params']))
        {
            foreach ($this->sqlQuery['params'] as $key=>$param)
                $sql=str_replace($key, '"'.$param.'"', $sql);
        }
        return $sql;
    }
    
    /**
     * 得到当前数据库名称
     */
    public function getDbName()
    {
        return $this->dbName;
    }
    
    /**
     * 得到用户名
     */
    public function getUser()
    {
        return $this->username;
    }
    
    /**
     * 得到密码
     */
    public function getPass()
    {
        return $this->password;
    }
    
    public function  getHost()
    {
        return $this->host;
    }
    
    public function getPort()
    {
        return $this->port;
    }
    
    /**
     * 得到连接相关的详细信息。
     */
    public function getConnInfo()
    {
        return array(
            'host'=>$this->host,
            'port'=>$this->port,
            'username'=>$this->username,
            'password'=>$this->password,
            'dbname'=>$this->dbName,
        );
    }

    /**
     * 开启事务，并设置错误模式为异常
     * 使用try cacth 来回滚或提交
     * beginTransaction()方法将会关闭自动提交（autocommit）模式，
     * 直到事务提交或者回滚以后才能恢复为pdo设置的模式
     */
    public function beginTransaction()
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        return $this->pdo->beginTransaction();
    }

    /**
     * 提交
     * @return bool
     */
    public function commit()
    {
        return $this->pdo->commit();
    }

    /**
     * 回滚事务
     * @return bool
     */
    public function rollBack()
    {
        return $this->pdo->rollBack();
    }

    /**
     * 关闭连接
     */
    public function close()
    {
        $this->pdo=null;
    }
    
    /**
     * 关闭所有连接
     */
    public static function closeAll()
    {
        foreach(static::$_instance as $o)
        {
            if($o instanceof self)
                $o->close();
        }
    }
    
    /**
     * 得到当前表的下一次自增长ID
     */
    public function getNextAutoIncrement($tableName)
    {
        $sql="show table status where name ='{$tableName}'";
        $res=$this->findOne($sql);
        return $res['Auto_increment'];
        
    }


    /**
     * 为一个表增加一个TIMESTAMP字段
     * @param $tableName　表名
     * @param $name　字段名
     * @return bool|int
     */
    public function addTIMESTAMP($tableName,$name='utime')
    {
        $addSql="alter table {$tableName} add {$name} TIMESTAMP
                 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;";
        $addSql.="ALTER TABLE {$tableName} ADD index {$name}($name)";

        return $this->exec($addSql);
    }
}