pdo mysql 封装类_自定义PDO封装类

最新推荐文章于 2022-12-31 23:55:21 发布

小云哥哥

最新推荐文章于 2022-12-31 23:55:21 发布

阅读量160

点赞数

文章标签： pdo mysql 封装类

本文链接：https://blog.youkuaiyun.com/weixin_29100399/article/details/113472402

版权

这是一个PHP数据库操作类库的实现，包括单例模式、构造函数、克隆禁止、数据库连接、查询、增删改查等方法。类库支持预处理SQL，提供事务处理、错误处理、数据表操作和字段检查等功能，适用于进行高效且安全的数据库交互。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

{protected static $_instance = null; //定义标识符(通过$_instance值得改变情况，来判定Model类是否被实例化)

protected $dbName = '';protected $dsn = '';protected $pdo = '';protected $prefix='wy_';protected $_table;protected $isShowSql=false;protected $tablesName=null;protected $al=[];//计算数组维度的临时数组

protected $where=[];//where数组

protected $sqlWhereParten;//wheresql片段

protected $whereOr=[];//whereOr数组

protected $sqlWhereOrParten;//whereOrsql片段

protected $selectField='*';//查询字段

protected $limit='';protected $order='asc';protected $group;protected $having;/**

* 析构方法

* final方法不能被继承

* 由于instance静态方法的原因__construct()只执行一次

* 第一次得到的对象后，在后面的访问直接返回该对象

* @param string $dbHost 服务器地址

* @param string $dbUser 数据库用户名

* @param string $dbPasswd 数据库密码

* @param string $dbName 操作的数据库名

* @param string $dbCharset 数据库字符集*/

private final function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset){try{$this->dbName=$dbName;$this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;$this->pdo = new PDO($this->dsn, $dbUser, $dbPasswd);$this->pdo->query("set names utf8"); //数据库utf8

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

}catch (PDOException $e) {$this->outputError($e->getMessage());

}

}/**

* 防止克隆

**/

private function__clone() {}/**

* 申明静态方法，由类名直接调用此方法，来实例化Model类

* 在静态方法中，绝不可以使用$this($this是对象产生之后才会有的)

* @param string $dbHost 服务器地址

* @param string $dbUser 数据库用户名

* @param string $dbPasswd 数据库密码

* @param string $dbName 操作的数据库名

* @param string $dbCharset 数据库字符集

* @return object 返回当前Db对象*/

public static function instance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)

{//实例化Model类

if (self::$_instance == null) {//self:代表自身(self=Model)

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

}return self::$_instance;

}/**

* destruct 关闭数据库连接*/

public functiondestruct()

{$this->dbh = null;

}/**

* 查询

* @param [type] $sql [description]

* @return [type] [description]*/

public function query($sql,$queryMode='All')

{$queryMode=strtolower($queryMode);$recordset = $this->pdo->query($sql);$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;

}/**

* 增改

* @param [type] $sql [description]

* @return [type] [description]*/

public function exec($sql)

{$resRow=$this->pdo->exec($sql);return $resRow;

}/**

* 执行预处理sql

* $sql="update good SET goods_store=goods_store-:num WHERE id=:id";

* $pdo->executeSql($sql,['num'=>1,'id'=>1]);

* @param [type] $sql [description]

* @param [type] $map [description]

* @return [type] [description]*/

public function executeSql($sql,$map=[])

{$stmt=$this->pdo->prepare($sql);/*$stmt->bindvalue(':num',1000);

$stmt->bindvalue(':id',2);

$stmt->bindParam(':num', $num,PDO::PARAM_INT);

$stmt->bindParam(':id', $id,PDO::PARAM_INT);

$res2=$stmt->execute();//成功返回行数

$res2=$stmt->execute();*/

$res=$stmt->execute($map);//成功返回行数

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

}public function insert($map=[])

{if(empty($table=$this->_table)){throw new Exception("Param Error: 缺少表名,执行插入操作前请调用table('tablename')获取");

}$dataPartten='';foreach ($map as $key => $value) {$dataPartten.=$key.'=:'.$key.',';

}$dataPartten=rtrim($dataPartten,',');$sql="INSERT INTO ".$this->prefix.$table.' SET '.$dataPartten;if($this->isShowSql){$this->isShowSql=false;return $sql;

}//INSERT INTO users (name, age) VALUES('姚明',25);

//INSERT INTO uses SET name = '姚明', age = 25;

$stmt=$this->pdo->prepare($sql);$res=$stmt->execute($map);//成功返回行数

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

}public function update($data=[])

