PDO类的操作,如何生成sql语言

采用单例模式编写PHP的PDO类

下面的代码是用此前一个名为MyPDO的类改写的，引入了单例模式来保证在全局调用中不会重复实例化这个类，降低系统资源的浪费。

<?php
// FastMVC
// Copyright (c) 2006~2016 http://00kb.cn All rights reserved
namespace fast;
class Query{
	public $conn;
	public $sqls			=	[];
	public $data			=	[];
	public $clear			=	true;
	public $debug			=	true;
	public $options			=	[];
	public function __construct($config=[]){
		$this->config=array_merge($this->config,App::$sql_config,$config);
		$this->connect($this->config);
	}
	public function connect($config){
		try {
			$this->conn = new \PDO($config['type'].':host='.$config['host'].';dbname='.$config['table'],$config['user'],$config['pass']);
			$this->conn->exec('SET character_set_connection='.$config['charset'].', character_set_results='.$config['charset'].', character_set_client=binary');
		} catch (PDOException $e){
			throw new \Exception('数据库连接错误: '.$e->getMessage(),"行号: ".$e->getLine());
		}
		return $this;
	}
	public function query($sql){
		try{ 
			if(APP_DEBUG){
				Debug::consuming('query');
			}
			if(isset($this->template[$sql])){
				$sql=$this->template[$sql];
			}
			$sql=$this->build($sql);
			if($this->clear){
				p($this->options);
				$this->options=[];
			}else{
				$this->clear=true;
			}
			if(in_array($action=strtoupper(substr($sql,0,6)),['SELECT','INSERT','UPDATE','DELETE'])){
				if('SELECT' == $action){
					$sth=$this->conn->query($sql);
					if($sth){
						$result=$sth->fetchAll(\PDO::FETCH_ASSOC);
						if(isset($result[0]['methodback'])){
							$result=$result[0]['methodback'];
						}
					}else{
						$result=null;
					}
				}else{
					$result=$this->conn->exec($sql);
					if('INSERT'==$action){
						$result=$this->conn->lastInsertId();
					}
				}			
			}
			if(APP_DEBUG){
				Debug::$sql[]='['.sprintf("%.9f", Debug::consuming('query')*1000 ).':'.(is_numeric($result)?$result:count($result)).'] '.$sql;
				if ($this->conn->errorCode() != '00000'){
					$err=$this->conn->errorInfo();	
					Debug::errorBar('数据库错误['.$err['1'].']',$err[2],$sql,$err[0],null);
				}
			}
			return $result;
		}catch (PDOException $e) {
			throw new \Exception('数据库操作错误: '.$e->getMessage(),"行号: ".$e->getLine());
		}
		return null;
	}
	public function whereOr($name=null,$action=null,$value=null){
		$this->where('OR',$name,$action,$value);
	}
	public function table($table){
		$this->options['table'] = $this->config['prefix'].$table;
		return $this;
	}
	public function field($field=null){
		if($field)$this->options['field']=$field;
		return $this;
	}
	public function limit($limit=null,$value=null){
		if(is_null($value)){
			$this->options['limit']=$limit;
		}else{
			$this->options['limit']=$limit.','.$value;
		}
		return $this;
	}
	public function order($order=null,$value=null){
		if(strpbrk($order,'( )')){
			$this->options['order']= $order ;
		}elseif(is_null($value)){
			$this->options['order']='`'.$order.'`';
		}else{
			$this->options['order']='`'.$order.'` '.$value;
		}
		return $this;
	}
	public function page($sub=10,&$page,$size=10){
		$this->clear=false;
		$page= \fast\Request::pagelist($sub,$this->count(),$size);
		$this->limit($page['limit']);
		return $this;
	}
	public function save($data=null,$key='id'){
		$this->options['data']=$data;
		return $this->query('save');
	}
	public function data($data=null){
		if(is_array($data))$this->options['data']=$data;
		return $this;
	}
	public function insert($data=null){
		if(!empty($data))$this->options['data']=$data;
		return $this->query('insert');
	}
	public function update($data=null){
		if(!empty($data)){
			if(isset($data['id'])&&empty($this->options['where'])){
				$this->where($data['id']);
				unset($data['id']);
			}
			$this->options['data']=$data;
		}
		return $this->query('update');
	}
	public function count($field=null){
		return $this->method('COUNT',$field);
	}
	public function max($field=null){
		return $this->method('MAX',$field);
	}
	public function min($field=null){
		return $this->method('MIN',$field);
	}	
	public function avg($field=null){
		return $this->method('AVG',$field);
	}
	public function sum($field=null){
		return $this->method('SUM',$field);
	}	
	public function method($method,$field=null){
		if($field)$this->options['field']=$field;
		$this->options['method']=$method;
		return $this->query('method');
	}
	public function find($name=null,$action=null,$value=null){
		if($name)$this->where($name,$action,$value);
		return $this->query('find')[0];
	}		
	public function select($name=null,$action=null,$value=null){
		if($name)$this->where($name,$action,$value);
		return $this->query('select');
	}	
	public function delete($name=null,$action=null,$value=null){
		if($name)$this->where($name,$action,$value);
		if($this->options['where'])return $this->query('delete');
	}
	public function escape($str){
		if(is_numeric($str)){
			return $str;
		}elseif(is_string($str)){
			return '"'.@mysql_escape_string($str).'"';
		}elseif(is_bool($str)){
			return mysql_escape_string($str);
		}elseif(is_null($str)){
			return 'NULL';
		}
		return '"undefined"';
	}
	public function joinEscape($arr){
		if(is_string($arr)){
			$arr=explode(',',$arr);
		}
		foreach($arr as &$v)$v=$this->escape($v);
		return implode(',',$arr);
	}
	public function where($logic=null,$name=null,$action=null,$value=null){
		$arg=is_array($name)?$name:func_get_args();
		if(!(is_string($arg[0])&&in_array($arg[0],$this->operator))){
			array_unshift($arg,'AND');
		}
		if(is_numeric($arg[1])){
			$str= '`id` = '.$arg[1];
		}elseif(is_string($arg[1])){
			if(is_string($arg[2])||is_numeric($arg[2])){
				if(isset($arg[3])&&in_array($arg[2],['not in','not like','NOT IN','NOT LIKE','in','like','IN','LIKE','<','>','<>','!=','='])){
					$arg[2]=strtoupper($arg[2]);
					if('IN'==$arg[2]){
						$str='`'.$arg[1].'` IN ('.$this->joinEscape($arg[3]).')';
					}elseif('LIKE'==$arg[2]){
						$str='`'.$arg[1].'` LIKE '.$this->escape($arg[3]);
					}else{
						$str='`'.$arg[1].'` '.$arg[2].' '.$this->escape($arg[3]);
					}
					$this->options['keys'].=$arg[1];
				}elseif(strpos($arg[1],'|')){
					$arg2=$this->escape($arg[2]);
					$str= '(`'.str_replace('|','` = '.$arg2.' OR `',$arg[1]).'` = '.$arg2.')';
				}else{
					$str= '`'.$arg[1].'` = '.$this->escape($arg[2]).'';
					$this->options['keys'].=$arg[1];
				}
			}elseif(is_null($arg[2])){
				if(strpos($arg[1],'OR')||strpos($arg[1],'AND')){
					$str= '('.$arg[1].')';
				}else{
					$str= $arg[1];
				}
			}
		}
		if(!isset($str)){
			$arr=[];
			foreach($arg as $k=>$v){
				if(is_array($v)){
					$arr[]=$this->where('CALLBACK',$v);
				}elseif(!is_int($k)){
					$arr[]=$this->where('CALLBACK',[$arg[0],$k,$v]);
				}
			}
			if(count($arr)>0){
				$str=''.implode($arr,' '.strtoupper($arg[0]).' ').'';
				if($this->options['where']&&count($arr)>1){
					$str='('.$str.')';
				}				
			}
		}
		if('CALLBACK'==$logic){
			return $str;
		}
		if($str){
			$logic=is_string($logic)&&in_array($logic,$this->operator)?strtoupper($logic):'AND';
			if(isset($this->options['where'])){
				if(strpos($this->options['where'],' OR ')){
					$this->options['where']='('.$this->options['where'].') '.$logic.' '.$str;
				}elseif($this->options['where']){
					$this->options['where'].=' '.$logic.' '.$str;
				}
			}else{
				$this->options['where'] = $str;
			}
		}
		return $this;
	}
	public function build($tpl=''){
		$sql=preg_replace_callback('/(\s)?%([a-z]+?)%/',function($a){
			switch($a[2]){
				case 'where' :
					if(isset($this->options['where'])){
						return $a[1].'WHERE '.$this->options['where'];
					}
					break;
				case 'table' :
					if(isset($this->options['table'])){
						return $a[1].'`'.$this->options['table'].'`';
					}
					break;
				case 'value':
					if(isset($this->options['data'])){
						$keyStr=$valstr=[];
						foreach($this->options['data'] as $key=>$val){
							array_push($keyStr,'`'.$key.'`');
							array_push($valstr,$this->escape($val));
							$this->options['keys'].=$key;
						}
						return $a[1].'('.implode(',',$keyStr).')VALUE('.implode(',',$valstr).')';
					}
					break;
				case 'set':
					if(isset($this->options['data'])){
						$valstr=[];
						foreach($this->options['data'] as $key=>$val){
							$valstr[]= '`'.$key.'` = '.$this->escape($val);
							$this->options['keys'].=$key;
						}
						return $a[1].implode(' , ',$valstr).'';
					}
					break;
				case 'field':
					if(isset($this->options['field']) && '*'!==$this->options['field']){
						return $a[1].'`'.str_replace(',','`,`',$this->options['field']).'`';
					}else{
						return $a[1].'*';
					}
					break;
				case 'limit':
					if(isset($this->options['limit'])){
						return $a[1].'LIMIT '.$this->options['limit'];
					}
					break;
				case 'order':
					if(isset($this->options['order'])){
						return $a[1].'ORDER BY '.$this->options['order'];
					}
					break;
				case 'method':
					if(isset($this->options['method'])){
						return $a[1].$this->options['method'];
					}
					break;
			}
			return '';
		},$tpl);
		//防sql注入
		if(isset($this->options['keys'])){
			if(preg_match('/[^\w,]/',$this->options['keys'])){
				throw new \Exception('数据库操作违法');
			}
		}
		$this->sqls[]=$sql;
		return $sql;
	}
	//运算符
	public function match($str,$key='spell',&$split=null){
		if(!$str)return $this;
		$split = $this ->splitWord($str);
		return $this->where('MATCH(`'.$key.'`)AGAINST('.$this->escape($split['spell']).')');
	}
	//分词
	public function splitWord($str,$sub=50){
		if(strlen($str)==mb_strlen($str,'utf8'))return ['text'=>$str,'split'=>$str,'spell'=>$str];
		return \lib\Phpanalysis::splitWord(mb_substr($str,0,$sub,'utf-8'));	
	}	
	//debug
	public function sql(){
		return $this->sqls;
	}
	//清理机制
	public function __destruct(){
		$this->conn=null;
	}
	//运算符
	public $operator = ['or','and','not','OR','AND','NOT'];
	//模版
	public $template =	[
		'find'		=>	"SELECT %field%\nFROM %table%\n%where%\n%order% LIMIT 1;",
		'save'		=>	"INSERT INTO %table%\n%value%\nON DUPLICATE KEY\nUPDATE %set%;",
		'method'	=>	"SELECT %method%(%field%) AS methodback\nFROM %table%\n%where%\n%limit%;",
		'select'	=>	"SELECT %field%\nFROM %table%\n%where%\n%order%\n%limit%;",
		'insert'	=>	"INSERT INTO %table%\n%value%\n%comment%;",
		'update'	=>	"UPDATE %table%\nSET %set%\n%where%;",
		'delete'	=>	"DELETE FROM %table%\n%where%\n%order%\n%limit%;",
	];
	//配置
	public $config=[
		'type'		=> 'mysql',
		'host'		=> '127.0.0.1',
		'user'		=> 'root',
		'pass'		=> '',
		'table'		=> '',
		'prefix'	=> '',
		'charset'	=> 'utf8',
	];
}

