[PHP] PHP PDO与mysql的连接单例防止超时情况处理
2023-02-18 15:41:49 时间
这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行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);
相关文章
- 关于微信企业付款到零钱X509Certificate2读取证书信息,发布到服务器访问不到的解决方案
- 关于ASP.NET MVC 项目在本地vs运行响应时间过长无法访问时,解决方法!
- 彻底关闭windows10自动更新解决方案
- Window10升级遇到大坑错误代码:0xc000000e完美解决方案
- ASP.NET MVC默认配置如有跳转到指定的Area区域中的对应程序中
- 使用MySQLl事件定时执行岗位七天下线任务
- .NET之微信消息模板推送
- 关于Layer web弹层组件的加载(loading)层位置居中问题
- 微信公众号支付提示mch_id参数格式错误
- C#常见金额优选类型及其三种常用的取整方式
- .NET微信网页开发之通过UnionID机制,解决用户在不同公众号,或在公众号、移动应用之间帐号统一问题
- Mui Webview下来刷新上拉加载实现
- Select下拉框使用ajax异步绑定数据
- .NET、C#基础知识
- 记一个数据库游标的实例
- .NET 使用OLEDB导入Excel数据
- 通用分页存储过程,干货无污染
- .NET如何引用System.Drawing.Drawing2D 命名空间和System.Drawing.Image及其相关概念
- .Net ADO拼接带参数的SQL语句
- 提高数据库的查询速率及其sql语句的优化问题