为此,MySQL协议支持特殊命令COM_PING,并且C API有一个调用
mysql_ping()来发送它.这将测试连接是否处于活动状态.
遗憾的是,如果您使用当前版本的PDO,则不支持这些功能.您只能提交SQL查询字符串,而不能提交特殊命令. PDO现在使用mysqlnd驱动程序,它有其优点,但不支持重新连接选项.所以问题无论如何都是没有意义的.
我不知道比尝试发出像SELECT 1这样的“虚拟”查询更好的解决方案,捕获异常,如果你得到错误代码2006(服务器已经消失),那么重新连接.
您可以创建一个单独的类来保存数据库连接,并在每次应用程序代码调用getConnection()时测试实时连接.这是我测试的一个例子:
class DB
{
protected static $pdo = null;
public static function getConnection() {
// initialize $pdo on first call
if (self::$pdo == null) {
self::init();
}
// now we should have a $pdo, whether it was initialized on this call or a previous one
// but it could have experienced a disconnection
try {
echo "Testing connection...\n";
$old_errlevel = error_reporting(0);
self::$pdo->query("SELECT 1");
} catch (PDOException $e) {
echo "Connection failed, reinitializing...\n";
self::init();
}
error_reporting($old_errlevel);
return self::$pdo;
}
protected static function init() {
try {
echo "Opening new connection...\n";
self::$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die($e->getMessage());
}
}
}
像这样用它:
echo "Query for 2:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 2")->fetchColumn() . "\n";
echo "\nSleeping 10 seconds...\n";
sleep(10);
echo "\n";
echo "Query for 3:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 3")->fetchColumn() . "\n";
输出:
Query for 2:
Opening new connection...
Testing connection...
2
Sleeping 10 seconds...
Query for 3:
Testing connection...
Connection failed, reinitializing...
Opening new connection...
3
您可以看到它检测到连接失败,并重新初始化.