<?php
/**
 * MyPDO
 * @author Jason.Wei <jasonwei06@hotmail.com>
 * @license http://www.sunbloger.com/
 * @version 5.0 utf8
 */
class MyPDO
{
    protected static $_instance = null;
    protected $dbName = '';
    protected $dsn;
    protected $dbh;
    /**
     * 构造
     * 
     * @return MyPDO
     */
    private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        try {
            $this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;
            $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);
            $this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');
        } catch (PDOException $e) {
            $this->outputError($e->getMessage());
        }
    }
    /**
     * 防止克隆
     * 
     */
    private function __clone() {}
    /**
     * Singleton instance
     * 
     * @return Object
     */
    public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        if (self::$_instance === null) {
            self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);
        }
        return self::$_instance;
    }
    /**
     * Query 查询
     *
     * @param String $strSql SQL语句
     * @param String $queryMode 查询方式(All or Row)
     * @param Boolean $debug
     * @return Array
     */
    public function query($strSql, $queryMode = 'All', $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $recordset = $this->dbh->query($strSql);
        $this->getPDOError();
        if ($recordset) {
            $recordset->setFetchMode(PDO::FETCH_ASSOC);
            if ($queryMode == 'All') {
                $result = $recordset->fetchAll();
            } elseif ($queryMode == 'Row') {
                $result = $recordset->fetch();
            }
        } else {
            $result = null;
        }
        return $result;
    }
    /**
     * Update 更新
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function update($table, $arrayDataValue, $where = '', $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        if ($where) {
            $strSql = '';
            foreach ($arrayDataValue as $key => $value) {
                $strSql .= ", `$key`='$value'";
            }
            $strSql = substr($strSql, 1);
            $strSql = "UPDATE `$table` SET $strSql WHERE $where";
        } else {
            $strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        }
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Insert 插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function insert($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Replace 覆盖方式插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function replace($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "REPLACE INTO `$table`(`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Delete 删除
     *
     * @param String $table 表名
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function delete($table, $where = '', $debug = false)
    {
        if ($where == '') {
            $this->outputError("'WHERE' is Null");
        } else {
            $strSql = "DELETE FROM `$table` WHERE $where";
            if ($debug === true) $this->debug($strSql);
            $result = $this->dbh->exec($strSql);
            $this->getPDOError();
            return $result;
        }
    }
    /**
     * execSql 执行SQL语句
     *
     * @param String $strSql
     * @param Boolean $debug
     * @return Int
     */
    public function execSql($strSql, $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * 获取字段最大值
     * 
     * @param string $table 表名
     * @param string $field_name 字段名
     * @param string $where 条件
     */
    public function getMaxValue($table, $field_name, $where = '', $debug = false)
    {
        $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, 'Row');
        $maxValue = $arrTemp["MAX_VALUE"];
        if ($maxValue == "" || $maxValue == null) {
            $maxValue = 0;
        }
        return $maxValue;
    }
    /**
     * 获取指定列的数量
     * 
     * @param string $table
     * @param string $field_name
     * @param string $where
     * @param bool $debug
     * @return int
     */
    public function getCount($table, $field_name, $where = '', $debug = false)
    {
        $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, 'Row');
        return $arrTemp['NUM'];
    }
    /**
     * 获取表引擎
     * 
     * @param String $dbName 库名
     * @param String $tableName 表名
     * @param Boolean $debug
     * @return String
     */
    public function getTableEngine($dbName, $tableName)
    {
        $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";
        $arrayTableInfo = $this->query($strSql);
        $this->getPDOError();
        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 function execTransaction($arraySql)
    {
        $retval = 1;
        $this->beginTransaction();
        foreach ($arraySql as $strSql) {
            if ($this->execSql($strSql) == 0) $retval = 0;
        }
        if ($retval == 0) {
            $this->rollback();
            return false;
        } else {
            $this->commit();
            return true;
        }
    }
    /**
     * checkFields 检查指定字段是否在指定数据表中存在
     *
     * @param String $table
     * @param array $arrayField
     */
    private function checkFields($table, $arrayFields)
    {
        $fields = $this->getFields($table);
        foreach ($arrayFields as $key => $value) {
            if (!in_array($key, $fields)) {
                $this->outputError("Unknown column `$key` in field list.");
            }
        }
    }
    /**
     * getFields 获取指定数据表中的全部字段名
     *
     * @param String $table 表名
     * @return array
     */
    private function getFields($table)
    {
        $fields = array();
        $recordset = $this->dbh->query("SHOW COLUMNS FROM $table");
        $this->getPDOError();
        $recordset->setFetchMode(PDO::FETCH_ASSOC);
        $result = $recordset->fetchAll();
        foreach ($result as $rows) {
            $fields[] = $rows['Field'];
        }
        return $fields;
    }
    /**
     * getPDOError 捕获PDO错误信息
     */
    private function getPDOError()
    {
        if ($this->dbh->errorCode() != '00000') {
            $arrayError = $this->dbh->errorInfo();
            $this->outputError($arrayError[2]);
        }
    }
    /**
     * debug
     * 
     * @param mixed $debuginfo
     */
    private function debug($debuginfo)
    {
        var_dump($debuginfo);
        exit();
    }
    /**
     * 输出错误信息
     * 
     * @param String $strErrMsg
     */
    private function outputError($strErrMsg)
    {
        throw new Exception('MySQL Error: '.$strErrMsg);
    }
    /**
     * destruct 关闭数据库连接
     */
    public function destruct()
    {
        $this->dbh = null;
    }
}
?>

