一个小工程的准备工作 - Php MySql Class
我比较喜欢的一个。
<?php
/**
* 数据库操作类
*
* @author Moyo
* @package defaultPackage
*/
class Mysql
{
// 默认配置
private $_config_default = array
(
'debug' => false,
'host' => 'localhost:3306',
'username' => 'root',
'password' => '',
'database' => 'mysql',
'prefix' => '',
'charset' => 'utf-8',
'cached' => 'file://{root}/query_cache/'
);
public $CACHE_HASH_SALT = 'sql.cache.uuland.org';
public $CLIENT_MULTI_RESULTS = 131072;
// 配置信息
private $_config = array();
private $_debug = true;
private $_host = '';
private $_username = '';
private $_password = '';
private $_database = '';
private $_prefix = '';
private $_charset = '';
private $_cached = '';
private $_fc_path = '';
private $_mc_server = '';
// 运行时变量
private $_dbc_handle = null;
private $_query_handle = null;
public $sql = '';
private $_cache_key = '';
private $_result = array();
private $_need_cache = false;
// 数据库操作
private $_operate = '';
private $_column = '';
private $_where = array();
private $_order = array();
private $_limit = '';
private $_data = array();
private $_cache = '';
// 调试记录
private $_trace = array();
// 获取实例
public function getInstance()
{
return new self();
}
// 私有化构造函数，禁止外部实例化
private function __construct(){}
// 卸载实例时，自动释放资源，关闭连接
public function __destruct()
{
// 释放资源
$this->free();
// 关闭连接
$this->close();
}
// 载入配置
public function config($config)
{
$this->trace('public::config::load');
$this->_config = $config;
// 执行初始化
$this->init();
}
// 初始化
private function init()
{
$this->trace('private::config::init_default');
// 配置分析
foreach ($this->_config as $key => $val)
{
$mkey = '_'.$key;
$this->$mkey = isset($this->_config[$key]) ? $this->_config[$key] : $this->_config_default[$key];
}
// 清理非debug模式下，之前记录的调试信息
if (!$this->_debug) unset($this->_trace);
// 缓存配置
$this->trace('private::config::init_cache');
$cache_conf = explode('://', $this->_cached);
$this->_cached = $cache_conf[0];
if ($this->_cached == 'file')
{
$this->_fc_path = str_replace('{current}', dirname(__FILE__), str_replace('{root}', $_SERVER['DOCUMENT_ROOT'], $cache_conf[1]));
// 检测目录
if (!is_dir($this->_fc_path))
{
mkdir($this->_fc_path);
}
}
elseif ($this->_cached == 'memcache')
{
$this->_mc_server = $cache_conf[1];
}
unset($this->_config);
}
// 连接至数据库
private function connect()
{
$this->trace('public::server::connect');
// 连接服务器
$this->_dbc_handle = mysql_connect(
$this->_host,
$this->_username,
$this->_password,
true,
$this->CLIENT_MULTI_RESULTS
);
if (!$this->_dbc_handle)
{
$this->alert('Can\'t connect to Server [ '.$this->_username.'@'.$this->_host.' ]');
return false;
}
// 选择数据库
if (!mysql_select_db($this->_database, $this->_dbc_handle))
{
$this->alert('Can\'t select database ['.$this->_database.']');
return false;
}
$version = mysql_get_server_info($this->_dbc_handle);
// 设置数据库编码
if ($version >= '4.1')
{
//使用UTF8存取数据库 需要mysql 4.1.0以上支持
mysql_query('SET NAMES "'.$this->_charset.'"', $this->_dbc_handle);
}
//设置 sql_model
if($version > '5.0.1')
{
mysql_query('SET SQL_Mode=""', $this->_dbc_handle);
}
return true;
}
// 释放数据查询
private function free()
{
$this->trace('public::query::free');
if ($this->_query_handle && $this->_operate == 'SELECT')
{
mysql_free_result($this->_query_handle);
}
unset($this->_query_handle);
unset($this->_operate);
unset($this->_column);
unset($this->_where);
unset($this->_order);
unset($this->_limit);
unset($this->_data);
unset($this->_cache);
unset($this->_result);
return true;
}
// 关闭数据库连接
private function close()
{
if ($this->_dbc_handle)
{
$this->trace('public::server::close');
mysql_close($this->_dbc_handle);
unset($this->_dbc_handle);
}
}
// <![数据库操作][
// 增改删查
public function select($column)
{
$this->_operate = 'SELECT';
$this->_column = $column;
return $this;
}
public function update($column)
{
$this->_operate = 'UPDATE';
$this->_column = $column;
return $this;
}
public function insert($column)
{
$this->_operate = 'INSERT';
$this->_column = $column;
return $this;
}
public function delete($column)
{
$this->_operate = 'DELETE';
$this->_column = $column;
return $this;
}
// 条件
public function where($where)
{
$this->_where[] = $where;
return $this;
}
// 排序
public function order($order)
{
$this->_order[] = $order;
return $this;
}
// 限制返回结果数
public function limit($limit)
{
$this->_limit = $limit;
return $this;
}
// 数据存储
public function data($data)
{
$this->_data[] = $data;
return $this;
}
// 缓存设置
public function cache($cache)
{
$this->_cache = $cache;
return $this;
}
// 开始执行操作
public function done()
{
$this->trace('public::query::init');
// 数据表
$column = $this->_prefix.$this->_column;
// 组合SQL
switch ($this->_operate)
{
case 'SELECT':
$sql = 'SELECT * FROM `'.$column.'`'.$this->pack_where().$this->pack_order().$this->pack_limit();
break;
case 'UPDATE':
$sql = 'UPDATE `'.$column.'`'.$this->pack_data().$this->pack_where();
break;
case 'INSERT':
$sql = 'INSERT INTO `'.$column.'`'.$this->pack_data();
break;
case 'DELETE':
$sql = 'DELETE FROM `'.$column.'`'.$this->pack_where();
break;
default: break;
}
$this->sql = $sql;
// 缓存判断 [暂时只支持缓存查询]
if ($this->_operate == 'SELECT' && $this->cache_check())
{
$return = $this->_result;
// 清理变量池并返回
if ($this->free()) return $return;
}
// 连接判断
if (!$this->_dbc_handle) $this->connect();
// 开始执行SQL
$this->trace('public::query::begin['.$this->_operate.']');
$this->_query_handle = mysql_query($sql, $this->_dbc_handle);
if (!$this->_query_handle)
{
$this->alert('SQL run error.');
}
$this->trace('public::query::finish['.$this->_operate.']');
if ($this->_operate == 'SELECT')
{
if (mysql_num_rows($this->_query_handle) > 0)
{
while ($one_row = mysql_fetch_assoc($this->_query_handle))
{
$this->_result[] = $one_row;
}
mysql_data_seek($this->_query_handle, 0);
}
else
{
$this->_result = null;
}
// 写缓存
if ($this->_need_cache) $this->cache_write();
$return = $this->_result;
// 清理变量池并返回
if ($this->free()) return $return;
}
else
{
$return = mysql_affected_rows($this->_dbc_handle);
// 清理变量池并返回
if ($this->free()) return $return;
}
}
// 返回结果限制
private function pack_limit()
{
if ($this->_limit == '') return '';
if (is_numeric($this->_limit))
{
return ' LIMIT 0,'.$this->_limit;
}
elseif (is_string($this->_limit))
{
return ' LIMIT '.$this->_limit;
}
}
// 条件整合
private function pack_where()
{
if (!$this->_where) return '';
$sql_where = ' WHERE ';
foreach ($this->_where as $where)
{
if (is_array($where))
{
foreach ($where as $key => $val)
{
if (is_numeric($val))
{
$sql_where .= $key.'='.$val;
}
elseif (is_string($val))
{
$sql_where .= $key.'="'.$val.'"';
}
$sql_where .= ' and ';
}
}
elseif (is_string($where))
{
$conds = explode(',', $where);
foreach ($conds as $one_cond)
{
$sql_where .= $one_cond.' and ';
}
}
}
return substr($sql_where, 0, -5);
}
// 排序整合
private function pack_order()
{
if (!$this->_order) return '';
$sql_order = ' ORDER BY ';
foreach ($this->_order as $order)
{
if (is_array($order))
{
foreach ($order as $key => $type)
{
$sql_order .= $key.' '.$type.', ';
}
}
elseif (is_string($order))
{
$ords = explode(',', $order);
foreach ($ords as $one_ord)
{
$sql_order .= str_replace('.', ' ', $one_ord).', ';
}
}
}
return substr($sql_order, 0, -2);
}
// 数据整合
private function pack_data()
{
if (!$this->_data) return '';
$sql_data = ' SET ';
foreach ($this->_data as $data)
{
if (is_array($data))
{
foreach ($data as $key => $val)
{
if (is_numeric($val))
{
$sql_data .= $key.'='.$val;
}
elseif (is_string($val))
{
$sql_data .= $key.'="'.$val.'"';
}
$sql_data .= ', ';
}
}
elseif (is_string($data))
{
$datas = explode(',', $data);
foreach ($datas as $one_data)
{
$sql_data .= $one_data.', ';
}
}
}
return substr($sql_data, 0, -2);
}
// ]>
// 缓存检测
private function cache_check()
{
$this->trace('private::cache::check');
if ($this->_cache == '') return false;
$this->_cache_key = md5($this->sql.'@'.$this->CACHE_HASH_SALT);
$time_calc = array
(
's' => 1,
'm' => 60,
'h' => 3600,
'd' => 86400
);
$c_rule = explode(':', $this->_cache);
$c_time = $c_rule[0];
$c_long = (int)$c_rule[1];
if(time() - $this->cache_time() > $time_calc[$c_time]*$c_long)
{
$this->_need_cache = true;
return false;
}
$this->_result = $this->cache_read();
return true;
}
// 获取时间
private function cache_time()
{
$handle = 'cache_handle_'.$this->_cached.'_time';
return $this->$handle($this->_cache_key);
}
// 读缓存
private function cache_read()
{
$this->trace('private::cache::read');
$handle = 'cache_handle_'.$this->_cached.'_value';
return $this->$handle($this->_cache_key);
}
// 写缓存
private function cache_write()
{
$this->trace('private::cache::write');
$handle = 'cache_handle_'.$this->_cached.'_write';
$this->$handle($this->_cache_key, $this->_result);
$this->_need_cache = false;
}
// <![缓存方式][
// 文件缓存
private function cache_handle_file_time($key)
{
if (is_file($this->_fc_path.$key.'.sql'))
{
return filemtime($this->_fc_path.$key.'.sql');
}
else
{
return 0;
}
}
private function cache_handle_file_value($key)
{
if (is_file($this->_fc_path.$key.'.sql'))
{
return unserialize(file_get_contents($this->_fc_path.$key.'.sql'));
}
else
{
return false;
}
}
private function cache_handle_file_write($key, $val)
{
file_put_contents($this->_fc_path.$key.'.sql', serialize($val));
return true;
}
// memcache 缓存 [这里做的不怎么好，不过平常也用不到memcache的 ^_^]
private function cache_handle_memcache_time($key)
{
$mec = new Memcache();
$mec->connect($this->_mc_server);
$val = $mec->get($this->_cache_key.'_time');
$mec->close();
if ($val == '')
{
return 0;
}
else
{
return $val;
}
}
private function cache_handle_memcache_value($key)
{
$mec = new Memcache();
$mec->connect($this->_mc_server);
$val = $mec->get($this->_cache_key.'_value');
$mec->close();
if ($val == '')
{
return false;
}
else
{
return $val['value'];
}
}
private function cache_handle_memcache_write($key, $val)
{
$mec = new Memcache();
$mec->connect($this->_mc_server);
$mec->set($this->_cache_key.'_time', time());
$mec->set($this->_cache_key.'_value', array('cached'=>true,'value'=>$val));
$mec->close();
return true;
}
// ]>
// <![一些数据库维护，例如：清空删除数据库、表，数据表优化等][
// 功能后续添加
// ]>
// 调试信息
private function alert($message)
{
if (!$this->_debug) return;
echo '<div style="border:2px solid #000;margin:10px;padding:10px;">';
echo $message;
echo '<hr/>';
echo mysql_error();
echo '</div>';
exit;
}
// 记录调试
private function trace($message)
{
if (!$this->_debug) return;
$this->_trace[] = array('timer'=>microtime(), 'mmusage'=>memory_get_usage(), 'message'=>$message);
}
// 输出调试
public function trace_output()
{
if (!$this->_debug) return;
echo '<div style="border:2px solid #000;margin:10px;padding:10px;">';
echo '<ul>';
foreach ($this->_trace as $i => $trace)
{
$timer_e = explode(' ', $trace['timer']);
$timer = (float)$timer_e[0];
$mmusage = $trace['mmusage'];
echo '<li>Time: '.$timer.' <font color="#0FC69D">+'.($timer-$last_timer).'</font> Memory: '.$trace['mmusage'].' <font color="#E56298">+'.($mmusage-$last_mmusage).'</font> Call: '.$trace['message'].'</li>';
$last_timer = $timer;
$last_mmusage = $mmusage;
}
echo '</ul>';
echo '</div>';
}
}
?>
具体使用方法：
<?php
include 'class.mysql.php';
echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />';
$conf_info = array
(
// 开启调试
'debug'=>true,
// MySQL主机
'host'=>'localhost:3306',
// 用户
'username'=>'run',
// 密码
'password'=>'moyo',
// 数据库
'database'=>'test',
// 数据表前缀
'prefix' => '',
// 数据库编码
'charset'=>'utf8',
// 缓存方式
// memcache缓存协议，“://” 后面的是服务器地址
//'cached'=>'memcache://127.0.0.1:11211'
// 文本缓存协议，“://”后面的是缓存地址。可用的标记：{root} 站点根目录， {current} 当前脚本目录
'cached'=>'file://{current}/query_cache/'
);
// 获取实例
$dbc = db_mysql::getInstance();
// 载入配置 [只支持数组方式]
$dbc->config($conf_info);
$start = explode(' ', microtime());
$memory_start = memory_get_usage();
// 插入数据
$affect = $dbc
// 操作表：user
->insert('user')
// 支持数组方式
->data(array('name'=>"Moyo"))
// 支持字符方式
->data('mail="moyo@mail"')
->done();
echo $dbc->sql;
echo '<p></p>';
echo 'INSERT 操作完成，影响行数：'.$affect;
echo '<hr/>';
// 修改数据
$affect = $dbc
->update('user')
// 支持数组方式
->where(array('name'=>'Moyo'))
// 支持字符方式
->where('mail="moyo@mail"')
->data(array('name'=>"Moyo.live", 'mail'=>'moyo@uuland'))
->done();
echo $dbc->sql;
echo '<p></p>';
echo 'UPDATE 操作完成，影响行数：'.$affect;
echo '<hr/>';
// 获取数据
$result = $dbc
->select('user')
->where('name like "%Moyo%"')
->order('id.desc')
->limit(3)
// 使用缓存，有效时间：10秒 [单位支持：d 天，h 时， m 分， s 秒]
->cache('s:10')
->done();
echo $dbc->sql;
echo '<p></p>';
echo 'SELECT 完成，记录数：'.count($result);
echo '<pre>';
print_r($result);
echo '</pre>';
echo '<hr/>';
// 删除数据
$affect = $dbc
->delete('user')
->where('name="Moyo.live"')
->done();
echo $dbc->sql;
echo '<p></p>';
echo 'DELETE 操作完成，影响行数：'.$affect;
echo '<hr/>';
$finish = explode(' ', microtime());
$memory_finish = memory_get_usage();
$start = $start[1]+$start[0];
$finish = $finish[1]+$finish[0];
$time = $finish-$start;
$memory = $memory_finish-$memory_start;
echo 'Trace (Time use '.$time.' Sec , Memory use '.$memory_finish.' Bytes , Incress '.$memory.' Bytes)';
echo '<hr width="30%" align="left" />';
$dbc->trace_output();
?>
