这次修改主要是增加了2个新function： 1. getTableEngine()，用于获取表引擎； 2. Replace()，以覆盖方式插入数据。
分享源码，仅供技术交流，下载请移步至：http://www.sunbloger.com/download/
源码如下：
代码语言：javascript复制<?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;
}
}
?>
