读写分离MYSQL类

2014年4月27日 12:34:08

概述:

1. 根据sql语句判断是连接读库还是写库

2. 链式调用$this->where()->get()

3. 不同的主机对应不同的实例, 不再多次new

<?php
class DBRWmysql
{
    private static $Instance = null;

    private $links = array();//链接数组
    private $link = null; //当前连接
    public $dbType = 'read';

    public $_host=''; //数据库所在主机名
    public $_database = '';//当前数据库名
    public $_tablename = '';//当前表的表名
    public $_dt ='';//database.tablename
    public $isRelease = 0; //查询完成后是否释放

    public $fields = '*';
    public $arrWhere = [];
    public $order = '';
    public $arrOrder = [];
    public $limit = '';
    public $sql = '';

    public $rs;//结果集

    private function __construct($database='', $tablename='', $isRelease=0)
    {
        $this->_database = $database;//database name
        $this->_tablename = $tablename;//table name
        $this->_dt = "`{$this->_database}`.`{$this->_tablename}`";
        $this->isRelease = $isRelease;
    }

    public static function getInstance($database='', $tablename='', $isRelease=0)
    {
        if (self::$Instance == null) {
            self::$Instance = new DBRWmysql($database, $tablename, $isRelease);
        }

        self::$Instance->_database  = $database;
        self::$Instance->_tablename = $tablename;
        self::$Instance->_dt        = "`{$database}`.`{$tablename}`";
        self::$Instance->isRelease  = $isRelease;

        return self::$Instance;
    }

    //如果主机没变,并且已经存在MYSQL连接,就不再创建新的连接
    //如果主机改变,就再生成一个实例创建一个连接
    //type == 'write'或'read'
    public function getLink($type)
    {
        $this->dbType = $$type;

        //随机选取一个数据库连接(区分读写)
        $dbConfig = DBConfig::$$type;
        $randKey = array_rand($dbConfig);
        $config = $dbConfig[$randKey];

        //链接数据库
        $host = $config['host'];
        $username = $config['username'];
        $password = $config['password'];

        if (empty($this->links[$host])) {
            $this->_host = $host;
            $this->links[$host] = new mysqli($host, $username, $password);
            if($this->links[$host]->connect_error) {
                $this->error($this->links[$host]->connect_error);
            }
        }

        //初始化链接
        $this->link = $this->links[$host];
        $this->link->query("set names utf8mb4;"); //支持emoji表情
        $this->link->query("use {$this->_database};");
    }
    
    public function getCurrentLinks()
    {
        return $this->links;
    }
    
    //析构函数
    public function __destruct()
    {
        foreach ($this->links as $v) {
            $v->close();
        }
    }
    
    //查询封装
    public function query($sql)
    {
        $this->sql = $sql;
        if (strpos($sql, 'select') !== false) {
            $this->getLink('read');//读库
        } else {
            $this->getLink('write');//写库
        }

        $this->rs = $this->link->query($sql);
        ($this->rs === false) && $this->error('sql error: '.$sql.PHP_EOL.$this->link->error);

        //查询完成后释放链接, 并删除链接对象
        if ($this->isRelease) {
            $this->link->close();
            unset($this->links[$this->_host]);
        }
        return $this->rs;
    }
    

    //增
    public function insert($arrData)
    {
        foreach ($arrData as $key=>$value) {
            $fields[] = $key;
            $values[] = "'".$value."'";
            // $fields[] = '`'.$key.'`';
            // $values[] = "'".$value."'";
        }
        $strFields = implode(',', $fields);
        $strValues = implode(',', $values);
        $sql = "insert into {$this->_dt} ($strFields) values ($strValues)";
        $this->query($sql);
        $insert_id = $this->link->insert_id;
        
        return $insert_id;
    }

    //增
    public function replace($arrData)
    {
        foreach ($arrData as $key=>$value) {
            $fields[] = $key;
            $values[] = "'{$value}'";
        }
        $strFields = implode(',', $fields);
        $strValues = implode(',', $values);
        $sql = "replace into {$this->_dt} ($strFields) values ($strValues)";
        
        $this->query($sql);
        
        return $this->link->insert_id;
    }
    