{/** $sql="update good SET goods_store=goods_store-:num WHERE id=:id";

* $pdo->executeSql($sql,['num'=>1,'id'=>1]);*/

if($this->arrLevel($data)>1){$this->outputError('数组格式不正确');

}$sqlData='';foreach ($data as $field => $value) {$sqlData.=$field.'='.':'.$field.',';

}$sqlData=rtrim($sqlData,',');//$sql="update o2o_user SET mobile='15632613871',update_time={$time} WHERE id=11";

$sql="UPDATE ".$this->prefix.$this->_table.' SET '.$sqlData;if(!empty($this->where)){$sql.=' WHERE '.$this->sqlWhereParten;

}if(!empty($this->whereOr)){$sql.=' OR '.$this->sqlWhereOrParten;

}if($this->isShowSql){$this->isShowSql=false;return $sql;

}$stmt=$this->pdo->prepare($sql);$map=array_merge($this->where,$this->whereOr,$data);$res=$stmt->execute($map);//成功返回行数

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

}public function find($id='')

{if(empty($this->_table)){$this->outputError('表名为空请用table方法获取');

}if(!empty($this->sqlWhereOrParten)){$whereor=" OR ".$this->sqlWhereOrParten;

}else{$whereor='';

}$sql="select ".$this->selectField." FROM ".$this->prefix.$this->_table." WHERE ".$this->sqlWhereParten." ".$whereor." ".$this->group." ".$this->having." ".$this->order." ".$this->limit;if($this->isShowSql){$this->isShowSql=false;return $sql;

}$stmt=$this->pdo->prepare($sql);$map=array_merge($this->where,$this->whereOr);$stmt->execute($map);//成功返回true

$this->getPDOError();//查询多条数据

$result=$stmt->fetch(PDO::FETCH_ASSOC);//关联数组

return $result;

}public function select($id='')

{if(empty($this->_table)){$this->outputError('表名为空请用table方法获取');

}if(!empty($this->sqlWhereOrParten)){$whereor=" OR ".$this->sqlWhereOrParten;

}else{$whereor='';

}$stmt=$this->pdo->prepare($sql);$map=array_merge($this->where,$this->whereOr);$stmt->execute($map);//成功返回true

$this->getPDOError();//查询多条数据

$result=$stmt->fetchAll(PDO::FETCH_ASSOC);//关联数组

return $result;

}public functiondelete()

{//$sql = "delete from stu where id=?";

if(empty($this->_table)){$this->outputError('表名为空请用table方法获取');

}$sql="DELETE FROM ".$this->prefix.$this->_table;if(!empty($this->where)){$sql.=' WHERE '.$this->sqlWhereParten;

}if(!empty($this->whereOr)&&!empty($this->where)){$sql.=' OR '.$this->sqlWhereOrParten;

}if($this->isShowSql){$this->isShowSql=false;return $sql;

}$stmt = $this->pdo->prepare($sql);//准备sql模板

$map=array_merge($this->where,$this->whereOr);$stmt->execute($map);//执行预处理语句

$this->getPDOError();$affect_row = $stmt->rowCount();return $affect_row;

}public function setInc($field,$num=1)

{$field=strval($field);$num=intval($num);if(empty($this->_table)){$this->outputError('表名为空请用table方法获取');

}$sql="UPDATE ".$this->prefix.$this->_table." SET ".$field."=".$field."+:num ";if(!empty($this->where)){$sql.=' WHERE '.$this->sqlWhereParten;

}if(!empty($this->whereOr)&&!empty($this->where)){$sql.=' OR '.$this->sqlWhereOrParten;

}if($this->isShowSql){$this->isShowSql=false;return $sql;

}$stmt = $this->pdo->prepare($sql);//准备sql模板

$map=array_merge($this->where,$this->whereOr);$map['num']=$num;$stmt->execute($map);//执行预处理语句

$this->getPDOError();$affect_row = $stmt->rowCount();return $affect_row;

}/**

* 递减

* @param [type] $field [description]

* @param [type] $num [description]*/

public function setDec($field,$num)

