php实现带读写分离功能的MySQL类完整实例

本文实例讲述了php实现带读写分离功能的mysql类。分享给大家供大家参考，具体如下：

概述:

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);
  }
}

更多关于php相关内容感兴趣的读者可查看本站专题：《php+mysqli数据库程序设计技巧总结》、《php基于pdo操作数据库技巧总结》、《php运算与运算符用法总结》、《php网络编程技巧总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

希望本文所述对大家php程序设计有所帮助。