2015年3月31日 18:27:34
最后编辑: 2016年4月17日 00:22:00 星期日
读写分离: 根据最终的sql语句来判断是读还是写 (随后会考察并加上一致性哈希)
链式调用: 参看下边用法
如果在一次PHP生命周期内,链接了多台机器, 那么每台机器的连接都会保存下来, 并且各有一份
如果在高并发下需要连接完就放弃链接, 可以在连接的时候传入参数$isRelease=1 (下边第4行的第二个参数)
用法(代码在某一个model文件中):
1 //获取论坛一级帖子列表 2 public function getTiezi() 3 { 4 $r = $this->getlink('tiezi') 5 ->setWhere(['level' => 0]) 6 ->setFields('id, strtime, content, nickname') 7 ->setOrder('id desc') 8 ->get(); 9 return $r; 10 }
setWhere, setWhereIn, setWhereBetween, setWhereBT(大于), setWhereLT(小于)
setFields, setOrder, setLimit,
insert, insertm, replace, update,
getCount,
get: 获取多条记录, getOne:获取一条记录, getOneField:获取某一条记录的某一个字段的值, getFields:获取多条记录的某个字段的所有值
select_in: 通过事先排序,分组,union all, 来优化select in 语句中有大量数字的查询
还没有group by having, 子查询,连结查询的实现, 建议结合redis等nosql, 尽量避免链接查询和子查询
mysql类的完整代码:
1 class DBmysql 2 { 3 private static $Instance = null; 4 5 private $links = array();//链接数组 6 private $link = null; //当前连接 7 public $dbType = 'read'; 8 9 public $_host=''; //数据库所在主机名 10 public $_database = '';//当前数据库名 11 public $_tablename = '';//当前表的表名 12 public $_dt ='';//database.tablename 13 public $isRelease = 0; //查询完成后是否释放 14 15 public $fields = '*'; 16 public $arrWhere = []; 17 public $order = ''; 18 public $arrOrder = []; 19 public $limit = ''; 20 public $sql = ''; 21 22 public $rs;//结果集 23 24 private function __construct($database='', $tablename='', $isRelease=0) 25 { 26 $this->_database = $database;//database name 27 $this->_tablename = $tablename;//table name 28 $this->_dt = "`{$this->_database}`.`{$this->_tablename}`"; 29 $this->isRelease = $isRelease; 30 } 31 32 public static function getInstance($database='', $tablename='', $isRelease=0) 33 { 34 if (self::$Instance == null) { 35 self::$Instance = new DBmysql($database, $tablename, $isRelease); 36 } 37 38 self::$Instance->_database = $database; 39 self::$Instance->_tablename = $tablename; 40 self::$Instance->_dt = "`{$database}`.`{$tablename}`"; 41 self::$Instance->isRelease = $isRelease; 42 43 return self::$Instance; 44 } 45 46 //如果主机没变,并且已经存在MYSQL连接,就不再创建新的连接 47 //如果主机改变,就再生成一个实例创建一个连接 48 //type == 'write'或'read' 49 public function getLink($type) 50 { 51 $this->dbType = $$type; 52 53 //随机选取一个数据库连接(区分读写) 54 $dbConfig = DBConfig::$$type; 55 $randKey = array_rand($dbConfig); 56 $config = $dbConfig[$randKey]; 57 58 //链接数据库 59 $host = $config['host']; 60 $username = $config['username']; 61 $password = $config['password']; 62 63 if (empty($this->links[$host])) { 64 $this->_host = $host; 65 $this->links[$host] = new mysqli($host, $username, $password); 66 if($this->links[$host]->connect_error) { 67 $this->error($this->links[$host]->connect_error); 68 } 69 } 70 71 //初始化链接 72 $this->link = $this->links[$host]; 73 $this->link->query("set names utf8mb4;"); //支持emoji表情 74 $this->link->query("use {$this->_database};"); 75 } 76 77 public function getCurrentLinks() 78 { 79 return $this->links; 80 } 81 82 //析构函数 83 public function __destruct() 84 { 85 foreach ($this->links as $v) { 86 $v->close(); 87 } 88 } 89 90 //查询封装 91 public function query($sql) 92 { 93 $this->sql = $sql; 94 if (strpos($sql, 'select') !== false) { 95 $this->getLink('read');//读库 96 } else { 97 $this->getLink('write');//写库 98 } 99 100 $this->rs = $this->link->query($sql); 101 ($this->rs === false) && $this->error('sql error: '.$sql.PHP_EOL.$this->link->error); 102 103 //查询完成后释放链接, 并删除链接对象 104 if ($this->isRelease) { 105 $this->link->close(); 106 unset($this->links[$this->_host]); 107 } 108 return $this->rs; 109 } 110 111 112 //增 113 public function insert($arrData) 114 { 115 foreach ($arrData as $key=>$value) { 116 $fields[] = $key; 117 $values[] = "'".$value."'"; 118 // $fields[] = '`'.$key.'`'; 119 // $values[] = "'".$value."'"; 120 } 121 $strFields = implode(',', $fields); 122 $strValues = implode(',', $values); 123 $sql = "insert into {$this->_dt} ($strFields) values ($strValues)"; 124 $this->query($sql); 125 $insert_id = $this->link->insert_id; 126 127 return $insert_id; 128 } 129 130 //增 131 public function replace($arrData) 132 { 133 foreach ($arrData as $key=>$value) { 134 $fields[] = $key; 135 $values[] = "'{$value}'"; 136 } 137 $strFields = implode(',', $fields); 138 $strValues = implode(',', $values); 139 $sql = "replace into {$this->_dt} ($strFields) values ($strValues)"; 140 141 $this->query($sql); 142 143 return $this->link->insert_id; 144 } 145 146 //增 147 //每次插入多条记录 148 //每条记录的字段相同,但是值不一样 149 public function insertm($arrFields, $arrData) 150 { 151 foreach ($arrFields as $v) { 152 // $fields[] = "`{$v}`"; 153 $fields[] = $v; 154 } 155 156 foreach ($arrData as $v) { 157 $data[] = '('.implode(',', $v).')'; 158 } 159 160 $strFields = implode(',', $fields); 161 $strData = implode(',', $data); 162 163 $sql = "insert into {$this->_dt} ($strFields) values {$strData}"; 164 165 $this->query($sql); 166 167 return $this->link->insert_id; 168 } 169 170 //删 171 public function delete() 172 { 173 $where = $this->getWhere(); 174 $limit = $this->getLimit(); 175 176 $sql = " delete from {$this->_dt} {$where} {$limit}"; 177 $this->query($sql); 178 return $this->link->affected_rows; 179 } 180 181 //改 182 public function update($data) 183 { 184 $where = $this->getWhere(); 185 186 $arrSql = array(); 187 foreach ($data as $key=>$value) { 188 $arrSql[] = "{$key}='{$value}'"; 189 } 190 $strSql = implode(',', $arrSql); 191 192 $sql = "update {$this->_dt} set {$strSql} {$where} {$this->limit}"; 193 194 $this->query($sql); 195 196 return $this->link->affected_rows; 197 198 } 199 200 //获取总数 201 public function getCount() 202 { 203 $where = $this->getWhere(); 204 205 $sql = " select count(1) as n from {$this->_dt} {$where} "; 206 $resault = $this->query($sql); 207 208 ($resault===false) && $this->error('getCount error: '.$sql); 209 210 $arrRs = $this->rsToArray($resault); 211 212 $num = array_shift($arrRs); 213 return $num['n']; 214 } 215 216 //将结果集转换成数组返回 217 //如果field不为空，则返回的数组以$field为键重新索引 218 public function rsToArray($field = '') 219 { 220 $arrRs = $this->rs->fetch_all(MYSQLI_ASSOC); //该函数只能用于php的mysqlnd驱动 221 $this->rs->free();//释放结果集 222 223 if ($field) { 224 $arrResult = []; 225 foreach ($arrRs as $v) { 226 $arrResult[$v[$field]] = $v; 227 } 228 return $arrResult; 229 } 230 231 return $arrRs; 232 } 233 234 //处理入库数据,将字符串格式的数据转换为...格式(未实现) 235 public function getInsertData($strData) 236 { 237 // $bmap = "jingdu,$jingdu;weidu,$weidu;content,$content"; 238 } 239 240 //select in 241 //arrData 整数数组，最好是整数 242 public function select_in($key, $arrData, $fields='') 243 { 244 $fields = $fields ? $fields : '*'; 245 sort($arrData); 246 $len = count($arrData); 247 $cur = 0; 248 $pre = $arrData[0]; 249 250 $new = array('0' => array($arrData[0])); 251 for ($i = 1; $i < $len; $i++) { 252 if (($arrData[$i] - $pre) == 1 ) { 253 $new[$cur][] = $arrData[$i]; 254 } else { 255 $cur = $i; 256 $new[$cur][] = $arrData[$i]; 257 } 258 $pre = $arrData[$i]; 259 } 260 261 $arrSql = array(); 262 foreach ($new as $v) { 263 $len = count($v) - 1; 264 if ($len) { 265 $s = $v[0]; 266 $e = end($v); 267 $sql = "(select $fields from {$this->_dt} where $key between $s and $e)"; 268 } else { 269 $s = $v[0]; 270 $sql = "(select $fields from {$this->_dt} where $key = $s)"; 271 } 272 273 $arrSql[] = $sql; 274 } 275 276 $strUnion = implode(' UNION ALL ', $arrSql); 277 $res = $this->query($strUnion); 278 return $this->rstoarray($res); 279 } 280 281 //where in 282 public function setWhereIn($key, $arrData) 283 { 284 if (empty($arrData)) { 285 $str = "(`{$key}` in ('0'))"; 286 $this->addWhere($str); 287 return $str; 288 } 289 290 foreach ($arrData as &$v) { 291 $v = "'{$v}'"; 292 } 293 $str = implode(',', $arrData); 294 $str = "(`{$key}` in ( {$str} ))"; 295 296 $this->addWhere($str); 297 298 return $this; 299 } 300 301 //where in 302 public function setWhere($arrData) 303 { 304 if (empty($arrData)) { 305 return ''; 306 } 307 308 foreach ($arrData as $k => $v) { 309 $str = "(`{$k}` = '{$v}')"; 310 $this->addWhere($str); 311 } 312 313 return $this; 314 } 315 316 //between and 317 public function setWhereBetween($key, $min, $max) 318 { 319 $str = "(`{$key}` between '{$min}' and '{$max}')"; 320 $this->addWhere($str); 321 return $this; 322 } 323 324 //where a>b 325 public function setWhereBT($key, $value) 326 { 327 $str = "(`{$key}` > '{$value}')"; 328 $this->addWhere($str); 329 return $this; 330 } 331 332 //where a<b 333 public function setWhereLT($key, $value) 334 { 335 $str = "(`{$key}` < '{$value}')"; 336 $this->addWhere($str); 337 return $this; 338 } 339 340 //组装where条件 341 public function addWhere($where) 342 { 343 $this->arrWhere[] = $where; 344 } 345 346 //获取最终查询用的where条件 347 public function getWhere() 348 { 349 if (empty($this->arrWhere)) { 350 return 'where 1'; 351 } else { 352 return 'where '.implode(' and ', $this->arrWhere); 353 } 354 } 355 356 //以逗号隔开 357 public function setFields($fields) 358 { 359 $this->fields = $fields; 360 return $this; 361 } 362 363 // order by a desc 364 public function setOrder($order) 365 { 366 $this->arrOrder[] = $order; 367 return $this; 368 } 369 370 //获取order语句 371 public function getOrder() 372 { 373 if (empty($this->arrOrder)) { 374 return ''; 375 } else { 376 $str = implode(',', $this->arrOrder); 377 $this->order = "order by {$str}"; 378 } 379 return $this->order; 380 } 381 382 //e.g. '0, 10' 383 //用limit的时候可以加where条件优化：select ... where id > 1234 limit 0, 10 384 public function setLimit($limit) 385 { 386 $this->limit = 'limit '.$limit; 387 return $this; 388 } 389 390 //直接查询sql语句, 返回数组格式 391 public function arrQuery($sql, $field='') 392 { 393 $this->query($sql); 394 $this->clearQuery(); 395 ($this->rs===false) && $this->error('select error: '.$sql); 396 return $this->rsToArray($field); 397 } 398 399 //如果 $field 不为空, 则返回的结果以该字段的值为索引 400 //暂不支持join 401 public function get($field='') 402 { 403 $where = $this->getWhere(); 404 $order = $this->getOrder(); 405 406 $sql = " select {$this->fields} from {$this->_dt} {$where} {$order} {$this->limit} "; 407 return $this->arrQuery($sql, $field); 408 } 409 410 //获取一条记录 411 public function getOne() 412 { 413 $this->setLimit(1); 414 $rs = $this->get(); 415 416 return !empty($rs) ? $rs[0] : []; 417 } 418 419 //获取一条记录的某一个字段的值 420 public function getOneField($field) 421 { 422 $this->setFields($field); 423 $rs = $this->getOne(); 424 425 return !empty($rs[$field]) ? $rs[$field] : ''; 426 } 427 428 //获取数据集中所有某个字段的值 429 public function getFields($field) 430 { 431 $this->setFields($field); 432 $rs = $this->get(); 433 $result = []; 434 foreach ($rs as $v) { 435 $result[] = $v[$field]; 436 } 437 unset($rs); 438 439 return $result; 440 } 441 442 //清除查询条件 443 //防止干扰下次查询 444 public function clearQuery() 445 { 446 $this->fields = '*'; 447 $this->arrWhere = []; 448 $this->order = ''; 449 $this->arrOrder = []; 450 $this->limit = ''; 451 } 452 453 //断开数据库连接 454 public function close() 455 { 456 $this->link->close(); 457 } 458 459 //事务 460 //自动提交开关 461 public function autocommit($bool) 462 { 463 $this->link->autocommit($bool); 464 } 465 466 //事务完成提交 467 public function commit() 468 { 469 $this->link->commit(); 470 } 471 472 //回滚 473 public function rollback() 474 { 475 $this->link->rollback(); 476 } 477 478 479 //输出错误sql语句 480 public function error($sql) 481 { 482 //if (IS_TEST) {} 483 exit($sql); 484 } 485 }
