|
|
用于CI的，支持读写分离的数据库架构，仅用于参考。
PHP复制代码
<?php
//数据库配置
define( 'DB_MASTER', 1 ); //写库
define( 'DB_SLAVE', 2 ); //读库
//数据库配置信息
$config['db'][DB_MASTER ]['host'] = 'localhost';
$config['db'][DB_MASTER ]['user'] = 'root';
$config['db'][DB_MASTER ]['pass'] = '';
$config['db'][DB_MASTER ]['name'] = '';
$config['db'][DB_MASTER ]['charset'] = 'utf8';
//注释下面则不启用读写分离，可稍作修改支持更多slave
$config['db'][DB_SLAVE ]['host'] = 'localhost';
$config['db'][DB_SLAVE ]['user'] = 'root';
$config['db'][DB_SLAVE ]['pass'] = '';
$config['db'][DB_SLAVE ]['name'] = '';
$config['db'][DB_SLAVE ]['charset'] = 'utf8';
/**
* mysql数据库管理类，支持读写分离
* 该类没有对SQL语句进行高度封装，但可以通过继承这个类来扩展更多功能
* @author wll 20111101
* @version 1.0
* examples:
* $db = new DB( $config, $true )
* $newid = $db->runsql(insert...)
* $db->getData(select * ...);
* $db->getLine(select * ...)
*
* 20121108
* 该类在CI中已经自动初始化，直接：$this->db->方法() 即可
* 为了兼顾CI的规则，应该尽量在models中调用，而不是到处乱用
* 写库：
* $this->runSql($sql) 该方法会 RETURN 一个新插入的自增ID
* 读库：
* $this->getData($sql)返回结果集
* $this->getLine($sql) 返回结果集第一条记录
* $this->getField($sql) 返回结果集第一条记录的第一个字段
*/
class DB {
protected $config; //数据库配置数组
protected $conn_id; //当前连接ID
protected $db_read;
protected $db_write;
protected $do_replication;
public $debug = true; //是否打开调试（会记录所有SQL语句以及语句执行时间）
protected $sqlnum = 0;
protected $sql_debug = array();
/**
* 构造函数
* @param unknown_type $db_config 数据库配置
* @param unknown_type $do_replication 是否开启读写分离
* @author wll 20111102
*/
function __construct (){
$this->config = get_instance ()->config->item('db');
$this->do_replication = true;
}
/**
* 获取一个需要的数据库配置
* @param unknown_type $is_master 默认取写库，可选从库
* @author wll 20111102
* @return Array()
*/
protected function get_config ($is_master){
if( $is_master !== true && isset($this->config[DB_SLAVE ]) ){
return $this->config[DB_SLAVE ];
}
return $this->config[DB_MASTER ];
}
/**
* 创建一个读连接，如果没有开启读写分离，则获取写连接
* @return unknown
* @return link resource
*/
final protected function read (){
if( isset($this->db_read) && mysql_ping( $this->db_read ) ){
return $this->db_read;
}else{
if( !$this->do_replication ){
return $this->write();
}else{
return $this->db_read = $this->connect(false);
}
}
}
/**
* 获取一个连接符，尽量优先读连接
* @return resouce
*/
function getConn (){
if( is_resource($this->db_read) ){
return $this->db_read;
}elseif(is_resource($this->db_write)){
return $this->db_write;
}
return $this->connect();
}
/**
* 创建一个写连接
* @author wll 20111102
* @return resouce
*/
final protected function write (){
if( isset($this->db_write) && mysql_ping( $this->db_write ) ){
return $this->db_write;
}else{
return $this->db_write = $this->connect(true);
}
}
/**
* 连接数据库，支持主从分离
* @param unknown_type $do_replication 默认为FALSE不支持主从
* @return resouce
*/
final protected function connect ( $is_master = true ){
$db = $this->get_config($is_master);
$link = @mysql_connect($db['host'], $db['user'], $db['pass']);
if ($link===false) {
return $this->save_err($link);
}
mysql_select_db($db['name'], $link) or $this->save_err($link);
@mysql_query( "SET NAMES {$db['charset']}", $link );
return $link;
}
/**
* 如操作中发现错误，及时抛出（可扩展这个方法自定义抛出的错误格式）
* @author wll 20111102
*/
protected function save_err ( $link ){
if( $link ){
$this->error = mysql_error( $link );
$this->errno = mysql_errno( $link );
}else{
$this->error = mysql_error();
$this->errno = mysql_errno();
}
if( $this->error ){
die( $this->error ."<br>". $this->errno );
}
}
/**
* 执行SQL查询
* 所有SQL查询都经由这里，可以
* @param unknown_type $sql
* @param unknown_type $link
* @return resouce
* @author wll 20111102
*/
protected function query ( $sql, $link ){
if( $this->debug ){
$st = $this->debug_time();
$ret = mysql_query( $sql, $link );
$et = $this->debug_time();
$this->sqlnum++;
$this->sql_debug[$this->sqlnum] = array( 'sql'=>$sql, 'time'=>$et-$st );
}else{
$ret = mysql_query( $sql, $link );
}
return $ret;
}
/**
* 返回多维数组的数据结果集
* @param $sql string
* @return Array or Null
*/
final public function getData ( $sql ){
$this->sql = $sql;
$data = array();
$i = 0;
$link = $this->read();
$ret = $this->query( $this->sql, $link );
$this->save_err($link);
while( $Array = mysql_fetch_array( $ret, MYSQL_ASSOC ) )
{
$data[$i++] = $Array;
}
mysql_free_result($ret);
if( count($data)>0 ){
return $data;
}else{
return null;
}
}
/**
* 返回单行结果集
* @param $sql string
* @return Array or Null
*/
final public function getLine ( $sql ){
$data = $this->getData($sql);
if( $data ){
return reset( $data );
}else{
return null;
}
}
/**
* 返回第一行的第1个字段
* @param unknown_type $sql
* @param unknown_type $pos
*/
public function getField ( $sql ){
$data = $this->getLine( $sql );
if( $data ){
return reset($data);
}
return null;
}
/**
* [写库]运行SQL语句，不返回结果集，用于UPDATE INSERT DELETE均可
* @param $sql string
* @return insert_id()
*/
final public function runSql ( $sql ){
$this->sql = $sql;
$link = $this->write();
$ret = $this->query( $this->sql, $link );
$this->save_err($link);
//如果有插入新ID，则返回ID值，否则返回QUERY的执行结果
$insert_id = mysql_insert_id($link);
return $insert_id > 0 ? $insert_id : $ret;
}
/**
* 关闭数据库连接
*/
public function close (){
if( isset($this->db_read) ){
mysql_close( $this->db_read );
}
if( isset($this->db_write) ){
mysql_close( $this->db_write );
}
}
/**
* 传入条件数组，拼接SQL语句
* $this->st( $whereArray, 'where_or' ); //以or为条件拼接SQL语句的片段
* 完整用法：select * from tables where $this->st( $whereArray, 'where_or' );
* 支持 insert/update/replace/where/whereor/in
* @param $data
* @param $type
*/
function st ( $data = array(), $type = 'insert', $escape = true ){
//如果传入的不是数组，则认为是SQL片段，原样返回
if( !is_array($data) ){
return $data;
}
$sqlText = '';
switch( $type ){
//新增语句
case "insert":
$key = '(';
$val = '(';
foreach( $data as $v=>$k ){
$k = $escape === true ? $this->escape_str( $k ) : $k;
$key .= "{$v},";
$val .= "'{$k}',";
}
$key = substr($key,0,-1).')';
$val = substr($val,0,-1).')';
$sqlText = " {$key} values {$val} ";
break;
//更新语句
case "update":
foreach( $data as $v=>$k ){
$k = $escape === true ? $this->escape_str( $k ) : $k;
$sqlText .= " {$v}='{$k}',";
}
$sqlText = substr( $sqlText, 0, -1 );
break;
//批量更新 replace into table (id,name) values ('id','name'),('id','name')
case "replace":
foreach( $data as $k ){
$k = $escape === true ? $this->escape_str( $k ) : $k;
$sqlText .= "('".implode( "','", $k )."'),";
}
$sqlText = substr( $sqlText, 0, -1 );
break;
//and 查询
case "where":
foreach( $data as $v=>$k ){
$k = $escape === true ? $this->escape_str( $k ) : $k;
//使where时支持 < > 等参数，如果key有空格则认为后面是运算条件
//or的条件放在条件数组的结尾
$tmpV = explode(' ',$v);
if( count($tmpV) == 2 ){
if( $tmpV[1] == 'in' ){
$sqlText .= " {$tmpV[0]} {$tmpV[1]} ({$k}) and";
}
else{
$sqlText .= " {$tmpV[0]} {$tmpV[1]} '{$k}' and";
}
}else{
$sqlText .= " {$v}='{$k}' and";
}
}
$sqlText = substr( $sqlText, 0, -3 );
break;
//or 查询
case "where_or":
foreach( $data as $v=>$k ){
$k = $escape === true ? $this->escape_str( $k ) : $k;
//使where时支持 < > 等参数，如果key有空格则认为后面是运算条件
$tmpV = explode(' ',$v);
if( count($tmpV) == 2 ){
$sqlText .= " {$tmpV[0]} {$tmpV[1]} '{$k}' or";
}else{
$sqlText .= " {$v}='{$k}' or";
}
}
$sqlText = substr( $sqlText, 0, -2 );
break;
//in 查询
case "in":
$sqlText = '(' . implode( ',', $data ). ')';
break;
}
return $sqlText;
}
/**
* Escape String
* 从CI框架剥出来的字符串转义..
* 这函数不适合对整个SQL做转义，只适合 key=escape_str(value)
* @access public
* @param string
* @param bool whether or not the string will be used in a LIKE condition
* @return string
*/
function escape_str ($str, $like = FALSE)
{
if (is_array($str))
{
foreach ($str as $key => $val)
{
$str[$key] = $this->escape_str($val, $like);
}
return $str;
}
$conn = $this->getConn();
if (function_exists('mysql_real_escape_string') AND is_resource($conn))
{
$str = mysql_real_escape_string($str, $conn);
}
else
{
$str = mysql_escape_string($str);
}
// escape LIKE condition wildcards
if ($like === TRUE)
{
$str = str_replace(array('%', '_'), array('\\%', '\\_'), $str);
}
return $str;
}
/**
* 调试选项，记录SQL执行效率
* @return unknown
*/
function debug_time (){
list($usec, $sec) = explode(" ", microtime());
return ((float )$usec + (float )$sec);
}
#获取调试信息
function get_debug (){
return $this->sql_debug;
}
}
|
|
IP卡
狗仔卡
发表于 2013-5-21 11:01:52
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡
楼主