    //增
    //每次插入多条记录
    //每条记录的字段相同,但是值不一样
    public function insertm($arrFields, $arrData)
    {
        foreach ($arrFields as $v) {
            // $fields[] = "`{$v}`";
            $fields[] = $v;
        }
        
        foreach ($arrData as $v) {
            $data[] = '('.implode(',', $v).')';
        }
        
        $strFields = implode(',', $fields);
        $strData = implode(',', $data);
        
        $sql = "insert into {$this->_dt} ($strFields) values {$strData}";
        
        $this->query($sql);
        
        return $this->link->insert_id;
    }
    
    //删
    public function delete()
    {
        $where = $this->getWhere();
        $limit = $this->getLimit();

        $sql = " delete from {$this->_dt} {$where} {$limit}";
        $this->query($sql);
        return $this->link->affected_rows;
    }
    
    //改
    public function update($data)
    {
        $where = $this->getWhere();

        $arrSql = array();
        foreach ($data as $key=>$value) {
            $arrSql[] = "{$key}='{$value}'";
        }
        $strSql = implode(',', $arrSql);
        
        $sql = "update {$this->_dt} set {$strSql} {$where} {$this->limit}";
        
        $this->query($sql);
        
        return $this->link->affected_rows;
    
    }

    //获取总数
    public function getCount()
    {
        $where = $this->getWhere();
        
        $sql = " select count(1) as n from {$this->_dt} {$where} ";
        $resault = $this->query($sql);
        
        ($resault===false) && $this->error('getCount error: '.$sql);
        
        $arrRs = $this->rsToArray($resault);
        
        $num = array_shift($arrRs);
        return $num['n'];
    }
    
    //将结果集转换成数组返回
    //如果field不为空，则返回的数组以$field为键重新索引
    public function rsToArray($field = '')
    {
        $arrRs = $this->rs->fetch_all(MYSQLI_ASSOC); //该函数只能用于php的mysqlnd驱动
        $this->rs->free();//释放结果集
        
        if ($field) {
            $arrResult = [];
            foreach ($arrRs as $v) {
                $arrResult[$v[$field]] = $v;
            }
            return $arrResult;
        }
        
        return $arrRs;
    }
    
    //给字段名加上反引号
    public function qw($strFields)
    {
        $strFields = preg_replace('#\s+#', ' ', $strFields);
        $arrNewFields = explode(' ', $strFields );
        $arrNewFields = array_filter($arrNewFields);
        foreach ($arrNewFields as $k => $v) {
            $arrNewFields[$k]= '`'.$v.'`';
        }
        return implode(',', $arrNewFields);
    }

    //处理入库数据,将字符串格式的数据转换为...格式(未实现)
    public function getInsertData($strData)
    {
        // $bmap = "jingdu,$jingdu weidu,$weidu content,$content";
    }

    //select in
    //arrData 整数数组，最好是整数
    public function select_in($key, $arrData, $fields='')
    {
        $fields = $fields ? $fields : '*';
        sort($arrData);
        $len = count($arrData);
        $cur = 0;
        $pre = $arrData[0];

        $new = array('0' => array($arrData[0]));
        for ($i = 1; $i < $len; $i++) {
            if (($arrData[$i] - $pre) == 1 ) {
                $new[$cur][] = $arrData[$i];
            } else {
                $cur = $i;
                $new[$cur][] = $arrData[$i];
            }
            $pre = $arrData[$i];
        }

        $arrSql = array();
        foreach ($new as $v) {
            $len = count($v) - 1;
            if ($len) {
                $s = $v[0];
                $e = end($v);
                $sql = "(select $fields from {$this->_dt} where $key between $s and $e)";
            } else {
                $s = $v[0];
                $sql = "(select $fields from {$this->_dt} where $key = $s)";
            }

            $arrSql[] = $sql;
        }
        
        $strUnion = implode(' UNION ALL ', $arrSql);
        $res = $this->query($strUnion);
        return $this->rstoarray($res);
    }
    
