A single example of PHP PDO and mysql connection to prevent timeout

This database class mainly deals with creating database objects in single instance mode. If there are two long-time intervals to execute sql operations, the problem of connection failure will occur when processing again. A cache array is used to store pdo objects and timestamps, and the time between the two executions is compared. If the interval is more than 10 seconds, new PDO creates the connection again, The original connection will continue to be used if it is not exceeded, and the connection will be renewed after each use. The timestamp in the cache array will also be renewed
The next connection will be obtained from the cache array every time the operation is executed. If the execution time is less than 10 seconds, there will only be one connection

In the code, read and write are separated. If the first six characters of the sql statement are select ed, query the slave database, and other operations query the master database. The master database and the slave database create different PDO object connections in 0 and 1 respectively in the configuration array

The code is as follows:

<?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",//Main library
"mysql:host=127.0.0.2;port=3306;dbname=surframe"//From library
);
$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");//Slave library used
$rows=$db->fetchAll();
var_dump($db);


$db=SinaPdoAdapter::getInstance($config);
$db->execute("select * from admin_users");//Slave library used
$rows=$db->fetchAll();
var_dump($db);

I hope that the above content can help you. Many PHPer will encounter some problems and bottlenecks when they are advanced. There is no sense of direction when they write too much business code. I don't know where to start to improve. I collated some information about this, including but not limited to: distributed architecture, high scalability, high performance, high concurrency, server performance tuning, TP6, laravel, YII2, Redis, SW Advanced advanced dry goods of multiple knowledge points, such as oole, Swoft, Kafka, Mysql optimization, shell script, Docker, microservice, Nginx, etc., can be shared for free. You need to stamp here PHP advanced architect > > > free access to video and interview documents

Tags: Programming PDO Database SQL MySQL

Posted on Fri, 29 May 2020 04:02:35 -0400 by kaveman50