db封装

db封装

以下Connection类封装支持以下几个特性

• 1.参数绑定防止sql注入
• 2.读写分离
• 3.多主多从，多节点负载均衡
• 4.故障自动摘除及自动恢复

代码实现

<?php
class Connection
{
    /**
     * @var array
     * @datetime 2020/7/5 10:20 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public $masters = [];

    /**
     * @var array
     * @datetime 2020/7/5 10:20 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public $slaves = [];

    /**
     * @var \PDO
     * @datetime 2020/7/5 10:21 AM
     * @author roach
     * @email jhq0113@163.com
     */
    protected $_master;

    /**
     * @var \PDO
     * @datetime 2020/7/5 10:21 AM
     * @author roach
     * @email jhq0113@163.com
     */
    protected $_slave;

    /**
     * @var string
     * @datetime 2020/7/6 1:32 下午
     * @author   roach
     * @email    jhq0113@163.com
     */
    protected $_driver;

    /**
     * @return string
     * @datetime 2020/7/6 1:49 下午
     * @author   roach
     * @email    jhq0113@163.com
     */
    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;
    }

    /**
     * @param array $configs
     * @return \PDO
     * @datetime 2020/7/5 12:19 PM
     * @author roach
     * @email jhq0113@163.com
     */
    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;
            }
        }
    }

    /**
     * @return \PDO
     * @throws Exception
     * @datetime 2020/7/5 10:30 AM
     * @author roach
     * @email jhq0113@163.com
     */
    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;
    }

    /**
     * @return \PDO
     * @throws Exception
     * @datetime 2020/7/5 10:31 AM
     * @author roach
     * @email jhq0113@163.com
     */
    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;
    }

    /**
     * @param bool   $useMaster
     * @param string $sql
     * @return bool|\PDOStatement
     * @throws Exception
     * @datetime 2020/7/5 12:12 PM
     * @author roach
     * @email jhq0113@163.com
     */
    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) {
            //连接断了，原因可能是超时、mysql宕机等，会重新选择一个数据库，仅重新选择一次
            if($useMaster) {
                $this->_master = null;
                $pdo = $this->_master();
                return $pdo->prepare($sql);
            } else {
                $this->_slave = null;
                $pdo = $this->_slave();
                return $pdo->prepare($sql);
            }
        }
    }

    /**
     * @return string
     * @datetime 2020/7/5 10:42 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public function lastInsertId()
    {
        return $this->_master->lastInsertId();
    }

    /**
     * @param string $sql
     * @param array  $params
     * @param bool   $useMaster
     * @return array
     * @throws Exception
     * @datetime 2020/7/5 12:13 PM
     * @author roach
     * @email jhq0113@163.com
     */
    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;
    }

    /**
     * @param $sql
     * @param array $params
     * @return int
     * @throws Exception
     * @throws \ReflectionException
     * @datetime 2020/7/5 12:13 PM
     * @author roach
     * @email jhq0113@163.com
     */
    public function execute($sql, $params = [])
    {
        $stmt = $this->_createCommand(true, $sql);
        $stmt->execute($params);

        return $stmt->rowCount();
    }

    /**
     * @return bool
     * @throws Exception
     * @datetime 2020/7/5 10:44 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public function begin()
    {
        return $this->_master()->beginTransaction();
    }

    /**
     * @return bool
     * @throws Exception
     * @datetime 2020/7/5 10:45 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public function rollback()
    {
        return $this->_master()->rollBack();
    }

    /**
     * @return bool
     * @throws Exception
     * @datetime 2020/7/5 10:48 AM
     * @author roach
     * @email jhq0113@163.com
     */
    public function commit()
    {
        return $this->_master()->commit();
    }

    /**
     * @param callable $handler
     * @return bool
     * @throws Exception
     * @datetime 2020/7/5 10:50 AM
     * @author roach
     * @email jhq0113@163.com
     */
    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