本人利用业余时间，自己在开发项目，框架还在搭建中，完全写完估计要两到三个月。先分享一下刚写完的mysql类，因为代码比较多，相关配置常量就不贴出来了。只贴出MYSQL核心代码。欢迎BUG指正，见议提出
<?php /* 系统核心数据库类 */ defined('IN_SHOP') or die(); class Mysql { private static $self; //数据库实例本身 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执行时间 /* 不允许实例化和修改构造函数 */ final protected function __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(); } /* 形成单例和不允许修改 */ final public 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) return Mysql::$self; Mysql::$self = new Mysql($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset, $db_cache_tpl, $pconnect, $select_cache, $max_cache_time, $error_path, $error_log, $sql_path, $sql_log); return Mysql::$self; } protected function 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; } } } protected function select_db() { if (!@mysql_select_db($this->dbname, $this->_link) && DEBUG) { //记录到错误日志 Log::write('数据库字符集设置失败, 请管理员检查原因', ROOT_PATH . $this->error_path, $this->error_log); return false; } } protected function setchar() { if (!@mysql_set_charset($this->dbcharset, $this->_link) && DEBUG) { //记录到错误日志 Log::write('选择数据库失败', ROOT_PATH . $this->error_path, $this->error_log); return false; } } //查询不缓存 protected function db_not_cache($sql, $is_cache) { if (!$is_cache) { $sql = 'select sql_no_cache ' . $sql; } return $sql; } //开启mysql查询缓存 protected function 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'); } //对表字段进行缓存 protected function 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); return unserialize($fields); } else { $fields = file_get_contents($filename); return unserialize($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; } } /* 数据库日志记录 */ protected function 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语句执行函数 */ protected function 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 < 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执行时间 */ protected function exe_time() { $this->exe_time = round(microtime(true) - $this->query_time, 2); //对sql执行时间保留两个小数位 } protected function error() { return mysql_error($this->_link); } protected function errno() { return mysql_errno($this->_link); } protected function ping() { return mysql_ping($this->_link); } function affected_rows() { return mysql_affected_rows($this->_link); } function num_rows($query) { return mysql_num_rows($query); } function num_fields($query) { return mysql_num_fields($query); } protected function free_result($query) { return mysql_free_result($query); } function insert_id() { return mysql_insert_id($this->_link); } protected function fetch_assoc($query) { return mysql_fetch_assoc($query); } /* 对sql语句进行转义 */ protected function escape_string($sql) { return mysql_real_escape_string($sql); } protected function close() { return mysql_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; } } /* 获取表字段信息 */ protected function getFields($table) { return $this->getCol('DESC ' . $table); } /* 获取表 */ protected function 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; } /* 查询缓存文件 */ protected function 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; } } /* 生成缓存文件 */ protected function set_query_cache($file, $key, $value) { $f = fopen($file, 'a+'); $content = "||" . $key . "=>" . $value . "||"; //加||分隔符 fwrite($f, $content); } /* ------------------------------------------------- ------------------------------------------------- 以下函数是将查询结果缓存至文件 notice: 缓存中的$key必需指定为唯一 ------------------------------------------------- ------------------------------------------------- */ /* 解析缓存文件 */ protected function 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); } return file_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; } } ?>
938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
