本文實例講述了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程序設計有所幫助。
