php批量数据提交mysql_php 数据批量插入mysql和mysql类

{private $host = "";//连接地址

private $user = "";//用户名

private $pass = "";//密码

public $dbcharset = "utf8";//默认的数据库编码

protected static $_instance = null;//数据库实例

protected$dsn;public $dbh = null;/**

* 构造

* @param $dbname 数据库名

* @param array $dbconf 数据库连接信息

* @throws Exception*/

private function __construct($dbname, $dbconf =array())

{

$host= isset($dbconf['host']) ? $dbconf['host'] : $this->host;

$user= isset($dbconf['user']) ? $dbconf['user'] : $this->user;

$pass= isset($dbconf['pass']) ? $dbconf['pass'] : $this->pass;try{

$this->dsn = 'mysql:host=' . $host . ';dbname='. $dbname;

$this->dbh = new PDO($this->dsn, $user, $pass);

$this->dbh->exec('SET character_set_connection=' . $this->dbcharset . ', character_set_results=' . $this->dbcharset . ', character_set_client=binary');

}catch(PDOException $e) {

$this->outputError($e->getMessage());

}

}/**

* Singleton instance

*

* @param $dbname

* @param array $dbconf 数据库连接信息

* @return null|PDOmysql 存放数据库实例

* @throws Exception*/

public static function getInstance($dbname, $dbconf =array())

{if (!self::$_instance) {

self::$_instance= newself($dbname, $dbconf);

}returnself::$_instance;

}/**

* Query 查询

*

* @param String $strSql SQL语句

* @param String $queryMode 查询方式(All or Row)

* @param int $pdoMode 指定数据获取方式

* @param Boolean $debug

* @return Array*/

public function query($strSql, $queryMode = 'All', $pdoMode = PDO::FETCH_ASSOC, $debug = false)

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

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

$this->getPDOError();if($recordset) {

$recordset->setFetchMode($pdoMode);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

* @throws Exception*/

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

{//print_r($arrayDataValue);

$this->checkFields($table, $arrayDataValue);if ($where) {

$strSql= '';foreach ($arrayDataValue as $key =>$value) {if (!empty($value)) {

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

}else{

$strSql .= ", $key=null";

}

}

$strSql= substr($strSql, 1);

$strSql= "UPDATE $table SET $strSql WHERE $where";//echo $strSql;

} 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

* @throws Exception*/

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;

}/**

* 批量插入

* 多条数据同时转化成插入SQL语句

* @param string $table 表名

* @$arr_key是表字段名的key：$arr_key=array("field1","field2","field3")

* @param array $arr是字段值 数组示例 array(("a","b","c"), ("bbc","bbb","caaa"),('add',"bppp","cggg"))

* @return string*/function multArray2Insert($table,$arr_key, $arr, $split= '`',$debug = false) {

$arrValues=array();if (empty($table) || !is_array($arr_key) || !is_array($arr)) {return false;

}

$sql= "INSERT INTO %s( %s ) values %s";foreach ($arr as $k =>$v) {

$arrValues[$k]= "'".implode("','",array_values($v))."'";

}

$sql= sprintf($sql, $table, "{$split}" . implode("{$split} ,{$split}", $arr_key) . "{$split}", "(" . implode(") , (", array_values($arrValues)) . ")");//var_dump($sql);exit;

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

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

$this->getPDOError();return$result;

}/**

* Replace 覆盖方式插入

*

* @param String $table 表名

* @param Array $arrayDataValue 字段与值

* @param Boolean $debug

* @return Int

* @throws Exception*/

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

* @throws Exception*/

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;

}/**

* checkFields 检查指定字段是否在指定数据表中存在

*

* @param String $table

* @param $arrayFields

* @throws Exception*/

privatefunction 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*/

privatefunction 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;

}/**

* 获取字段最大值

*

* @param string $table 表名

* @param string $field_name 字段名

* @param string $where 条件

* @param bool $debug

* @return int|mixed*/

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 $where

* @param bool $debug

* @return int*/

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

{

$strSql= "SELECT COUNT(1) AS c FROM $table";if ($where != '') $strSql .= "WHERE $where";if ($debug === true) $this->debug($strSql);

$arrTemp= $this->query($strSql, 'Row');return $arrTemp['c'];

}/**

* 获取表引擎

*

* @param String $dbName 库名

* @param String $tableName 表名

* @return String*/

publicfunction getTableEngine($dbName, $tableName)

{

$strSql= "SHOW TABLE STATUS FROM $dbName WHERE Name='" . $tableName . "'";

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

$this->getPDOError();return $arrayTableInfo[0]['Engine'];

}/**

* beginTransaction 事务开始*/

publicfunction beginTransaction()

{

$this->dbh->beginTransaction();

}/**

* commit 事务提交*/

publicfunction commit()

{

$this->dbh->commit();

}/**

* rollback 事务回滚*/

publicfunction rollback()

{

$this->dbh->rollback();

}/**

* transaction 通过事务处理多条SQL语句

* 调用前需通过getTableEngine判断表引擎是否支持事务

*

* @param array $arraySql

* @return Boolean*/

publicfunction 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;

}

}/**

* getPDOError 捕获PDO错误信息*/

privatefunction getPDOError()

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

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

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

}

}/**

* debug

*

* @param mixed $debuginfo*/

privatefunction debug($debuginfo)

{

var_dump($debuginfo);

exit();

}/**

* 输出错误信息

*

* @param String $strErrMsg

* @throws Exception*/

privatefunction outputError($strErrMsg)

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

}/**

* 防止克隆

**/

privatefunction __clone()

{

}/**

* destruct 关闭数据库连接*/

publicfunction __destruct()

{

$this->dbh = null;

}

}