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;
}
}?>