mysql 大量空连接_一个PDO数据库连接类库,以后有空会扩展和更新PHP协同Mysql处理大量数据的一些方法，望大家常来...

PDO.class.php

* version 1.0

*/

class DB{

protected static $_instance = null;

protected $dbName = '';

protected $dsn;

protected $dbh;

/**

* 构造函数

*/

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(){}

/**

* 实例化一个数据库

* @param [string] $dbName

* @param [string] $dbUser

* @param [string] $dbPasswd

* @param [string] $dbName

* @param [string] $dbCharset

* @return [object] 数据库实例

*/

public function getInstance($dbHost,$dbUser,$dbPasswd,$dbName,$dbCharset){

if(self::$_instance==null){

self::$_instance = new self($dbHost,$dbUser,$dbPasswd,$dbName,$dbCharset);

}

return self::$_instance;

}

/**

* [query description]

* @param [string] $sqlstr [查询字符串]

* @param string $querymode [查询模式，All:全部|Row：单条]

* @param boolean $debug [是否返回sql语句，默认false]

* @return [array] $result [数据]

*/

public function query($sqlstr,$querymode='All',$debug=false){

if($debug==true) $this->debug($sqlstr);

$recordset = $this->dbh->query($sqlstr);

$this->getPDOError();

if($recordset){

$recordset->setFetchMode(PDO::FETCH_ASSOC);

if($querymode=='All'){

$result = $recordset->fetchAll();

}elseif($querymode=='Row'){

$result = $recordset->fetch();

}

}else{

$result = '';

}

return $result;

}

/**

* [update 更新]

* @param [type] $table [表名]

* @param [type] $arrayDataValue [数据]

* @param string $where [条件]

* @param [type] $debug [错误输出]

* @return [type] [description]

*/

public function update($table,$arrayDataValue,$where='',$debug=false){

$this->checkFields($table,$arrayDataValue);

if($where){

$strSql = '';

foreach($arrayDataValue as $key => $val){

$strSql.=','.$key.'="'.$val.'"';

}

$strSql = substr($strSql,1);

$strSql = 'update '.$table.' set '.$strSql.' where '.$where;

}else{

$strSql = 'replace into '.$table.'('.implode(',',array_keys($arrayDataValue)).') values("'.implode('","',$arrayDataValue).'")';

}

$result = $this->execSql($strSql,$debug);

return $result;

}

/**

* [insert 插入]

* @param [type] $table [表名]

* @param [type] $arrayDataValue [数据]

* @param boolean $debug [调试]

* @return [type] [description]

*/

public function insert($table,$arrayDataValue,$debug=false){

$this->checkFields($table,$arrayDataValue);

$strSql = 'insert into '.$table.'('.implode(',',array_keys($arrayDataValue)).') values("'.implode('","',$arrayDataValue).'")';

$result = $this->execSql($strSql,$debug);

return $result;

}

/**

* [replace 覆盖,需有主键ID]

* @param [type] $table

* @param [type] $arrayDataValue

* @param boolean $debug

* @return [type]

*/

public function replace($table,$arrayDataValue,$debug=false){

$this->checkFields($table,$arrayDataValue);

$strSql = 'replace into '.$table.'('.implode(',',array_keys($arrayDataValue)).') values("'.implode('","',$arrayDataValue).'")';

$result = $this->execSql($strSql,$debug);

return $result;

}

/**

* [delete 删除]

* @param [string] $table

* @param string $where

* @param boolean $debug

* @return [type]

*/

public function delete($table,$where='',$debug=false){

if($where == ''){

$this->outputError('where is Null');

}else{

$strSql = 'delete from '.$table.' where '.$where;

$result = $this->execSql($strSql,$debug);

return $result;

}

}

/**

* [execSql 执行sql]

* @param [type] $strSql

* @param boolean $debug

* @return [type]

*/

public function execSql($strSql,$debug=false){

if($debug==true) $this->debug($strSql);

$result = $this->dbh->exec($strSql);

$this->getPDOError();

return $result;

}

/**

* [getMaxValue 获取最大值]

* @param [type] $table

* @param [type] $fields_name

* @param string $where

* @param boolean $debug

* @return [type]

*/

public function getMaxValue($table,$fields_name,$where='',$debug=false){

$strSql = 'select max('.$fields_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;

}

/*获取字段总数*/

public function getCount($table,$fields_name,$where='',$debug=false){

$strSql = 'select count('.$fields_name.') as NUM from '.$table;

if($where!='') $strSql.=' where '.$where;

if($debug==true) $this->debug($strSql);

$arrTemp = $this->query($strSql,'Row');

return $arrTemp['NUM'];

}

/*获得表引擎*/

public function getTableEngine($dbName,$tableName){

$strSql = 'show table status from '.$dbName.' where name="'.$tableName.'"';

$arrayTableInfo=$this->query($strSql);

$this->getPDOError();

return $arrayTableInfo[0]['Engine'];

}

/*开始事务*/

private function beginTransaction(){

$this->dbh->beginTransaction();

}

/*提交事务*/

private function commit(){

$this->dbh->commit();

}

/*火棍事务*/

private function rollback(){

$this->dbh->rollback();

}

/*执行事务*/

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] $arrayDataValue [数据：字段名：数据]

*/

private function checkFields($table,$arrayFields){

$fields = $this->getFields($table);

foreach ($arrayFields as $key => $value) {

if(!in_array($key,$fields)){

$this->outputError('unkown columns'.$key.' in fields list');

}

}

}

/**

* [getFields 获得表字段]

* @param [string] $table [表名称]

* @return [array] $fields [表字段数组]

*/

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 $a) {

$fields[]=$a['Field'];

}

return $fields;

}

/**

* 捕捉PDO错误异常信息

* @return [type] [description]

*/

private function getPDOError(){

if($this->dbh->errorCode()!='00000'){

$arrayError = $this->dbh->errorInfo();

$this->outputError($arrayError[2]);

}

}

/**

* [outputError 输出错误信息]

* @param [type] $strErrMsg [错误信息]

* @return [type] [description]

*/

private function outputError($strErrMsg)

{

throw new Exception('MySQL Error: '.$strErrMsg);

}

/**

* debug sql语句错误信息

* @param mixed $debuginfo

*/

private function debug($debuginfo){

var_dump($debuginfo);

exit();

}

/**

* [destruct 关闭数据库连接]

*

*/

public function destruct(){

$this->dbh = null;

}

}

?>

PDO.zip