php 如何调试 PDO 数据库查询？

How to debug PDO database queries?

提问by Nathan Long

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.

在转向 PDO 之前，我通过连接字符串在 PHP 中创建了 SQL 查询。如果我遇到数据库语法错误，我可以只回显最终的 SQL 查询字符串，在数据库上自己尝试，然后调整它直到我修复错误，然后将其放回代码中。

Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catchblock), I can't see the query that caused them.

准备好的 PDO 语句更快、更好、更安全，但有一件事情困扰着我：我从来没有看到发送到数据库的最终查询。当我在我的 Apache 日志或我的自定义日志文件中收到有关语法的错误（我将错误记录在一个catch块中）时，我看不到导致它们的查询。

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

有没有办法捕获 PDO 发送到数据库的完整 SQL 查询并将其记录到文件中？

采纳答案by Nathan Long

Looking in the database log

查看数据库日志

Although Pascal MARTINis correct that PDO doesn't send the complete query to the database all at once, ryeguy's suggestion to use the DB's logging function actually allowed me to see the complete query as assembled and executed by the database.

尽管Pascal MARTIN是正确的 PDO 不会一次将完整的查询发送到数据库，但ryeguy建议使用数据库的日志记录功能实际上让我看到了由数据库组装和执行的完整查询。

Here's how: (These instructions are for MySQL on a Windows machine - your mileage may vary)

方法如下：（这些说明适用于 Windows 机器上的 MySQL - 您的里程可能会有所不同）

• In my.ini, under the [mysqld]section, add a logcommand, like log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• Restart MySQL.
• It will start logging every query in that file.

• 在my.ini，在该[mysqld]部分下，添加一个log命令，例如log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• 重启 MySQL。
• 它将开始记录该文件中的每个查询。

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

该文件会快速增长，因此请确保在完成测试后将其删除并关闭日志记录。

回答by Pascal MARTIN

You say this :

你这样说：

I never see the final query as it's sent to the database

我从来没有看到最后的查询，因为它被发送到数据库

Well, actually, when using prepared statements, there is no such thing as a "final query":

嗯，实际上，在使用准备好的语句时，没有“最终查询”这样的东西：

• First, a statement is sent to the DB, and prepared there
  • The database parses the query, and builds an internal representation of it
• And, when you bind variables and execute the statement, only the variables are sent to the database
  • And the database "injects" the values into its internal representation of the statement

• 首先，一个语句被发送到数据库，并在那里准备
  • 数据库解析查询，并构建它的内部表示
• 并且，当你绑定变量并执行语句时，只有变量被发送到数据库
  • 并且数据库将值“注入”到语句的内部表示中

So, to answer your question :

所以，回答你的问题：

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

有没有办法捕获 PDO 发送到数据库的完整 SQL 查询并将其记录到文件中？

No : as there is no "complete SQL query" anywhere, there is no way to capture it.

否：因为在任何地方都没有“完整的 SQL 查询”，因此无法捕获它。

The best thing you can do, for debugging purposes, is "re-construct" an "real" SQL query, by injecting the values into the SQL string of the statement.

出于调试目的，您可以做的最好的事情是“重新构建”一个“真正的”SQL 查询，方法是将值注入到语句的 SQL 字符串中。

What I usually do, in this kind of situations, is :

在这种情况下，我通常会做的是：

• echo the SQL code that corresponds to the statement, with placeholders
• and use var_dump(or an equivalent)just after, to display the values of the parameters
• This is generally enough to see a possible error, even if you don't have any "real" query that you can execute.

• 回显与语句对应的 SQL 代码，带有占位符
• 并在之后使用var_dump（或等效的）来显示参数的值
• 这通常足以看到可能的错误，即使您没有任何可以执行的“真实”查询。

This is not great, when it comes to debugging -- but that's the price of prepared statements and the advantages they bring.

这在调试方面并不是很好——但这就是准备好的语句的代价及其带来的优势。

回答by Saud Alfadhli

Sure you can debug using this mode {{ PDO::ATTR_ERRMODE }}Just add new line before your query then you will show the debug lines.

确保您可以使用此模式进行调试{{ PDO::ATTR_ERRMODE }}只需在查询前添加新行即可显示调试行。

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$db->query('SELECT *******');  

回答by fijiaaron

Probably what you want to do is use debugDumParams()It doesn't build the prepared statement for you, but it will show your parameters.

可能你想要做的是使用debugDumParams()它不会为你构建准备好的语句，但它会显示你的参数。

回答by dontaskagain

An old post but perhaps someone will find this useful;

一个旧帖子，但也许有人会发现这很有用；

function pdo_sql_debug($sql,$placeholders){
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}

回答by Matt Browne

Here's a function to see what the effective SQL will be, adpated from a comment by "Mark" at php.net:

