欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-22 10:37:46
这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行sql操作,再次处理会出现连接失败的问题,利用一个cache数组存放pdo对象与时间戳,把两次执行之间的时间进行了比较,如果间隔超过了10秒就再次new PDO创建连接,没有超过的情况下会继续使用原来的连接,并且因为每次 ......

这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行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);