{$field=strval($field);$num=intval($num);if(empty($this->_table)){$this->outputError('表名为空请用table方法获取');

}$sql="UPDATE ".$this->prefix.$this->_table." SET ".$field."=".$field."-:num ";if(!empty($this->where)){$sql.=' WHERE '.$this->sqlWhereParten;

}if(!empty($this->whereOr)&&!empty($this->where)){$sql.=' OR '.$this->sqlWhereOrParten;

}if($this->isShowSql){$this->isShowSql=false;return $sql;

}$stmt = $this->pdo->prepare($sql);//准备sql模板

$map=array_merge($this->where,$this->whereOr);$map['num']=$num;$stmt->execute($map);//执行预处理语句

$this->getPDOError();$affect_row = $stmt->rowCount();return $affect_row;

}public function field($field='*')

{$this->selectField=trim($field);return $this;

}public function limit($a,$b=''){//limit 10 等于 limit 0 10 查前10条

//limit 10 1000 查询10条以后的 1000条数据即 11-10010

$a=abs(intval($a));if(!empty($b)){$b=abs(intval($b));

}$sql= 'limit '.$a.','.$b;$sql=rtrim($sql,',');$this->limit=$sql;return $this;

}/*order('id,name[asc]');*/

public function order($order='asc')

{//默认 orderby id asc

//order by name,id desc

$order=trim(strval($order));$order=strtr($order,' ',',');//将' '替换成，

$arr=array_filter(explode(',',$order));$temp='';foreach ($arr as $key => $field) {$pos=stripos($field,'[');if($pos!==false){if($pos===0){unset($arr[$key]);

}else{$arrValue=substr($field,0,$pos);$arr[$key]=$arrValue;

}$ade=trim(substr($field,$pos),'[]');//当ade为默认的asc不取值去掉它

if($ade=='desc'){$temp=$ade;

}

}$order=implode($arr,',');if(!empty($order)){$sql="ORDER BY ".$order.' '.strtoupper($temp);$sql=trim($sql);

}else{$sql='';

}$this->order=$sql;return $this;

}public function group($field='')

{return $this;

}public functionhaving(){return $this;

}/**

* where (id=1 and name=1 ) or (id=2 and )

* @param [type] $where [description]

* @return [type] [description]*/

public function where($where)

{/*where id in(1,3,5)

where id>5 and id<10

where id between 40 and 50;*/

if($this->arrLevel($where)>2){$this->outputError('数组格式不正确');

}$sql='';$wh=[];foreach ($where as $field => $values){if(is_array($values)){if($values['0']=='range'){$arr=explode(',', $values[1],2);$newarr=[];$sql.=$field.' >:wh_'.$field.'Min AND '.$field.' <:wh_ and>

}else{$sql.=$field.' '.$values[0].':wh_'.$field.' AND ';$wh['wh_'.$field]=$values[1];

}

}elseif (is_string($values)||is_int($values)) {$sql.=$field.'='.':wh_'.$field.' AND ';$wh['wh_'.$field]=$values;

}else{$this->outputError('数组格式不正确');

}

}$sql='('.substr($sql,0,-5).')';$this->sqlWhereParten=null;$this->sqlWhereParten=$sql;$this->where=[];$this->where=$wh;return $this;

}/**

* where (id=1 and name=1 ) or (id=2 and )

* @param [type] $where [description]

* @return [type] [description]*/

public function whereOr($whereOr)

{if($this->arrLevel($whereOr)>2){$this->outputError('数组格式不正确');

}$sql='';$wh=[];foreach ($whereOr as $field => $values){if(is_array($values)){if($values['0']=='range'){$arr=explode(',', $values[1],2);$newarr=[];$sql.=$field.' >:whor_'.$field.'Min AND '.$field.' <:whor_ and>

}else{$sql.=$field.' '.$values[0].':whor_'.$field.' AND ';$wh['whor_'.$field]=$values[1];

}

}elseif (is_string($values)||is_int($values)) {$sql.=$field.'='.':whor_'.$field.' AND ';$wh['whor_'.$field]=$values;

}else{$this->outputError('数组格式不正确');

}

}$sql='('.substr($sql,0,-5).')';$this->sqlWhereOrParten=null;$this->sqlWhereOrParten=trim($sql);$this->whereOr=[];$this->whereOr=$wh;return $this;

}public function showSql($flag=false)

{$this->isShowSql=$flag;return $this;

}public function table($tablename='')

{$this->tableExists($tablename);if(!empty($tablename)){$this->_table=$tablename;

}return $this;

}/**

* 预处理

* @param string $sql [description]

* @return [type] [description]*/

public function prepare($sql=''){return $this->pdo->prepare($sql);

}/**

* 执行预处理语句

* @param [type] $presql [description]

* @return [type] [description]*/

public function execute($presql){return $this->pdo->execute($presql);

}/**

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

* @param String $table

* @param array $arrayField*/

private function checkFields($table, $arrayFields)

{//没必要加上$this->tableExists($table); getField有啦

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

{$this->tableExists($table);$fields = array();$sql= "SHOW COLUMNS FROM ".$this->prefix.$table;$recordset = $this->pdo->query($sql);$this->getPDOError();$recordset->setFetchMode(PDO::FETCH_ASSOC);$result = $recordset->fetchAll();foreach ($result as $rows) {$fields[] = $rows['Field'];

}return $fields;

}/**

* 获取当前连接库的所有表名

* @param string $dbname [description]

* @return [type] [description]*/

public function getTableNames($dbname='')

{if($this->tablesName!==null&&is_array($this->tablesName)){return $this->tablesName;

}$dbname=$this->dbName;$sql="select table_name from information_schema.tables where table_schema='$dbname'";$data=$this->query($sql);$this->getPDOError();$prefix=$this->prefix;$prefixLength=strlen($prefix);$tables=[];foreach ($data as $key => $val) {if(!empty($prefix)){$tables[]=substr($val['table_name'],$prefixLength);

}else{$tables[]=$val['table_name'];

}

}$this->tablesName=$tables;return $tables;

}/**

* 检测当前连接数据库是否存在该表名

* @param string $tablename 查询的表名

* @return boolean or Exception 返回true或者抛出异常*/

public function tableExists($tablename)

{if(in_array($tablename,$this->getTableNames())){return true;

}$this->outputError("当前数据库没有此表[{$this->prefix}$tablename],请核对表名是否正确");

}/**

* 检查是否在一个事务内

* @return [type] [description]*/

public functioninTransaction()

{if($this->pdo->inTransaction()){return true;

}else{return false;

}

}/**

* 获取表引擎

* @param String $tableName 表名

* @param String $dbName 库名

* @param Boolean $debug

* @return String*/

public function getTableEngine($tableName,$dbName='')

{if(empty($dbName)){$dbName=$this->dbName;

}$this->tableExists($tableName);$strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$this->prefix.$tableName."'";$arrayTableInfo = $this->query($strSql,'all');$this->getPDOError();return $arrayTableInfo[0]['Engine'];

}/**

* 获取mysql支持的引擎

* @return [type] [description]*/

public functiongetEngines()

{$sql="show Engines";$resInfo=$this->query($sql);$this->getPDOError();return $resInfo;

}/**

* 获取建表默认引擎

* @return [type] [description]*/

public functiongetDefaultEngines()

{$sql="show variables like 'default_storage_engine'";$resInfo=$this->query($sql);$this->getPDOError();return $resInfo[0]['Value'];

}/**

* 设置数据表的引擎

* @param [type] $table 表名

* @param string $value MyISAM/InnoDB/...

* @return [type] [description]*/

public function setTableEngine($table,$value='InnoDB')

{$this->tableExists($table);$engines=$this->getEngines();$newArr=[];foreach ($engines as $key => $engineMsgArr) {if($engineMsgArr['Support']==='YES'||$engineMsgArr['Support']==='DEFAULT'){$newArr[]=strtolower($engineMsgArr['Engine']);

}

}if(!in_array(strtolower($value),$newArr)){$this->outputError('mysql目前不支持该引擎');

}$sql="ALTER TABLE {$this->prefix}$table ENGINE = $value";$res=$this->pdo->exec($sql);$this->getPDOError();return $res;//返回受影响行数有多少数据就返回多少数字

}/**

* 获取自动提交的状态 1开启自动提交 0关闭自动提交

* PDO::ATTR_AUTOCOMMIT 0

* @return [type] [description]*/

public functiongetAttrAutoCommit()

{return $this->pdo->getAttribute(PDO::ATTR_AUTOCOMMIT);

}/**

* 设置自动提交的状态 1开启自动提交 0关闭自动提交

* PDO::ATTR_AUTOCOMMIT 0

* @return [type] [description]*/

public function setAttrAutoCommit($value=true)

{$this->isBoolean();return $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,$value);

}/**

* 获取当前mysql支持事务的引擎

* @return [type] [description]*/

public functiongetTransactionEngine()

{$sql="SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES WHERE SUPPORT IN ('YES','DEFAULT') AND TRANSACTIONS='YES'";$data=$this->query($sql);$this->getPDOError();return $data;

}/**

* 开启事务

* @param [type] $table [description]

* @return [type] [description]*/

public function beginTransaction($table){$this->tableExists($table);$engine=$this->getTableEngine($table);$engineLower=strtolower($engine);switch ($engineLower) {case 'innodb':

case 'bdb'://5.1一下版本才支持 Berkley的别名

case 'berkley'://5.1一下版本才支持

return $this->pdo->beginTransaction();break;case 'myisam'://8.0被废弃

case 'isam'://过时的存储引擎，现在由MyISAM取代

case 'mrg_myisam':

case 'merge'://MRG_MYISAM的别名

case 'mrg_isam'://过时的存储引擎，现在由MERGE取代

case 'csv':

case 'performance_schema':

case 'archive':

case 'memory':

case 'heap'://MEMORY正式地被确定为HEAP引擎

case 'blackhole':

case 'federated':

case 'ndb':

case 'example':

case 'ndbcluster'://NDB别名

$this->outputError("当前表引擎为$engine,目前不支持事务");break;default:

$this->outputError("当前表引擎为$engine,请核对是否支持事务");break;

}

}public functioncommit(){return $this->pdo->commit();

}public functionrollBack(){return $this->pdo->rollBack();

}/**

* 设置错误级别

* 0 ERRMODE_SILENT 默认模式，不主动报错，需要主动以 $pdo->errorInfo()的形式获取错误信息

* 1 ERRMODE_WARNING 显示警告错误引发 E_WARNING 错误，主动报错

* 2 ERRMODE_EXCEPTION 主动抛出 exceptions 异常，需要以try{}cath(){}输出错误信息

* 3 PDO::ATTR_ERRMODE

* @param integer $value 错误级别的值

* @return boolean 成功返回true，失败返回false*/

public function setAttrErrMode($value=0)

{if($value==1){$res=$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);

}elseif ($value==2) {$res=$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

}else{$res=$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);

}return $res;

}/**

* 获取错误级别

* @return integer 返回错误级别*/