这是一个查看有效 SQL 的函数，改编自php.net 上“Mark”的评论：

function sql_debug($sql_string, array $params = null) {
    if (!empty($params)) {
        $indexed = $params == array_values($params);
        foreach($params as $k=>$v) {
            if (is_object($v)) {
                if ($v instanceof \DateTime) $v = $v->format('Y-m-d H:i:s');
                else continue;
            }
            elseif (is_string($v)) $v="'$v'";
            elseif ($v === null) $v='NULL';
            elseif (is_array($v)) $v = implode(',', $v);

            if ($indexed) {
                $sql_string = preg_replace('/\?/', $v, $sql_string, 1);
            }
            else {
                if ($k[0] != ':') $k = ':'.$k; //add leading colon if it was left out
                $sql_string = str_replace($k,$v,$sql_string);
            }
        }
    }
    return $sql_string;
}

回答by ryeguy

No. PDO queries are not prepared on the client side. PDO simply sends the SQL query and the parameters to the database server. The databaseis what does the substitution (of the ?'s). You have two options:

不可以。客户端未准备 PDO 查询。PDO 只是将 SQL 查询和参数发送到数据库服务器。该数据库是什么呢取代（的?“S）。您有两个选择：

• Use your DB's logging function (but even then it's normally shown as two separate statements (ie, "not final") at least with Postgres)
• Output the SQL query and the paramaters and piece it together yourself

• 使用您的数据库的日志记录功能（但即使如此，至少在 Postgres 中它通常显示为两个单独的语句（即“非最终”）
• 输出 SQL 查询和参数并自己拼凑起来

回答by Zippp

almost nothing was said about error displaying except check error logs, but there's a rather helpful functionality:

除了检查错误日志外，几乎没有提到错误显示，但有一个相当有用的功能：

<?php
/* Provoke an error -- bogus SQL syntax */
$stmt = $dbh->prepare('bogus sql');
if (!$stmt) {
    echo "\PDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}
?>

(source link)

（来源链接）

it is clear that this code can be modified to be used as exception message or any other kind of error handling

很明显，可以修改此代码以用作异常消息或任何其他类型的错误处理

回答by Alireza

for example you have this pdo statement :

例如你有这个 pdo 语句：

$query="insert into tblTest (field1, field2, field3)
values (:val1, :val2, :val3)";
$res=$db->prepare($query);
$res->execute(array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
));

now you can get the executed query by defining an array like this :

现在您可以通过定义这样的数组来获取执行的查询：

$assoc=array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
);
$exQuery=str_replace(array_keys($assoc), array_values($assoc), $query);
echo $exQuery;

回答by bkilinc

Searching internet I found this as an acceptable solution. A different class is used instead of PDO and PDO functions are called through magic function calls. I am not sure this creates serious performance problems. But it can be used until a sensible logging feature is added to PDO.

搜索互联网我发现这是一个可以接受的解决方案。使用不同的类代替 PDO，并且 PDO 函数通过魔术函数调用来调用。我不确定这会造成严重的性能问题。但它可以使用，直到将合理的日志记录功能添加到 PDO。

So as per this thread, you can write a wrapper for your PDO connection which can log and throws an exception when you get a error.

因此，根据此线程，您可以为 PDO 连接编写一个包装器，它可以在出现错误时记录并抛出异常。

Here is simple example:

这是一个简单的例子：

class LoggedPDOSTatement extends PDOStatement    {

function execute ($array)    {
    parent::execute ($array);
    $errors = parent::errorInfo();
    if ($errors[0] != '00000'):
        throw new Exception ($errors[2]);
    endif;
  }

}

so you can use that class instead of PDOStatement:

因此您可以使用该类而不是 PDOStatement：

$this->db->setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('LoggedPDOStatement', array()));

Here a mentioned PDO decorator implementation:

这里提到了 PDO 装饰器实现：

class LoggedPDOStatement    {

function __construct ($stmt)    {
    $this->stmt = $stmt;
}

function execute ($params = null)    {
    $result = $this->stmt->execute ($params); 
    if ($this->stmt->errorCode() != PDO::ERR_NONE):
        $errors = $this->stmt->errorInfo();
        $this->paint ($errors[2]);
    endif;
    return $result;
}

function bindValue ($key, $value)    {
    $this->values[$key] = $value;    
    return $this->stmt->bindValue ($key, $value);
}

function paint ($message = false)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $this->stmt->queryString . '</td></tr>';
    if (count ($this->values) > 0):
    foreach ($this->values as $key => $value):
    echo '<tr><th align="left" style="background-color: #ccc;">' . $key . '</th><td>' . $value . '</td></tr>';
    endforeach;
    endif;
    echo '</table>';
    echo '</pre>';
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->stmt, $method), $params); 
}

}