<?php
abstract class AbstractDB {
private $m_pdo;
public $m_stmt;
private static $empty_array = array();
public function open(){
try {
$this->m_pdo = new PDO('mysql:host=127.0.0.1;dbname=MYDB', 'root', '123456', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';"));
return true;
}
catch (PDOException $e){
return false;
}
} public function close(){
$this->m_stmt = null;
$this->m_pdo = null;
} public function prepare_statement($prepare_statement_text){
if (empty($this->m_pdo))
return;
if(!empty($this->m_stmt)){
$this->m_stmt->closeCursor();
}
$this->m_stmt = $this->m_pdo->prepare($prepare_statement_text);
}
public function set_param($param_name, $param_value){
if (empty($this->m_stmt))
return;
$this->m_stmt->bindParam($param_name, $param_value);
} public function execute_single_row(){
if (empty($this->m_stmt))
return self::$empty_array; if (!$this->m_stmt->execute()){
return self::$empty_array;
}
return $this->m_stmt->fetch(PDO::FETCH_ASSOC);
}
public function execute_rows(){
if (empty($this->m_stmt))
return self::$empty_array;
if (!$this->m_stmt->execute())
{
$error = $this->m_stmt->errorInfo();
return self::$empty_array;
}
$this->m_stmt->setFetchMode(PDO::FETCH_ASSOC);
return $this->m_stmt->fetchALL();
}
}
class TCategoryAttrDB extends AbstractDB{
public function get_category_attr_list($arr_params){
$sql = 'select category_id,category_name,attrib_name,attrib_content from cat_attrib where category_id = :category_id and is_delete = 0';
$this->prepare_statement($sql);
$this->set_param(':category_id', $arr_params['category_id']);
return $this->execute_rows();
} public function getCategoryIdFromTCategory($arr_params) {
$sql = 'SELECT category_id FROM `t_category` where category_name = :category_name and is_delete = 0
and parent_id = (SELECT category_id from t_category WHERE category_name = :parent_category_name and is_delete = 0);';
$this->prepare_statement($sql);
$this->set_param(':parent_category_name', $arr_params['parent_category_name']);
$this->set_param(':category_name', $arr_params['category_name']);
return $this->execute_single_row();
}
}
$t_db = new TCategoryAttrDB();
$arr_params = array('parent_category_name' => 'XXXX', 'category_name' => 'YYY');
if($t_db->open()){
$category = $t_db->getCategoryIdFromTCategory($arr_params);
$result = $t_db->get_category_attr_list($category);
}
?>
上面的代码是我提取出来的关键代码,在我的开发机上,当执行第二条SQL的prepare()方法会返回false而不是PDOStatement对象,errorInfo输出的信息是:Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute."
但是这个代码在生产环境是没有问题的,我试过用下面的方法解决我开发机的问题都成功了,如下://(1)在PDO初始化时候设置MYSQL_ATTR_USE_BUFFERED_QUERY属性,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>1
//(2)在prepare_statement函数中添加下面代码,就是在prepare前执行closeCursor()
if(!empty($this->m_stmt)){
$this->m_stmt->closeCursor();
}现在有几点疑惑想请教:
1.我的PHP版本是5.2.10,生产环境的PHP版本是5.3.11,但是到底是什么差异导致这个结果的不一致?只是PHP版本吗?
2.PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (available in MySQL): Use buffered queries。这是文档对属性的解释,但是还不是很明白。什么叫用缓存查询,缓存的对象是什么,用多大的内存,占用的大小跟什么有关系。
3.添加PDOStatement::closeCursor 对性能的影响有多大(当并发高的时候)?
PDO 被设计成单用户方式,每次查询后都应读完数据再复用对象
这样不就不能发挥出php5.3的优势了吗?(至少php5.3要比php5.2快5倍,php5.4比php5.3快10倍)