最近在swoole中使用php pdo扩展访问数据库的时候，发现了一个很有意思的事情。
我测试用的版本是 PHP 7.1.13
官方手册明确到告诉我们，使用PDO是这样关闭数据库连接的，只需要将PDO对象置为null即可，这段代码就是手册里的
<?php
try {
$dbh = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', '');
foreach($dbh->query('SELECT * from test') as $row) {
print_r($row);
}
sleep(100);
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
我实际测下来也的确如此。 这是query执行前的MySQL的PROCESSLIST
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 10 | root | localhost:56477 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
这是query运行后，PDO 对象置为null前的MySQL的PROCESSLIST
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 10 | root | localhost:56477 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 14 | root | localhost:56572 | test | Sleep | 2 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
PDO 对象置为null后，数据库连接的的确确关闭了，看来官方手册没错
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 10 | root | localhost:56477 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
但是接下来有意思的事情就要发生了，看这段代码
<?php
$pdo = new PDO(
'mysql:dbname=test;host=127.0.0.1;port=3306;charset=utf8mb4', 'root', '',
[
PDO::ATTR_TIMEOUT => 10,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
$s = $pdo->prepare("select * from test WHERE id=:id");
$s->execute(['id' => 1]);
$s->fetchAll();
$pdo = null;
sleep(100);
在PDO对象被置为null之后，MySQL的连接并没有被真的释放
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 10 | root | localhost:56477 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 18 | root | localhost:56606 | test | Sleep | 3 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
是不是很诡异？没关系，我们在改下这段代码
<?php
$pdo = new PDO(
'mysql:dbname=test;host=127.0.0.1;port=3306;charset=utf8mb4', 'root', '',
[
PDO::ATTR_TIMEOUT => 10,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
$s = $pdo->prepare("select * from test WHERE id=:id");
$s->execute(['id' => 1]);
$s->fetchAll();
$s=null;
$pdo = null;
sleep(100);
在$s也被置为null之后将pdo也置为null，pdo申请的数据库连接就被真真正正的释放了
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 10 | root | localhost:56477 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
这是为什么？看下面这段代码
$pdo = new PDO(
'mysql:dbname=test;host=127.0.0.1;port=3306;charset=utf8mb4', 'root', '',
[
PDO::ATTR_TIMEOUT => 10,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
debug_zval_dump($pdo);
$s = $pdo->prepare("select * from test WHERE id=:id");
debug_zval_dump($pdo);
$s->execute(['id' => 1]);
$s->fetchAll();
$s=null;
debug_zval_dump($pdo);
$pdo = null;
sleep(100);
输出
object(PDO)#1 (0) refcount(2){
}
object(PDO)#1 (0) refcount(3){
}
object(PDO)#1 (0) refcount(2){
}
你会发现，当$pdo对象第一次打印的时候他的引用计数是2（为什么是2，而不是1呢？传送门 http://php.net/manual/zh/function.debug-zval-dump.php ）在pdo−>prepare之后引用计数变成了3，也就是说，即使pdo−>prepare之后引用计数变成了3，也就是说，即使pdo被置为null，它依然还是会被$s引用，并不会被PHP的GC给回收掉，所以它创建的数据库连接也不会被释放。
在FPM模式下的PHP，这个问题并不会引起什么问题，但是在SWOOLE这种运行环境下，假如你的PDO对象在被一些全局变量（传送门 https://wiki.swoole.com/wiki/page/p-zend_mm.html ） 引用，即使你将PDO对象置为null，你的数据库连接依然不会被释放。
