这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行sql操作,再次处理会出现连接失败的问题,利用一个cache数组存放pdo对象与时间戳,把两次执行之间的时间进行了比较,如果间隔超过了10秒就再次new PDO创建连接,没有超过的情况下会继续使用原来的连接,并且因为每次使用后会使连接续期,cache数组里的时间戳也进行了续期.
每次执行操作都会从cache数组中获取下连接,多次执行不超过10秒的情况下,只会有一个连接
代码中实现读写分离,判断sql语句前面6个字符是select的就查询从库,其余操作查询主库.主库和从库就是分别在配置数组中0和1创建不同的PDO对象连接
代码如下:
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);
以上内容希望帮助到大家,很多PHPer在进阶的时候总会遇到一些问题和瓶颈,业务代码写多了没有方向感,不知道该从那里入手去提升,对此我整理了一些资料,包括但不限于:分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6,laravel,YII2,Redis,Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家,需要戳这里