• [PHP] PHP PDO与mysql的连接单例防止超时情况处理


    这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行sql操作,再次处理会出现连接失败的问题,利用一个cache数组存放pdo对象与时间戳,把两次执行之间的时间进行了比较,如果间隔超过了10秒就再次new PDO创建连接,没有超过的情况下会继续使用原来的连接,并且因为每次使用后会使连接续期,cache数组里的时间戳也进行了续期.
    每次执行操作都会从cache数组中获取下连接,多次执行不超过10秒的情况下,只会有一个连接

    代码中实现读写分离,判断sql语句前面6个字符是select的就查询从库,其余操作查询主库.主库和从库就是分别在配置数组中0和1创建不同的PDO对象连接

    代码如下:

    <?php
    class SinaPdoAdapter{
        const MASTER    = 0;
        const SLAVE     = 1;
        const DEFAULT_CACHE_EXPIRETIME = 10;
        private static $options = array(
            PDO::ATTR_AUTOCOMMIT            => true,
            PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
            //PDO::ATTR_PERSISTENT            => true,
        );
        private $dsn = null;
        private $username = null;
        private $password = null;
        private $timeout = null;
        private $charset = null;
        private $conns = array();
        private $conn = null;
        private $stmt = null;
        private static $obj=null;
        private function __construct($dsn, $username, $password, $timeout = null, $charset = null){
            $this->dsn = $dsn;
            if (!is_array($username)) {
                $this->username = array($username);
            } else {
                $this->username = $username;
            }
            if (!is_array($password)) {
                $this->password = array($password);
            } else {
                $this->password = $password;
            }
            $this->timeout = intval($timeout);
            $this->charset = $charset;
        }
        private function getConnection($id = self::MASTER){
            if (!isset($this->dsn[$id])) {
                $id = self::MASTER;
            }
            $conn = $this->getCachedConn($id);
            if ($conn) {
                return $conn;
            }
            $opts = self::$options;
            if ($this->timeout > 0) {
                $opts[PDO::ATTR_TIMEOUT] = $this->timeout;
            }
            $username = isset($this->username[$id]) ? $this->username[$id] : $this->username[self::MASTER];
            $password = isset($this->password[$id]) ? $this->password[$id] : $this->password[self::MASTER];
            $conn = new PDO($this->dsn[$id], $username, $password, $opts);
            $this->cacheConn($id, $conn);
            if ($this->charset) {
                $conn->exec('set names ' . $this->charset);
            }
            return $conn;
        }
    
        public function execute($sql, $params = array()){
            $cmd = substr($sql, 0, 6);
            if (strcasecmp($cmd, 'select') === 0) {
                $conn = $this->getConnection(self::SLAVE);
            } else {
                $conn = $this->getConnection(self::MASTER);
            }
            $stmt = $conn->prepare($sql);
            $stmt->execute($params);
            $this->stmt = $stmt;
            $this->conn = $conn;
        }
    
        public function fetch(){
            return $this->stmt->fetch();
        }
    
        public function fetchAll(){
            return $this->stmt->fetchAll();
        }
        public function lastInsertId(){
            return $this->conn->lastInsertId();
        }
        public function rowCount(){
            return $this->stmt->rowCount();
        }
    
        public static function getInstance($conf){
            if(self::$obj == null){
                self::$obj = new self($conf->dsn,$conf->username,$conf->password,$conf->timeout,$conf->charset);
            }
            return self::$obj;
        }
    
        private function getCachedConn($id){
            if (!isset($this->conns[$id])) {
                return null;
            }
            list($conn, $timeout) = $this->conns[$id];
            if (time() < $timeout) {
                $this->cacheConn($id, $conn);
                return $conn;
            } else {
                return null;
            }
        }
        private function cacheConn($id, $conn){
            $timeout = time();
            if ($this->timeout) {
                $timeout += $this->timeout;
            } else {
                $timeout += self::DEFAULT_CACHE_EXPIRETIME;
            }
            $this->conns[$id] = array($conn, $timeout);
        }
    }
    
    $config=new stdClass();
    $config->dsn=array(
    "mysql:host=127.0.0.1;port=3306;dbname=surframe",//主库
    "mysql:host=127.0.0.2;port=3306;dbname=surframe"//从库
    );
    $config->username=array(
    'root', 'root',
    );
    $config->password=array(
    'taoshihan1', 'taoshihan1',
    );
    $config->timeout=10;
    $config->charset="utf8";
    
    
    
    
    $db=SinaPdoAdapter::getInstance($config);
    $db->execute("select * from admin_users");//使用的从库
    $rows=$db->fetchAll();
    var_dump($db);
    
    
    $db=SinaPdoAdapter::getInstance($config);
    $db->execute("select * from admin_users");//使用的从库
    $rows=$db->fetchAll();
    var_dump($db);
  • 相关阅读:
    celery worker的工作模式
    动态加载js
    PHP加密解密
    js加载div, 元素事件不生效问题
    任意页面加载聊天框组件(也可用于其他)
    重装系统
    vue 跨域问题
    859. Buddy Strings
    316. Remove Duplicate Letters
    654. Maximum Binary Tree
  • 原文地址:https://www.cnblogs.com/taoshihan/p/11869298.html
Copyright © 2020-2023  润新知