[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);