<?php
/**
 * MyPDO
 * @author Jason.Wei <jasonwei06@hotmail.com>
 * @license http://www.sunbloger.com/
 * @version 5.0 utf8
 */
class MyPDO
{
    protected static $_instance = null;
    protected $dbName = '';
    protected $dsn;
    protected $dbh;
    /**
     * 构造
     * 
     * @return MyPDO
     */
    private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        try {
            $this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;
            $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);
            $this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');
        } catch (PDOException $e) {
            $this->outputError($e->getMessage());
        }
    }
    /**
     * 防止克隆
     * 
     */
    private function __clone() {}
    /**
     * Singleton instance
     * 
     * @return Object
     */
    public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        if (self::$_instance === null) {
            self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);
        }
        return self::$_instance;
    }
    /**
     * Query 查询
     *
     * @param String $strSql SQL语句
     * @param String $queryMode 查询方式(All or Row)
     * @param Boolean $debug
     * @return Array
     */
    public function query($strSql, $queryMode = 'All', $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $recordset = $this->dbh->query($strSql);
        $this->getPDOError();
        if ($recordset) {
            $recordset->setFetchMode(PDO::FETCH_ASSOC);
            if ($queryMode == 'All') {
                $result = $recordset->fetchAll();
            } elseif ($queryMode == 'Row') {
                $result = $recordset->fetch();
            }
        } else {
            $result = null;
        }
        return $result;
    }
    /**
     * Update 更新
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function update($table, $arrayDataValue, $where = '', $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        if ($where) {
            $strSql = '';
            foreach ($arrayDataValue as $key => $value) {
                $strSql .= ", `$key`='$value'";
            }
            $strSql = substr($strSql, 1);
            $strSql = "UPDATE `$table` SET $strSql WHERE $where";
        } else {
            $strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        }
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Insert 插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function insert($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "INSERT INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Replace 覆盖方式插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function replace($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "REPLACE INTO `$table`(`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * Delete 删除
     *
     * @param String $table 表名
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function delete($table, $where = '', $debug = false)
    {
        if ($where == '') {
            $this->outputError("'WHERE' is Null");
        } else {
            $strSql = "DELETE FROM `$table` WHERE $where";
            if ($debug === true) $this->debug($strSql);
            $result = $this->dbh->exec($strSql);
            $this->getPDOError();
            return $result;
        }
    }
    /**
     * execSql 执行SQL语句
     *
     * @param String $strSql
     * @param Boolean $debug
     * @return Int
     */
    public function execSql($strSql, $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    /**
     * 获取字段最大值
     * 
     * @param string $table 表名
     * @param string $field_name 字段名
     * @param string $where 条件
     */
    public function getMaxValue($table, $field_name, $where = '', $debug = false)
    {
        $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, 'Row');
        $maxValue = $arrTemp["MAX_VALUE"];
        if ($maxValue == "" || $maxValue == null) {
            $maxValue = 0;
        }
        return $maxValue;
    }
    /**
     * 获取指定列的数量
     * 
     * @param string $table
     * @param string $field_name
     * @param string $where
     * @param bool $debug
     * @return int
     */
    public function getCount($table, $field_name, $where = '', $debug = false)
    {
        $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";
        if ($where != '') $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, 'Row');
        return $arrTemp['NUM'];
    }
    /**
     * 获取表引擎
     * 
     * @param String $dbName 库名
     * @param String $tableName 表名
     * @param Boolean $debug
     * @return String
     */
    public function getTableEngine($dbName, $tableName)
    {
        $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";
        $arrayTableInfo = $this->query($strSql);
        $this->getPDOError();
        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 function execTransaction($arraySql)
    {
        $retval = 1;
        $this->beginTransaction();
        foreach ($arraySql as $strSql) {
            if ($this->execSql($strSql) == 0) $retval = 0;
        }
        if ($retval == 0) {
            $this->rollback();
            return false;
        } else {
            $this->commit();
            return true;
        }
    }
    /**
     * checkFields 检查指定字段是否在指定数据表中存在
     *
     * @param String $table
     * @param array $arrayField
     */
    private function checkFields($table, $arrayFields)
    {
        $fields = $this->getFields($table);
        foreach ($arrayFields as $key => $value) {
            if (!in_array($key, $fields)) {
                $this->outputError("Unknown column `$key` in field list.");
            }
        }
    }
    /**
     * getFields 获取指定数据表中的全部字段名
     *
     * @param String $table 表名
     * @return array
     */
    private function getFields($table)
    {
        $fields = array();
        $recordset = $this->dbh->query("SHOW COLUMNS FROM $table");
        $this->getPDOError();
        $recordset->setFetchMode(PDO::FETCH_ASSOC);
        $result = $recordset->fetchAll();
        foreach ($result as $rows) {
            $fields[] = $rows['Field'];
        }
        return $fields;
    }
    /**
     * getPDOError 捕获PDO错误信息
     */
    private function getPDOError()
    {
        if ($this->dbh->errorCode() != '00000') {
            $arrayError = $this->dbh->errorInfo();
            $this->outputError($arrayError[2]);
        }
    }
    /**
     * debug
     * 
     * @param mixed $debuginfo
     */
    private function debug($debuginfo)
    {
        var_dump($debuginfo);
        exit();
    }
    /**
     * 输出错误信息
     * 
     * @param String $strErrMsg
     */
    private function outputError($strErrMsg)
    {
        throw new Exception('MySQL Error: '.$strErrMsg);
    }
    /**
     * destruct 关闭数据库连接
     */
    public function destruct()
    {
        $this->dbh = null;
    }
}
?>

调用方法：

<?php
require 'MyPDO.class.php';
$db = MyPDO::getInstance('localhost', 'root', '123456', 'test', 'utf8');
//do something...
$db->destruct();
?>

<?php
require 'MyPDO.class.php';
$db = MyPDO::getInstance('localhost', 'root', '123456', 'test', 'utf8');
//do something...
$db->destruct();
?>