c mysql 封装类_封装完美mysql类

protected $_link; //数据库连接资源

protected $dbhost; //主机

protected $dbuser; //用户名

protected $dbpwd; //用户密码

protected $dbname; //数据库名

protected $dbcharset = 'utf8'; //字符集

protected $pconnect; //是否持久连接, 默认不是

protected $db_cache_tpl; //数据库缓存位置

protected $is_cache = true; //默认缓存, (对表字段缓存)

protected $max_cache_time = 1800; //默认tpl查询结果缓存30分钟

protected $select_cache = false; //是否开启查询缓存

protected$error_log;protected$error_path;protected$sql_log;protected$sql_path;protected $query_time = 0; //sql语句执行时间记录

protected $start_time; //类初始化时间

protected $exe_time; //sql执行时间

/*不允许实例化和修改构造函数*/finalprotectedfunction __construct($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset, $db_cache_tpl, $pconnect, $select_cache, $max_cache_time,$error_path, $error_log, $sql_path, $sql_log) {

$this->start_time =time();

$this->dbhost =$dbhost;

$this->dbuser =$dbuser;

$this->dbpwd =$dbpwd;

$this->dbname =$dbname;if($dbcharset)

{

$this->dbcharset = strtolower(str_replace('-', '', $dbcharset));

}

$this->pconnect =$pconnect;if($db_cache_tpl)

{

$this->db_cache_tpl = $db_cache_tpl . md5($dbhost . $dbuser . $dbpwd . $dbname); //如果为空, 则不缓存

$this->is_cache = true;

}

$this->select_cache =$select_cache;

$this->max_cache_time =$max_cache_time;/*数据库日志记录*/$this->db_log($error_path, $error_log, $sql_path, $sql_log);/*连接数据库*/$this->connect();/*选择数据库*/$this->select_db();/*设置字符集*/$this->setchar();/*数据库查询缓存*/

if ($this->select_cache)

$this->db_sel_cache();

}/*形成单例和不允许修改*/finalpublic static function getDb($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset = 'utf8', $db_cache_tpl = '', $pconnect = false, $select_cache = false, $max_cache_time = 300, $error_path = '', $error_log = '', $sql_path = '', $sql_log = '') {if(Mysql::$self instanceof Db)returnMysql::$self;

Mysql::$self= newMysql($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset, $db_cache_tpl, $pconnect, $select_cache, $max_cache_time, $error_path, $error_log, $sql_path, $sql_log);returnMysql::$self;

}protectedfunction connect() {if ($this->pconnect)

{

$this->_link = @mysql_pconncect($this->dbhost, $this->dbuser, $this->dbpwd);if (!$this->_link || !$this->ping() && DEBUG) //如果连接失败则重新连接

{//记录到错误日志

Log::write('数据库连接失败, 请管理员检查原因', ROOT_PATH . $this->error_path, $this->error_log);return false;

}

}else{

$this->_link = @mysql_connect($this->dbhost, $this->dbuser, $this->dbpwd);if (!$this->_link || !$this->ping() &&DEBUG)

{//记录到错误日志

Log::write('数据库连接失败, 请管理员检查原因', ROOT_PATH . $this->error_path, $this->error_log);return false;

}

}

}protectedfunction select_db() {if (!@mysql_select_db($this->dbname, $this->_link) &&DEBUG)

{//记录到错误日志

Log::write('数据库字符集设置失败, 请管理员检查原因', ROOT_PATH . $this->error_path, $this->error_log);return false;

}

}protectedfunction setchar() {if (!@mysql_set_charset($this->dbcharset, $this->_link) &&DEBUG)

{//记录到错误日志

Log::write('选择数据库失败', ROOT_PATH . $this->error_path, $this->error_log);return false;

}

}//查询不缓存

protectedfunction db_not_cache($sql, $is_cache) {if (!$is_cache)

{

$sql= 'select sql_no_cache'. $sql;

}return$sql;

}//开启mysql查询缓存

