class mysql_[转]Php MySql Class

最新推荐文章于 2021-02-08 22:08:43 发布

敬普

最新推荐文章于 2021-02-08 22:08:43 发布

阅读量105

点赞数

文章标签： class mysql

本文链接：https://blog.csdn.net/weixin_31328555/article/details/113143122

版权

/**

* 数据库操作类

* @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 '

echo $message;

echo '

echo mysql_error();

echo '

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 '

foreach ($this->_trace as $i => $trace)

{

$timer_e = explode(' ', $trace['timer']);

$timer = (float)$timer_e[0];

$mmusage = $trace['mmusage'];

echo '

Time: '.$timer.' +'.($timer-$last_timer).' Memory: '.$trace['mmusage'].' +'.($mmusage-$last_mmusage).' Call: '.$trace['message'].'';

$last_timer = $timer;

$last_mmusage = $mmusage;

}

echo '

}

具体使用方法：

include 'class.mysql.php';

echo '';

$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 '

echo 'INSERT 操作完成，影响行数：'.$affect;

echo '

//修改数据$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 '

echo 'UPDATE 操作完成，影响行数：'.$affect;

echo '

//获取数据$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 '

echo 'SELECT 完成，记录数：'.count($result);

echo '

';

print_r($result);

echo '

//删除数据$affect = $dbc

->delete('user')

->where('name="Moyo.live"')

->done();

echo $dbc->sql;

echo '

echo 'DELETE 操作完成，影响行数：'.$affect;

echo '

$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 '

$dbc->trace_output();

敬普

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
class mysql_[转]Php MySql Class

/***数据库操作类**@authorMoyo*@packagedefaultPackage*/classMysql{//默认配置private$_config_default=array('debug'=>false,'host'=>'localhost:3306','username'=>'root','password'=&g...
复制链接

扫一扫