基于PHP的PDO编写的MySQL类2.0版源码分享

<?php
/**
 * MySQL Class (PDO)
 * @author Jason.Wei <jasonwei06@hotmail.com>
 * @version 2.0 utf-8
 */
class MySQL
{
    private $dsn;
    private $dbh;
    private $rs;
 
    protected $db_name;
 
    /**
     * Connect
     *
     * @param String $db_host 主机地址
     * @param String $db_user 用户名
     * @param String $db_pw 密码
     * @param String $db_name 库名
     * @param String $db_charset 字符集
     */
    public function Connect($db_host, $db_user, $db_pw, $db_name, $db_charset)
    {
        try
        {
            $this->db_name = $db_name;
            $this->dsn = 'mysql:host='.$db_host.';dbname='.$db_name;
            //$this->dsn = 'mysql:host='.$db_host.';';
            $this->dbh = new PDO($this->dsn, $db_user, $db_pw);
            $this->dbh->query("SET character_set_connection=$db_charset, character_set_results=$db_charset, character_set_client=binary");
        }
        catch (PDOException $e)
        {
            exit('MySQL Connect Error:'.$e->getMessage());
        }
    }
 
    /**
     * Query 查询
     *
     * @param String $Sql SQL语句
     * @param String $queryMode 查询方式(All or Row)
     * @param Boolean $debug
     * @return Array
     */
    public function Query($Sql, $queryMode = 'All', $debug = false)
    {
        if($debug) //debug
        {
            exit($Sql);
        }
        $this->rs = $this->dbh->query($Sql);
        //getPDOError();
        if($this->rs)
        {
            $this->rs->setFetchMode(PDO::FETCH_ASSOC);
            if($queryMode == 'All')
            {
                $result = $this->rs->fetchAll();
            } elseif($queryMode == 'Row') {
                $result = $this->rs->fetch();
            }
        } else {
            return NULL;
        }
        $this->rs = NULL;
        return $result;
    }
 
    /**
     * Update 更新
     *
     * @param String $table 表名
     * @param Array $args 字段与值
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function Update($table, $args, $where = '', $debug = false)
    {
        $this->checkFields($table, $args);
        if($where)
        {
            $Sql = '';
            foreach($args as $k=>$v)
            {
                $Sql .= ", `$k`='$v'";
            }
            $Sql = substr($Sql, 1);
            $Sql = "UPDATE `$table` SET $Sql WHERE $where";
        } else {
            $Sql = "REPLACE INTO `$table`(`".implode('`,`', array_keys($args))."`) VALUES('".implode("','", $args)."')";
        }
        if($debug) //debug
        {
            exit($Sql);
        }
        if(($rows = $this->dbh->exec($Sql)) > 0)
        {
            $this->getPDOError();
            return $rows;
        }
        return false;
    }
 
    /**
     * Insert 插入
     *
     * @param String $table 表名
     * @param Array $args 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function Insert($table, $args, $debug = false)
    {
        $this->checkFields($table, $args);
        $Sql = '';
        foreach($args as $k=>$v)
        {
            $Sql .= ", `$k`='$v'";
        }
        $Sql = substr($Sql, 1);
        $Sql = "INSERT INTO `$table` SET $Sql";
        if($debug) //debug
        {
            exit($Sql);
        }
        if($this->dbh->exec($Sql))
        {
            $this->getPDOError();
            return $this->dbh->lastInsertId();
            //return true;
        }
        return false;
    }
 
    /**
     * Replace 覆盖方式插入
     *
     * @param String $table 表名
     * @param Array $args 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function Replace($table, $args, $debug = false)
    {
        $this->checkFields($table, $args);
        $Sql = '';
        foreach($args as $k=>$v)
        {
            $Sql .= ", `$k`='$v'";
        }
        $Sql = substr($Sql, 1);
        $Sql = "REPLACE INTO `$table` SET $Sql";
        if($debug) //debug
        {
            exit($Sql);
        }
        if($this->dbh->exec($Sql))
        {
            $this->getPDOError();
            return $this->dbh->lastInsertId();
            //return true;
        }
        return false;
    }
 
    /**
     * Delete 删除
     *
     * @param String $table 表名
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function Delete($table, $where = '', $debug = false)
    {
        if($where == '')
        {
            exit('缺少必要条件');
        } else {
            $Sql = "DELETE FROM `$table` WHERE $where";
            if($debug)
            {
                exit($Sql);
            }
            if( ($rows = $this->dbh->exec($Sql)) > 0 )
            {
                $this->getPDOError();
                return $rows;
            } else {
                return false;
            }
        }
    }
 
    /**
     * execSql 执行SQL语句
     *
     * @param String $Sql
     * @param Boolean $debug
     * @return mixed
     */
    public function execSql($Sql, $debug = false)
    {
        if($debug)
        {
            exit($Sql);
        }
        $result = $this->dbh->exec($Sql);
        return $result;
    }
 
    /**
     * 获取表引擎
     *
     * @param String $db_name 库名
     * @param String $table_name 表名
     * @param Boolean $debug
     * @return String
     */
    public function getTableEngine($db_name, $table_name, $debug = false)
    {
        $Sql = "SHOW TABLE STATUS FROM $db_name where Name='".$table_name."'";
        $arrayTableInfo = $this->Query($Sql);
        if($debug === true)
        {
            exit($Sql);
        }
        return $arrayTableInfo[0]['Engine'];
    }
 
    /**
     * beginTransaction 事务开始
     */
    private function beginTransaction()
    {
        $this->dbh->beginTransaction();
    }
 
    /**
     * commit 事务提交
     */
    private function commit()
    {
        $this->dbh->commit();
    }
 
    /**
     * rollback 事务回滚
     */
    private function rollback()
    {
        $this->dbh->rollback();
    }
 
    /**
     * transaction 通过事务处理多条SQL语句
     * 调用前需通过getTableEngine判断表引擎是否支持事务
     *
     * @param array $arraySql
     * @return Boolean
     */
    public static function transaction($arraySql)
    {
        $retval = 1;
        $this->beginTransaction();
        foreach($arraySql as $Sql)
        {
            if($this->execSql($sql) == 0)
            {
                $retval = 0;
            }
        }
        if($retval == 0)
        {
            $this->rollback();
            return false;
        }
        else
        {
            $this->commit();
            return true;
        }
    }
 
    /**
     * checkFields 检查指定字段是否在指定数据表中存在
     *
     * @param String $tablename
     * @param array $arrayField
     */
    private function checkFields($tablename, $arrayField)
    {
        $fields = $this->getFields($tablename);
        foreach($arrayField AS $k=>$v)
        {
            if(!in_array($k, $fields))
            {
                exit("MySQL Query Error: Unknown column '$k' in field list");
                return false;
            }
        }
    }
 
    /**
     * getFields 获取指定数据表中的全部字段名
     *
     * @param String $table 表名
     * @return array
     */
    private function getFields($table)
    {
        $fields = array();
        $this->rs = $this->dbh->query("SHOW COLUMNS FROM $table");
        $this->rs->setFetchMode(PDO::FETCH_ASSOC);
        $result = $this->rs->fetchAll();
        foreach($result as $rows)
        {
            $fields[] = $rows['Field'];
        }
        $this->getPDOError();
        return $fields;
    }
 
    /**
     * getPDOError 捕获PDO错误信息
     */
    private function getPDOError()
    {
        if($this->dbh->errorCode() != '00000')
        {
            $error = $this->dbh->errorInfo();
            exit($error[2]);
        }
    }
 
    /**
     * Destruct 关闭数据库连接
     */
    public function Destruct()
    {
        $this->dbh = null;
    }
}
?>