public functiongetAttrErrMode()

{return $this->getAttribute(PDO::ATTR_ERRMODE);

}/**

* 设置pdo属性值

* @param integer $name 属性名

* @param mixd $value true or Exception 返回布尔值或抛出异常*/

public function setAttribute($name,$value)

{$this->isInteger($name);try{$res=$this->pdo->setAttribute($name,$value);return $res;

}catch (PDOException $e) {$this->outputError($e->getMessage());

}

}/**

* /

* @param $name [description]

* @return [type] [description]*/

public function getAttribute($name)

{$this->isInteger($name);return $this->pdo->getAttribute($name);

}public function isInteger($name){if(!is_int($name)){throw new Exception("Param Error: 第一个参数不是期望的整形");

}

}public function isBoolean ($name){if(!is_bool($value)||$value!==1||$value!==0){throw new Exception("Param Error: 不是期望的布尔参数");

}

}/**

* 是否开启长链接

* 什么情况下使用长连接？

* 不同的脚本的执行间距非常短，同时每个脚本都要操作数据库(Mysql：mysql_pconnect())

* @param [type] $value [description]

* @return boolean [description]*/

public function isAttrPersistent($value=false)

{$this->isBoolean();return $this->pdo->setAttribute(PDO::ATTR_PERSISTENT,$value);

}/**

* 设置默认的数据提取模式

* PDO::FETCH_ASSOC 2

* @param integer $value [description]*/

public function setAttrDefaultFetchMode($value=PDO::FETCH_BOTH)

{$this->isInteger();/*setFetchMode($value)*/

return $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,$value);

}/**

* 设置数据的提取模式

* @param integer $value [description]*/

public function setFetchMode($value=2)

{$this->pdo->setFetchMode($value);

}/**

* 输出错误信息

* @param String $strErrMsg*/

private function outputError($strErrMsg)

{echo '

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

}/**

* getPDOError 捕获PDO错误信息*/

private functiongetPDOError()

{if ($this->pdo->errorCode() != '00000') {$arrayError = $this->pdo->errorInfo();$this->outputError($arrayError[2]);

}

}public function dump($value='')

{echo '

';var_dump($value);

}public function aL($data,&$al,$level=0)

{if(is_array( $data)){$level++;$al[] = $level;foreach($data as $v){$this->aL($v,$al,$level);

}

}public function arrLevel($where)

{$this->al=[];//去掉缓存

$this->aL($where,$this->al,$level=0);return max($this->al);

}