protectedfunction db_sel_cache() {//是否开启查询缓存

$res = $this->getAll('show variables like "have_query_cache"');if (empty($res) && strtolower($res[0]['Value']) != 'yes')

$this->query('set session query_cache_type = on');//设置查询缓存大小

$size = $this->getAll('show variables like "query_cache_size"');if (empty($size) && $size[0]['value'] !=QUERY_CACHE_SIZE)

$this->query('set @@global.query_cache_size='. QUERY_CACHE_SIZE);//设置每次最大缓存值

$limit = $this->getAll('show variables like "query_cache_limit"');if (empty($limit) && $limit[0]['Value'] !=QUERY_CACHE_LIMIT)

$this->query('set @@global.query_cache_limit='. QUERY_CACHE_LIMIT);

}//清除缓存碎片

function flush_cache() {return $this->query('flush query cache');

}//清除查询缓存

function reset_cache() {return $this->query('reset query cache');

}//对表字段进行缓存

protectedfunction cache_table_fields($table) {//生成缓存文件

$filename = $this->db_cache_tpl . $table . '.php';if (!file_exists($filename))

{

$fields= serialize($this->getFields($table));if (!is_dir(ROOT_PATH . DB_CACHE_TPL))

mkdir(ROOT_PATH . DB_CACHE_TPL,0777, true);

touch($filename);

file_put_contents($filename, $fields);returnunserialize($fields);

}else{

$fields=file_get_contents($filename);returnunserialize($fields);

}

}//清除表字段缓存

function clear_table_cache() {

$tables= $this->getTables();foreach ($tables as$t)

{

$this->clear_one_table($t['Tables_in_'. DB_NAME]);

}return true;

}//清除某张表字段的缓存

function clear_one_table($table) {

$filename= $this->db_cache_tpl . $table . '.php';if(file_exists($filename))

{if(!unlink($filename))return false;

}

}/*数据库日志记录*/

protectedfunction db_log($error_path, $error_log, $sql_path, $sql_log) {

$this->error_path = !$error_path ? '': $error_path;

$this->error_log = !$error_log ? '': $error_log;

$this->sql_path = !$sql_path ? '': $sql_path;

$this->sql_log = !$sql_log ? '': $sql_log;

}/*SQL语句执行函数*/

