db封装
以下Connection类封装支持以下几个特性
- 1.参数绑定防止sql注入
- 2.读写分离
- 3.多主多从，多节点负载均衡
- 4.故障自动摘除及自动恢复
代码实现
<?php
class Connection
{
public $masters = [];
public $slaves = [];
protected $_master;
protected $_slave;
protected $_driver;
public function getDriver()
{
if(is_null($this->_driver)) {
$this->_driver = ucfirst(substr($this->masters[0]['dsn'], 0, strpos($this->masters[0]['dsn'], ':')));
}
return $this->_driver;
}
protected function _select($configs = [])
{
shuffle($configs);
foreach ($configs as $config) {
try {
if(!isset($config['options'])) {
$config['options'] = [];
}
$config['options'][ \PDO::ATTR_ERRMODE ] = \PDO::ERRMODE_EXCEPTION;
$pdo = new \PDO($config['dsn'], $config['username'], $config['password'], $config['options']);
return $pdo;
}catch (\Throwable $throwable) {
continue;
}
}
}
protected function _master()
{
if(is_null($this->_master)) {
$this->_master = $this->_select($this->masters);
if(is_null($this->_master)) {
throw new Exception('没有可用的master数据库了');
}
}
return $this->_master;
}
protected function _slave()
{
if(is_null($this->_slave)) {
if(empty($this->slaves)) {
$this->slaves = $this->masters;
}
$this->_slave = $this->_select($this->slaves);
if(is_null($this->_slave)) {
throw new Exception('没有可用的slave数据库了');
}
}
return $this->_slave;
}
protected function _createCommand($useMaster, $sql)
{
try {
if($useMaster) {
$pdo = $this->_master();
return $pdo->prepare($sql);
} else {
$pdo = $this->_slave();
return $pdo->prepare($sql);
}
}catch (\Throwable $throwable) {
if($useMaster) {
$this->_master = null;
$pdo = $this->_master();
return $pdo->prepare($sql);
} else {
$this->_slave = null;
$pdo = $this->_slave();
return $pdo->prepare($sql);
}
}
}
public function lastInsertId()
{
return $this->_master->lastInsertId();
}
public function queryAll($sql, $params = [], $useMaster = false)
{
$stmt = $this->_createCommand($useMaster, $sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$stmt->closeCursor();
return $rows;
}
public function execute($sql, $params = [])
{
$stmt = $this->_createCommand(true, $sql);
$stmt->execute($params);
return $stmt->rowCount();
}
public function begin()
{
return $this->_master()->beginTransaction();
}
public function rollback()
{
return $this->_master()->rollBack();
}
public function commit()
{
return $this->_master()->commit();
}
public function transaction(callable $handler)
{
$result = $this->begin();
if(!$result) {
return false;
}
$result = call_user_func($handler, $this);
if($result) {
return $this->commit();
}
$this->rollback();
return false;
}
}
使用
<?php
$connection = new Connection([
'masters' => [
[
'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
'username' => 'browser',
'password' => 'browser.360'
]
],
'slaves' => [
[
'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
'username' => 'browser',
'password' => 'browser.360'
],
[
'dsn' => 'mysql:host=10.16.49.113;port=3306;dbname=doctor_v6;charset=utf8',
'username' => 'browser',
'password' => 'browser.360'
],
]
]);
$rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
uniqid('u'), uniqid('t'), time()
]);
$userId = $connection->lastInsertId();
echo '插入的用户id为：'.$userId.';受影响行数为：'.$rows.PHP_EOL;
$rows = $connection->execute('UPDATE `t_user` SET add_time=? WHERE id=?', [
time()+1, $userId
]);
echo '修改的受影响行数为：'.$rows.PHP_EOL;
$users = $connection->queryAll('SELECT * FROM `t_user` WHERE id=?', [
$userId
]);
echo '插入和修改的用户信息为：'.json_encode($users[0], JSON_UNESCAPED_UNICODE).PHP_EOL;
$users = $connection->queryAll('SELECT * FROM `t_user` LIMIT 5', [], true);
echo '主库读取到的数据：'.json_encode($users, JSON_UNESCAPED_UNICODE).PHP_EOL;
try {
$connection->begin();
$rows = $connection->execute('INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)', [
uniqid('u'), uniqid('t'), time()
]);
if($rows < 1) {
throw new \Exception('插入失败');
}
$userId = $connection->lastInsertId();
if($userId % 2 !== 0) {
throw new \Exception('用户id只能为偶数');
}
$connection->commit();
echo '提交事务成功'.PHP_EOL;
}catch (\Exception $exception) {
$connection->rollback();
echo $exception->getMessage().PHP_EOL;
}
如果您需要一个orm封装，可以通过以下指令安装
composer require jhq0113/roach-orm
jhq0113/roach-orm源码及文档地址
- https://github.com/jhq0113/roach-orm
学习更多内容: https://404.360tryst.com
我的视频课程: https://edu.csdn.net/course/detail/9933
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
