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

巴特西

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

最新文章

热门文章