protectedfunction query($sql) {if (!$this->_link)

{

$this->_link = mysql_connect($this->dbhost, $this->dbuser, $this->dbname) || $this->ping();

}//记录sql执行时间的微秒数

if (!$this->query_time)

{

$this->query_time = microtime(true);

}//如果当前时间大于类初始化时间, 自动执行ping操作进行连接

if ($this->start_time

{

$this->ping();

}

Log::write($sql, ROOT_PATH . $this->sql_path, $this->sql_log, $this->exe_time, true); //sql语句记录

if (!($query = mysql_query($sql, $this->_link)))

{if(SQL_LOG)

{

Log::write("mysql_error:" . $this->error() . "----mysql_errno:" . $this->errno(), ROOT_PATH . $this->sql_path, $this->sql_log); //sql错误记录

}return false;

}

$this->exe_time();return$query;

}/*返回sql执行时间*/

protectedfunction exe_time() {

$this->exe_time = round(microtime(true) - $this->query_time, 2); //对sql执行时间保留两个小数位

}protectedfunction error() {return mysql_error($this->_link);

}protectedfunction errno() {return mysql_errno($this->_link);

}protectedfunction ping() {return mysql_ping($this->_link);

}

function affected_rows() {return mysql_affected_rows($this->_link);

}

function num_rows($query) {returnmysql_num_rows($query);

}

function num_fields($query) {returnmysql_num_fields($query);

}protectedfunction free_result($query)

{returnmysql_free_result($query);

}

function insert_id() {return mysql_insert_id($this->_link);

}protectedfunction fetch_assoc($query) {returnmysql_fetch_assoc($query);

}/*对sql语句进行转义*/

protectedfunction escape_string($sql) {returnmysql_real_escape_string($sql);

}protectedfunction close() {returnmysql_close($sql);

}/*组建limit, 默认取一行*/function limit($sql, $table, $num= 1, $start = 0, $is_cache = true) {if (!$is_cache && $this->select_cache)

$sql= $this->db_not_cache($sql, $is_cache);if ($start == 0)

{

$sql .= 'LIMIT'. $num;

}else{

$sql .= 'LIMIT' . $start . ',' . ($start +$num);

}if ($query = $this->query($sql))

{if ($this->num_rows($query) == 1)

{

$res=mysql_fetch_row($query);

$this->free_result($query);return$res;

}

$res=array();while ($row =mysql_fetch_assoc($query))

{

$res[]=$row;

}

$this->free_result($query);return$res;

}

}/*返回一行*/function getRow($sql, $is_cache= true) {return $this->limit($sql, $is_cache);

}/*返回多行*/function getAll($sql, $is_cache= true) {if (!$is_cache && $this->select_cache)

$sql= $this->db_not_cache($sql, $is_cache);

$query= $this->query($sql);

$res=array();while ($row =mysql_fetch_assoc($query))

{

$res[]=$row;

}

$this->free_result($query);return$res;

}/*返回一个*/function getOne($sql, $is_cache=true) {if (!$is_cache && $this->select_cache)

$sql= $this->db_not_cache($sql, $is_cache);

$query= $this->query($sql);

$row=mysql_fetch_row($query);if($row)

{

$this->free_result($query);return $row[0];

}else{return '';

}

}/*返回一列*/function getCol($sql, $is_cache= true) {if (!$is_cache && $this->select_cache)

$sql= $this->db_not_cache($sql, $is_cache);

$query= $this->query($sql);

$res=array();while ($row =mysql_fetch_row($query))

{

$res[]= $row[0];

}

$this->free_result($query);return$res;

}/*自动insert or update

@params $fileds array 插入字段

$where string 插入条件

$table string 表名

$type string 更新或者插入

@return bool*/function _auto($fields, $table, $type="insert", $where = '') {if ($this->is_cache)

$fields_name= $this->cache_table_fields($table);else$fields_name= $this->getFileds($table);

$sql= '';if ($type == 'insert')

{

$insert= $values =array();foreach ($fields_name as $k =>$v)

{if(array_key_exists($v, $fields))

{

$insert[]=$v;

$values[]=$fields[$v];

}

}if (!empty($insert) && $where == '')

{

$sql= 'INSERT INTO' . $table . '(' . implode(',', $insert) . ')' . 'VALUES ("' . implode('", "', $values) . '")';

}else{

$sql= '';

}

}

elseif ($type== 'update')

{

$sets=array();foreach ($fields_name as $k =>$v)

{if(array_key_exists($v, $fields))

{

$sets[]= $v . '="' . $fields[$v] . '"';

}

}

$sql= 'UPDATE' . $table . 'SET' . implode(',', $sets);if ($where)

$sql .= 'WHERE' . $where;

}if ($sql && $this->query($sql))

{return true;

}else{return false;

}

}/*获取表字段信息*/

protectedfunction getFields($table) {return $this->getCol('DESC'. $table);

}/*获取表*/

protectedfunction getTables() {return $this->getAll('show tables');

}/*清理数据库所有碎片*/function optimize_db() {

$tables= $this->getTables();foreach ($tables as$t)

{

$this->optimize_table($t['Tables_in_'. DB_NAME]);

}return true;

}/*清理表碎片*/function optimize_table($table) {if (!$this->query('optimize table .'. $table))return false;return true;

}/*任何sql均可执行, 主要用于后台admin管理员对数据库的操作*/function _query($sql, $is_cache= true) {if (!$is_cache && $this->select_cache)

$sql= $this->db_not_cache($sql);

$query= $this->query($sql);if(is_resource($query))

{if ($this->num_rows($query) > 1)

{

$res=array();while ($row =mysql_fetch_assoc($query))

{

$res[]=$row;

}

$this->free_result($query);return$res;

}

elseif($this->num_rows($query) == 1)

{if($row =mysql_fetch_assoc($query))

{

$this->free_result($query);return$row;

}

}else

return false;

}

elseif (is_bool($query))return$query;

}/*查询缓存文件*/

protectedfunction get_query_cache($file, $key) {if(file_exists($file))

{

$content=file_get_contents($file);

$content= preg_replace("/[\r\n]+/", "", $content);

preg_match("/\|\|". $key . "=>(.*)\|\|$/", $content, $res); //正则不能匹配出中文, 明天解决

if(empty($res))return false;return$res;

}else{return false;

}

}/*生成缓存文件*/

protectedfunction set_query_cache($file, $key, $value) {

$f= fopen($file, 'a+');

$content= "||" . $key . "=>" . $value . "||"; //加||分隔符

fwrite($f, $content);

}/*-------------------------------------------------

-------------------------------------------------

以下函数是将查询结果缓存至文件

notice: 缓存中的$key必需指定为唯一

-------------------------------------------------

-------------------------------------------------*/

/*解析缓存文件*/

protectedfunction set_cache_tpl($table) {if(DB_TPL_CACHE)

{

$file=ROOT_PATH . DB_TPL_FILE;if (!is_dir($file))

mkdir($file,0777, true);

$file .= md5($this->dbhost . $this->dbuser . $this->dbpwd . $this->dbname) . $table . '.php';

}return$file;

}/*查询一行缓存

@params $sql string sql语句

$is_cache bool 是否从数据库取出数据, 而不是从缓存文件中取

@$key string 缓存键值*/function limit_cache($sql, $table, $key, $num= 1, $start = 0, $is_cache = true)

{if (!$is_cache)return $this->limit($sql, $table, $num, $start);

$file= $this->set_cache_tpl($table);if ($start == 0)

{

$sql .= 'LIMIT'. $num;

}else{

$sql .= 'LIMIT' . $start . ',' . ($start +$num);

}if(!($info = $this->get_query_cache($file, $key)))

{

$info= $this->_query($sql);if (!($info ===html_encode($info)))return $info; //如果其中有html代码, 将不进行编码

$this->set_query_cache($file, $key, json_encode($info));return$info;

}return json_decode($info[1]);

}/*获取一行缓存*/function getRow_cache($sql, $table, $key, $is_cache= true) {if (!$is_cache)return $this->getRow($sql);

$file= $this->set_cache_tpl($table);if (!($info = $this->get_query_cache($file, $key)))

{

$info= $this->getRow($sql);if (!($info ===html_encode($info)))return$info;

$this->set_query_cache($file, $key, serialize($info));return$info;

}return unserialize($info[1]);

}/*获取一个缓存*/function getOne_cache($sql, $table, $key, $is_cache= true) {if (!$is_cache)return $this->getOne($sql);

$file= $this->set_cache_tpl($table);if (!($info = $this->get_query_cache($file, $key)))

{

$info= $this->getOne($sql);if (!($info ===html_encode($info)))return$info;

$this->set_query_cache($file, $key, serialize($info));return$info;

}return unserialize($info[1]);

}/*获取一列的缓存*/function getCol_cache($sql, $table, $key, $is_cache= true) {if (!$is_cache)return $this->getCol($sql);

$file= $this->set_cache_tpl($table);if (!($info = $this->get_query_cache($file, $key)))

{

$info= $this->getCol($sql);if (!($info ===html_encode($info)))return$info;

$this->set_query_cache($file, $key, serialize($info));return$info;

}return unserialize($info[1]);

}/*获取多行缓存数据*/function getAll_cache($sql, $table, $key, $is_cache= true) {if (!$is_cache)return $this->getAll($sql);

$file= $this->set_cache_tpl($table);if (!($info = $this->get_query_cache($file, $key)))

{

$info= $this->getAll($sql);if (!($info ===html_encode($info)))return$info;

$this->set_query_cache($file, $key, serialize($info));return$info;

}return unserialize($info[1]);

}/*清除缓存数据

@params $file @string 清除哪张表的缓存

$key @string 清除缓存文件中哪个键的缓存

$fileAll @bool 是否整个文件缓存全部清除*/function clear_cache($table, $key= '', $fileAll= false) {

$file= $this->set_cache_tpl($table); //获取缓存文件名

if($fileAll)

{if (!$file)return true; //缓存文件不存在, 不必清除

else

return@unlink($file);

}

$content=file_get_contents($file);if(is_array($key))

{foreach($key as$v) {

$content= preg_replace("/\|\|" . $v . "=>.*\|\|/", "", $content);

}returnfile_put_contents($file, $content);

}else{

$content= preg_replace("/\|\|" . $key . "=>.*\|\|/", "", $content);

file_put_contents($file, $content);

}

}/*清除所有文件缓存, 将删除缓存文件夹下的所有文件*/function clear_all($dir) {if(is_dir($dir))

{

$d=opendir($dir);

$filename=array();while (($file = readdir($d)) !== false)

{if ($file != "." && $file != ".." &&$file)

$filename[]=$file;

}

}

closedir($d);foreach ($filename as$f)

{

@unlink($dir . $f);

}return true;

}

}?>