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