php操作mysql的普通类和pdo类,2者几乎可兼容升级。
20210119优化版
<?php
/*
普通调用:
require 本类;
$mydb=new myDb($dbname);//$dbname为数据库名称
项目调用:
M()->query();//参考func/global.php中的function M();
*/
class myDb{
private $Link_ID=0;
private $PDOStatement;//执行失败时设置为false
private $Db='';
private $host=array();
private $debugMode=false;
private $debugParams=array();
private $debugSql='';
function __construct($db='',$host=array()){
if(defined('MYSQL_ERROR') && MYSQL_ERROR===true){//判断是否允许debug
$this->debugMode=true;
}
$this->Db=$db;
$this->host=$host;
if(count($host)<=0){
$dbconfig=dirname(dirname(__FILE__)).'/config/global/db.php';
if(is_file($dbconfig)){
require_once $dbconfig;
$this->host=$GLOBALS['dbHost']['default'];
}
else {exit('undefind db');}
}
//不执行mysql链接,因为考虑到包含文件并不一定需要使用到mysql,由相关方法里面自行执行
//$this->connect();
}
function connect(){
if(!$this->Link_ID){
try{
$this->Link_ID=new PDO('mysql:host='.$this->host[0].';dbname='.$this->Db.';charset='.$this->host[3],$this->host[1],$this->host[2]);
/*
考虑到我们的app以前客户端都是以字符串进行处理的,所以会造成新版写入缓存的时候是整型,客户端老版api返回后客户端会死掉,在新app的api接口没更换之前,先注释
查询mysql返回字段int变为String型解决方法
$this->Link_ID->setAttribute(PDO::ATTR_STRINGIFY_FETCHES,false);
$this->Link_ID->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);//启用或禁用预处理语句的模拟。默认true.如果设置false,可以在prepare阶段抛出不会终止程序的"Mysql error".
===说明:
上方$this->Link_ID->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);注释之后,则为默认值true,会导致$this->debugMode的时候无法抛出"Mysql error"
可以增加一处设置:$this->Link_ID->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
PDO::ATTR_ERRMODE的值请参考:https://www.php.net/manual/zh/pdo.setattribute.php
所以当PDO::ATTR_EMULATE_PREPARES为false,且PDO::ATTR_ERRMODE为PDO::ERRMODE_EXCEPTION的时候,抛PDOException错误(可捕获)
*/
//if($this->debugMode){不用这样判断,否则会导致无法获取异常信息,在需要显示异常信息的地方判断即可
$this->Link_ID->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//}
}
catch(PDOException $e){$this->Link_ID=0;die('mysql connect error...');}
$this->Link_ID && $this->Link_ID->query('set names '.$this->host[3].';');
}
}
/**
* 执行查询
* @param string $sql
* @param array $params 参数['column'=>$value,...]
* @return bool
*/
function query($sql='',$params=array()){
if(empty($sql)){return false;}
$this->connect();
$queryOK=false;
if($this->debugMode===true){
$this->debugSql=$sql;
$this->debugParams=$params;
}
//try catch需要配合`PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION`使用
try{
$PDOStatement=$this->Link_ID->prepare($sql,array(PDO::ATTR_CURSOR=>PDO::CURSOR_SCROLL));//返回 PDOStatement / False
if($PDOStatement){
//不能用$PDOStatement->execute($params);如果有int类型会报错
//应该用PDOStatement::bindValue()去绑定(注意: 不要用bindParam())
foreach($params as $key=>$value){
$PDOStatement->bindValue(':'.$key,$value,$this->getPdoValueType($value));//类型需要判断
}
$queryOK=$PDOStatement->execute();//bool
}
$this->PDOStatement=$PDOStatement;
}
catch(PDOException $e){
$this->PDOStatement=false;//异常也要设置为false
if($this->debugMode){
//explainPDOException会打印原型pdo语句,所以getDebugSql(true)可以打印拼接后的(默认为true打印拼接后的sql)
exit('<pre>'.implode(PHP_EOL,$this->explainPDOException($e)).'</pre>'.PHP_EOL.'<br />SQL: '.$this->getDebugSql());
}
}
return $queryOK;
}
/**
* 单条数据插入 (pdo绑定参数)
* @param string $table
* @param array $data 数据['column'=>$value,...]
* @return bool
*/
function insert($table,$data){
$arr_column=array_keys($data);
return $this->query('insert into '.$table.'('.implode(',',$arr_column).')values('.(':'.implode(',:',$arr_column)).')',$data);
}
/**
* 获取上次插入的id
* @return int|0
*/
function insert_id(){
if(!$this->PDOStatement){return 0;}
return intval($this->Link_ID->lastInsertId());
}
/**
* 单条数据插入并返回自增id (pdo绑定参数)
* @param string $table
* @param array $data 数据['column'=>$value,...]
* @return int|0
*/
function insert_get_id($table,$data){
$this->insert($table,$data);
return $this->insert_id();
}
/**
* 查询多笔记录
* @param string $sql
* @param array $params
* @return array 有结果时返回二维数组, 无则返回空数组 []
*/
function query_all($sql='',$params=array()){
if(!$this->query($sql,$params)){return array();}//不能直接return 0;否则当结果为零条的时候前端会出错
return $this->PDOStatement->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 查询一笔记录
* @param string $sql
* @param array $params
* @return array|false 有记录则返回一维数组, 无则返回 false
*/
function query_first($sql='',$params=array()){
if(!$this->query($sql,$params)){return false;}
return $this->PDOStatement->fetch(PDO::FETCH_ASSOC);
}
/**
* 获取影响的行数
* @return int|0
*/
function num_affected_rows(){
if(!$this->PDOStatement){return 0;}
return $this->PDOStatement->rowCount();
}
function beginTransaction(){$this->connect();$this->Link_ID->beginTransaction();}//开启事务
function rollBack(){$this->Link_ID->rollBack();}//回滚事务
function commit(){$this->Link_ID->commit();}//提交事务
private function getPdoValueType($value){
if(is_int($value)){return PDO::PARAM_INT;}
else if(is_bool($value)){return PDO::PARAM_BOOL;}
else if(is_null($value)){return PDO::PARAM_NULL;}
else{return PDO::PARAM_STR;}
}
private function explainPDOException($ex){
//code, message, file, line, trace
$arr=[];
$arr[]='PDOException:'.$ex->getCode().':'.$ex->getMessage();
$arr[]='File:'.$ex->getFile().':'.$ex->getLine();
$i=0;
//这里不用$ex->getTraceAsString(), 而是直接获取trace
//file, line [,class, type], funcion, args[0,1,2,...]
foreach($ex->getTrace() as $row){
$arr2=[];
$arr2[]=$row['file'].'('.$row['line'].'):';
if(isset($row['class'])){$arr2[]=$row['class'].$row['type'];}
$arr2[]=$row['function'];
if(isset($row['args']) && !empty($row['args'])){//参数 args
$arr2[]='(';
$k=0;
foreach($row['args'] as $arg){
if($k>0){$arr2[]=',';}
if(is_array($arg)){$arr2[]='['.trim(json_encode($arg,256),'{}').']';}
elseif(is_scalar($arg)){
if(is_string($arg)){$arr2[]='\'' .htmlspecialchars($arg).'\'';}
else{$arr2[]=htmlspecialchars((string)$arg);}
}
else{}//其他参数类型,暂时忽略
$k++;
}
$arr2[]=')';
}
$arr[]='#'.((string)$i).' '.implode('',$arr2);
$i++;
}
return $arr;
}
//打印sql语句($pdoAfter为false时候,打印pdo原型,比如:select * from table where ip=:ip
function getDebugSql($pdoAfter=true){
if($this->debugMode===true){
if(!$pdoAfter){return $this->debugSql;}
foreach($this->debugParams as $k=>$v){
$this->debugParams[':'.ltrim($k,':')]=$v;
unset($this->debugParams[ltrim($k,':')]);
}
$debugSql=$this->debugSql;
$debugParams_vals=array_values($this->debugParams);
$r=true;
$index=0;
while($r){
$r=stristr($debugSql,'?');
if(!empty($r)){
empty($debugParams_vals[$index]) && $debugParams_vals[$index]='NULL';
if(!is_numeric($debugParams_vals[$index])){$debugParams_vals[$index]=addslashes($debugParams_vals[$index]);}
$debugSql=preg_replace('/\?/','\''.$debugParams_vals[$index].'\'',$debugSql,1);
$index++;
}
}
$t=$this->debugParams;//php7可以直接在preg_replace_callback内部使用
return preg_replace_callback('/:[0-9a-zA-Z_]+/i',function($m) use ($t){
$v=$t[$m[0]];
if($v===null){return 'NULL';}
if(!is_numeric($v)){$v=addslashes($v);}
return "'{$v}'";
},$debugSql);
}
else {return 'no sql will return if undefind MYSQL_ERROR or MYSQL_ERROR!=true';}
}
}
先贴上mysql的普通类
<?php
class mydb{
private $Record=array();
private $Link_ID=0;
private $Query_ID=0;
private $Db;
function __construct($db){
$this->Db=$db;
$this->Link_ID=0;
}
function connect(){
if(!$this->Link_ID){
$this->Link_ID=mysql_connect('localhost','root','123456');
mysql_select_db($this->Db,$this->Link_ID);
mysql_query("set names 'utf8'",$this->Link_ID);
}
}
function insert_id(){
$this->connect();
return mysql_insert_id($this->Link_ID);
}
function query($sql){
$this->connect();
$this->Query_ID=mysql_query($sql);
return $this->Query_ID?$this->Query_ID:0;
}
function next_record() {
if(!$this->Query_ID) {return 0;}
$this->Record=mysql_fetch_assoc($this->Query_ID);
return is_array($this->Record)?$this->Record:0;
}
function query_first($sql){
if(!$this->query($sql)) {return 0;}
$this->Record=mysql_fetch_assoc($this->Query_ID);
return $this->Record;
}
function num_rows() {return mysql_num_rows($this->Query_ID);}
function num_affected_rows() {return mysql_affected_rows();}
function f($field) {return $this->Record[$field];}
}
?>
再贴上mysql pdo的类
<?php
/*
普通调用:
require 本类;
$mydb=new myDb($dbname);//$dbname为数据库名称
项目调用:
M()->query();//参考func/global.php中的function M();
*/
class myDb{
private $Record=array();
private $Link_ID=0;
private $Query_ID=0;
private $Db='';
private $host=array();
private $debugMode=false;
private $debugParams=null;
private $debugSql='';
function __construct($db='',$host=array()){
//判断是否允许debug
if(defined('MYSQL_ERROR') && MYSQL_ERROR===true){
$this->debugMode=true;
}
$this->Db=$db;
$this->Link_ID=0;
$this->host=$host;
if(count($host)<=0){
$configPath=dirname(dirname(__FILE__));
$dbconfig=$configPath.'/config/global/db.php';
if(is_file($dbconfig)){
require_once $dbconfig;
$this->host=$GLOBALS['dbHost']['default'];
}
else exit('undefind db');
}
}
function connect(){
if(!$this->Link_ID){
try{
$this->Link_ID=new PDO('mysql:host='.$this->host[0].';dbname='.$this->Db.';charset='.$this->host[3],$this->host[1],$this->host[2]);
//查询mysql返回字段int变为String型解决方法
$this->Link_ID->setAttribute(PDO::ATTR_STRINGIFY_FETCHES,false);
$this->Link_ID->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
}
catch(PDOException $e){$this->Link_ID=0;die('mysql connect error...');}
$this->Link_ID && $this->Link_ID->query('set names '.$this->host[3].';');
}
}
function query($sql='',$params=array()){
if($sql==''){return false;}
$queryOK=true;
$this->connect();
if($this->debugMode===true){
$this->debugSql=$sql;
$this->debugParams=$params;
}
$PDOStatement=$this->Link_ID->prepare($sql,array(PDO::ATTR_CURSOR=>PDO::CURSOR_SCROLL));//返回 PDOStatement / False
if(!$PDOStatement){
$queryOK=false;
}
else {
if(count($params)>0){
//不能用$queryOK=$this->Query_ID->execute($params);如果有int类型会报错
//应该用PDOStatement::bindValue()去绑定(注意: 不要用bindParam())
foreach($params as $key=>$value){
$PDOStatement->bindValue(':'.$key,$value,$this->getPdoValueType($value));//类型需要判断
}
}
$queryOK=$PDOStatement->execute();
}
if($queryOK===false && $this->debugMode===true){
$this->queryError();
}
$this->Query_ID=$PDOStatement;
return $queryOK;
}
function queryError(){
$errorInfo=$this->Link_ID->errorInfo();
if($errorInfo[2]!==null) {
$debug_backtrace=debug_backtrace();
$last_error_line=array('file' => __FILE__);
while(__FILE__==$last_error_line['file']){
$last_error_line=$debug_backtrace[0];
array_shift($debug_backtrace);
}
echo '<br /><b>Mysql error</b>: '.$errorInfo[2].' <b>in '.$last_error_line['file'].':line '.$last_error_line['line'].'</b><br />';
}
}
function getPdoValueType($value){
if(is_int($value)){return PDO::PARAM_INT;}
else if(is_null($value)){return PDO::PARAM_NULL;}
else if(is_bool($value)){return PDO::PARAM_BOOL;}
else{return PDO::PARAM_STR;}//string
}
//单条数据插入,$data为数组结构,比如array('title'=>$title,'content'=>$content)
function insert($table,$data){
$arr_column=array_keys($data);
return $this->query('insert into '.$table.'('.implode(',',$arr_column).')values('.(':'.implode(',:',$arr_column)).')',$data);
}
//单条数据插入,$data为数组结构,比如array('title'=>$title,'content'=>$content)
function insert_get_id($table,$data){
$this->insert($table,$data);
return $this->insert_id();
}
function next_record(){
if(!$this->Query_ID){return 0;}
$this->Record=$this->Query_ID->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT);
return is_array($this->Record)?$this->Record:0;
}
function query_all($sql='',$params=array()){
if(!$this->query($sql,$params)){return array();}//不能直接return 0;否则当结果为零条的时候前端会出错
$this->Record=$this->Query_ID->fetchAll(PDO::FETCH_ASSOC);
return $this->Record;
}
function query_first($sql='',$params=array()){
if(!$this->query($sql,$params)){return 0;}
$this->Record=$this->Query_ID->fetch(PDO::FETCH_ASSOC);
return $this->Record;
}
function insert_id(){$this->connect();return $this->Link_ID->lastInsertId();}
function num_rows(){return count($this->Query_ID->fetchAll());}
function num_affected_rows(){return $this->Query_ID->rowCount();}
function f($field){return $this->Record[$field];}
function beginTransaction(){$this->connect();$this->Link_ID->beginTransaction();}//开启事务
function rollBack(){$this->connect();$this->Link_ID->rollBack();}//回滚事务
function commit(){$this->connect();$this->Link_ID->commit();}//提交事务
//打印sql语句($pdoAfter为false时候,打印pdo原型,比如:select * from table where ip=:ip
function debugSql($pdoAfter=true){
if($this->debugMode===true){
if(!$pdoAfter){
return $this->debugSql;
}
foreach($this->debugParams as $k=>$v){
$this->debugParams[':'.ltrim($k,':')]=$v;
unset($this->debugParams[ltrim($k,':')]);
}
$debugSql=$this->debugSql;
$debugParams_vals=array_values($this->debugParams);
$r=true;
$index=0;
while($r){
$r=stristr($debugSql,'?');
if(!empty($r)){
empty($debugParams_vals[$index]) && $debugParams_vals[$index]='NULL';
is_numeric($debugParams_vals[$index]) or $debugParams_vals[$index]=addslashes($debugParams_vals[$index]);
$debugSql=preg_replace('/\?/','\''.$debugParams_vals[$index].'\'',$debugSql,1);
$index++;
}
}
$t=$this->debugParams;//php7可以直接在preg_replace_callback内部使用
return preg_replace_callback('/:[0-9a-zA-Z_]+/i',function($m) use ($t){
$v=$t[$m[0]];
if($v===null){
return 'NULL';
}
is_numeric($v) or $v=addslashes($v);
return "'{$v}'";
},$debugSql);
}
else {return 'no sql will return if undefind MYSQL_ERROR or MYSQL_ERROR!=true';}
}
}
近期评论