基於php實現帶讀寫分離功能的MySQL類

這篇文章主要介紹了php實現帶讀寫分離功能的MySQL類,具有針對mysql資料庫的基本增刪改查及讀寫分離操作的功能,涉及針對讀庫與寫庫的判斷及操作技巧,需要的朋友可以參考下

具體如下：

概述:

1. 根據sql語句判斷是串連讀庫還是寫庫
2. 鏈式調用$this->where()->get()
3. 不同的主機對應不同的執行個體, 不再多次new

具體代碼如下：

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

總結：以上就是本篇文的全部內容，希望能對大家的學習有所協助。