    //where in
    public function setWhereIn($key, $arrData)
    {
        if (empty($arrData)) {
            $str = "(`{$key}` in ('0'))";
            $this->addWhere($str);
            return $str;
        }
        
        foreach ($arrData as &$v) {
            $v = "'{$v}'";
        }
        $str = implode(',', $arrData);
        $str = "(`{$key}` in ( {$str} ))";
        
        $this->addWhere($str);
        
        return $this;
    }
    
    //where in
    public function setWhere($arrData)
    {
        if (empty($arrData)) {
            return '';
        }
        
        foreach ($arrData as $k => $v) {
            $str = "(`{$k}` = '{$v}')";
            $this->addWhere($str);
        }
        
        return $this;
    }
    
    //between and
    public function setWhereBetween($key, $min, $max)
    {
        $str = "(`{$key}` between '{$min}' and '{$max}')";
        $this->addWhere($str);
        return $this;
    }
    
    //where a>b
    public function setWhereBT($key, $value)
    {
        $str = "(`{$key}` > '{$value}')";
        $this->addWhere($str);
        return $this;
    }
    
    //where a<b
    public function setWhereLT($key, $value)
    {
        $str = "(`{$key}` < '{$value}')";
        $this->addWhere($str);
        return $this;
    }
    
    //组装where条件
    public function addWhere($where)
    {
        $this->arrWhere[] = $where;
    }
    
    //获取最终查询用的where条件
    public function getWhere()
    {
        if (empty($this->arrWhere)) {
            return 'where 1';
        } else {
            return 'where '.implode(' and ', $this->arrWhere);
        }
    }
    
    //以逗号隔开
    public function setFields($fields)
    {
        $this->fields = $fields;
        return $this;
    }
    
    // order by a desc
    public function setOrder($order)
    {
        $this->arrOrder[] = $order;
        return $this;
    }
    
    //获取order语句
    public function getOrder()
    {
        if (empty($this->arrOrder)) {
            return '';
        } else {
            $str = implode(',', $this->arrOrder);
            $this->order = "order by {$str}";
        }
        return $this->order;
    }
    
    //e.g. '0, 10'
    //用limit的时候可以加where条件优化：select ... where id > 1234 limit 0, 10 
    public function setLimit($limit)
    {
        $this->limit = 'limit '.$limit;
        return $this;
    }

    //直接查询sql语句, 返回数组格式
    public function arrQuery($sql, $field='')
    {
        $this->query($sql);
        $this->clearQuery();
        ($this->rs===false) && $this->error('select error: '.$sql);
        return $this->rsToArray($field);
    }
    
    //如果 $field 不为空, 则返回的结果以该字段的值为索引
    //暂不支持join
    public function get($field='')
    {
        $where = $this->getWhere();
        $order = $this->getOrder();
        
        $sql = " select {$this->fields} from {$this->_dt} {$where} {$order} {$this->limit} ";
        return $this->arrQuery($sql, $field);
    }
    
    //获取一条记录
    public function getOne()
    {
        $this->setLimit(1);
        $rs = $this->get();

        return !empty($rs) ? $rs[0] : [];
    }
    
    //获取一条记录的某一个字段的值
    public function getOneField($field)
    {
        $this->setFields($field);
        $rs = $this->getOne();

        return !empty($rs[$field]) ? $rs[$field] : '';
    }

    //获取数据集中所有某个字段的值
    public function getFields($field)
    {
        $this->setFields($field);
        $rs = $this->get();
        $result = [];
        foreach ($rs as $v) {
            $result[] = $v[$field];
        }
        unset($rs);

        return $result;
    }
    
    //清除查询条件
    //防止干扰下次查询
    public function clearQuery()
    {
        $this->fields = '*';
        $this->arrWhere = [];
        $this->order = '';
        $this->arrOrder = [];
        $this->limit = '';
    }

    //断开数据库连接
    public function close()
    {
        $this->link->close();
    }

    //事务
    //自动提交开关
    public function autocommit($bool)
    {
        $this->link->autocommit($bool);
    }

    //事务完成提交
    public function commit()
    {
        $this->link->commit();
    }

    //回滚
    public function rollback()
    {
        $this->link->rollback();
    }


    //输出错误sql语句
    public function error($sql)
    {
        //if (IS_TEST) {}
        exit($sql);
    }
}

最后编辑: 2016年4月16日 19:45:14 星期六