如何调试PDO数据库查询?

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 catch block), 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查询并将其记录到文件中?

17 个解决方案

#1

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
  • 数据库解析查询，并构建查询的内部表示
• 首先，将一条语句发送到DB，并在那里准备数据库解析查询，并构建查询的内部表示
• 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
• 使用占位符来响应与语句对应的SQL代码
• and use var_dump (or an equivalent) just after, to display the values of the parameters
• 然后使用var_dump(或等效的)来显示参数的值
• This is generally enough to see a possible error, even if you don't have any "real" query that you can execute.
• 这通常足以看到可能的错误，即使您没有任何可以执行的“真实”查询。

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

当涉及到调试时，这不是很好——但这是准备好的语句的代价和它们带来的好处。

#2

Looking in the database log

Although Pascal MARTIN is 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关于使用DB的日志功能的建议实际上让我看到了由数据库组装和执行的完整查询。

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 log command, like log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• 在我的。在[mysqld]小节下，添加一个日志命令，比如log="C:\程序文件MySQL\MySQL \MySQL Server 5.1\data\ MySQL .log"
• Restart MySQL.
• 重新启动MySQL。
• It will start logging every query in that file.
• 它将开始记录该文件中的每个查询。

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

该文件将快速增长，所以请确保在完成测试时删除它并关闭日志记录。

#3

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 *******');  

#4

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;
}

#5

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()它不会为您构建准备好的语句，但是它会显示您的参数。

#6

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

不。在客户端没有准备PDO查询。PDO只是将SQL查询和参数发送到数据库服务器。数据库是什么替代了?你有两个选择:

• Use your DB's logging function (but even then it's normally shown as two separate statements (ie, "not final") at least with Postgres)
• 使用您的DB的日志功能(但即使是这样，它通常也会显示为两个独立的语句(即“不是最终的”)，至少与Postgres是一样的。
• Output the SQL query and the paramaters and piece it together yourself
• 输出SQL查询和参数，然后自己将它们拼接在一起

#7

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

下面是一个函数，通过php.net上的“Mark”注释，可以看到有效的SQL是什么:

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;
}

#8

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

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

#9

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;

#10

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 decorator实现:

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); 
}

}

#11

To log MySQL in WAMP, you will need to edit the my.ini (e.g. under wamp\bin\mysql\mysql5.6.17\my.ini)

要在WAMP中登录MySQL，需要编辑my。ini下(例如mysql wamp \ bin \ \ mysql5.6.17 \ my.ini)

and add to [mysqld]:

并添加(mysqld):

general_log = 1
general_log_file="c:\\tmp\\mysql.log"

#12

Here is a function I made to return a SQL query with "resolved" parameters.

这里有一个函数，我用“解析”参数返回一个SQL查询。

function paramToString($query, $parameters) {
    if(!empty($parameters)) {
        foreach($parameters as $key => $value) {
            preg_match('/(\?(?!=))/i', $query, $match, PREG_OFFSET_CAPTURE);
            $query = substr_replace($query, $value, $match[0][1], 1);
        }
    }
    return $query;
    $query = "SELECT email FROM table WHERE id = ? AND username = ?";
    $values = [1, 'Super'];

    echo paramToString($query, $values);

Assuming you execute like this

假设你像这样执行

$values = array(1, 'SomeUsername');
$smth->execute($values);

This function DOES NOT add quotes to queries but does the job for me.

这个函数不向查询添加引号，但是为我做了这个工作。

#13

How to debug PDO mysql database queries in Ubuntu

TL;DR Log all your queries and tail the mysql log.

DR记录所有查询并跟踪mysql日志。

These directions are for my install of Ubuntu 14.04. Issue command lsb_release -a to get your version. Your install might be different.

这些是我安装Ubuntu 14.04的指南。发出命令lsb_release -a以获得您的版本。您的安装可能不同。

Turn on logging in mysql

1. Go to your dev server cmd line
2. 转到您的开发服务器cmd线
3. Change directories cd /etc/mysql. You should see a file called my.cnf. That’s the file we’re gonna change.
4. 更改目录cd /etc/mysql.您应该看到一个名为my.cnf的文件。这就是我们要改变的文件。
5. Verify you’re in the right place by typing cat my.cnf | grep general_log. This filters the my.cnf file for you. You should see two entries: #general_log_file = /var/log/mysql/mysql.log && #general_log = 1.
6. 输入cat my.cnf | grep general_log，验证您的位置是否正确。这将过滤my.cnf文件。您应该看到两个条目:#general_log_file = /var/log/mysql/mysql日志& #general_log = 1。
7. Uncomment those two lines and save via your editor of choice.
8. 取消这两行注释，然后通过编辑器保存。
9. Restart mysql: sudo service mysql restart.
10. 重新启动mysql: sudo服务。
11. You might need to restart your webserver too. (I can’t recall the sequence I used). For my install, that’s nginx: sudo service nginx restart.
12. 您可能也需要重新启动web服务器。(我记不起我用过的顺序了)。对于我的安装，这是nginx: sudo服务nginx重新启动。

Nice work! You’re all set. Now all you have to do is tail the log file so you can see the PDO queries your app makes in real time.

不错的工作!现在你要做的就是跟踪日志文件，这样你就可以实时看到你的应用进行的PDO查询。

Tail the log to see your queries

Enter this cmd tail -f /var/log/mysql/mysql.log.

输入cmd尾部-f /var/log/mysql/mysql. logl。

Your output will look something like this:

您的输出将如下所示:

73 Connect  xyz@localhost on your_db
73 Query    SET NAMES utf8mb4
74 Connect  xyz@localhost on your_db
75 Connect  xyz@localhost on your_db
74 Quit 
75 Prepare  SELECT email FROM customer WHERE email=? LIMIT ?
75 Execute  SELECT email FROM customer WHERE email='[e-mail]' LIMIT 5
75 Close stmt   
75 Quit 
73 Quit 

Any new queries your app makes will automatically pop into view, as long as you continue tailing the log. To exit the tail, hit cmd/ctrl c.

只要您继续跟踪日志，您的应用程序进行的任何新查询都会自动弹出。要退出尾部，点击cmd/ctrl c。

Notes

1. Careful: this log file can get huge. I’m only running this on my dev server.
2. 注意:这个日志文件可能会变得很大。我只在我的dev服务器上运行这个。
3. Log file getting too big? Truncate it. That means the file stays, but the contents are deleted. truncate --size 0 mysql.log.
4. 日志文件变大了?截断。这意味着文件保持不变，但内容被删除。0 mysql.log截断,大小。
5. Cool that the log file lists the mysql connections. I know one of those is from my legacy mysqli code from which I'm transitioning. The third is from my new PDO connection. However, not sure where the second is coming from. If you know a quick way to find it, let me know.
6. 日志文件列出了mysql连接。我知道其中一个来自我正在转换的遗留mysqli代码。第三个是我新的PDO连接。然而，不知道第二个来自哪里。如果你知道快速找到它的方法，请告诉我。

Credit & thanks

Huge shout out to Nathan Long’s answer above for the inspo to figure this out on Ubuntu. Also to dikirill for his comment on Nathan’s post which lead me to this solution.

向内森·朗的答案大声呼喊，让浪潮在Ubuntu上找到答案。同时也感谢dikirill对Nathan的文章的评论，这让我想到了这个解决方案。

Love you *!

*爱你!

#14

The problem I had with the solution to catch PDO exemptions for debuging purposes is that it only caught PDO exemptions (duh), but didn't catch syntax errors which were registered as php errors (I'm not sure why this is, but "why" is irrelevant to the solution). All my PDO calls come from a single table model class that I extended for all my interactions with all tables... this complicated things when I was trying to debug code, because the error would register the line of php code where my execute call was called, but didn't tell me where the call was, actually, being made from. I used the following code to solve this problem:

为调试目的捕获PDO豁免权的解决方案的问题是，它只捕获了PDO豁免权(duh)，但没有捕获注册为php错误的语法错误(我不确定为什么会这样，但是“为什么”与解决方案无关)。我所有的PDO调用都来自一个表模型类，我对所有表的交互进行了扩展……当我尝试调试代码时，这很复杂，因为错误会在调用我的执行调用时注册php代码行，但是没有告诉我调用在哪里，实际上，是由。我用下面的代码来解决这个问题:

/**
 * Executes a line of sql with PDO.
 * 
 * @param string $sql
 * @param array $params
 */
class TableModel{
    var $_db; //PDO connection
    var $_query; //PDO query

    function execute($sql, $params) { 
        //we're saving this as a global, so it's available to the error handler
        global $_tm;
        //setting these so they're available to the error handler as well
        $this->_sql = $sql;
        $this->_paramArray = $params;            

        $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->_query = $this->_db->prepare($sql);

        try {
            //set a custom error handler for pdo to catch any php errors
            set_error_handler('pdoErrorHandler');

            //save the table model object to make it available to the pdoErrorHandler
            $_tm = $this;
            $this->_query->execute($params);

            //now we restore the normal error handler
            restore_error_handler();
        } catch (Exception $ex) {
            pdoErrorHandler();
            return false;
        }            
    }
}

So, the above code catches BOTH PDO exceptions AND php syntax errors and treats them the same way. My error handler looks something like this:

因此，上面的代码捕获了PDO异常和php语法错误，并以相同的方式处理它们。我的错误处理程序是这样的:

function pdoErrorHandler() {
    //get all the stuff that we set in the table model
    global $_tm;
    $sql = $_tm->_sql;
    $params = $_tm->_params;
    $query = $tm->_query;

    $message = 'PDO error: ' . $sql . ' (' . implode(', ', $params) . ") \n";

    //get trace info, so we can know where the sql call originated from
    ob_start();
    debug_backtrace(); //I have a custom method here that parses debug backtrace, but this will work as well
    $trace = ob_get_clean();

    //log the error in a civilized manner
    error_log($message);

    if(admin(){
        //print error to screen based on your environment, logged in credentials, etc.
        print_r($message);
    }
}

If anyone has any better ideas on how to get relevant info to my error handler than setting the table model as a global variable, I would be happy to hear it and edit my code.

如果有人对如何向我的错误处理程序获取相关信息有更好的想法，而不是将表模型设置为全局变量，我很高兴听到它并编辑我的代码。

#15

this code works great for me :

这个代码对我来说非常有用:

echo str_replace(array_keys($data), array_values($data), $query->queryString);

Don't forget to replace $data and $query by your names

不要忘记用您的名字替换$data和$query

#16

i use this class to debug PDO (with Log4PHP)

我使用这个类调试PDO(使用Log4PHP)

<?php

/**
 * Extends PDO and logs all queries that are executed and how long
 * they take, including queries issued via prepared statements
 */
class LoggedPDO extends PDO
{

    public static $log = array();

    public function __construct($dsn, $username = null, $password = null, $options = null)
    {
        parent::__construct($dsn, $username, $password, $options);
    }

    public function query($query)
    {
        $result = parent::query($query);
        return $result;
    }

    /**
     * @return LoggedPDOStatement
     */
    public function prepare($statement, $options = NULL)
    {
        if (!$options) {
            $options = array();
        }
        return new \LoggedPDOStatement(parent::prepare($statement, $options));
    }
}

/**
 * PDOStatement decorator that logs when a PDOStatement is
 * executed, and the time it took to run
 * @see LoggedPDO
 */
class LoggedPDOStatement
{

    /**
     * The PDOStatement we decorate
     */
    private $statement;
    protected $_debugValues = null;

    public function __construct(PDOStatement $statement)
    {
        $this->statement = $statement;
    }

    public function getLogger()
    {
        return \Logger::getLogger('PDO sql');
    }

    /**
     * When execute is called record the time it takes and
     * then log the query
     * @return PDO result set
     */
    public function execute(array $params = array())
    {
        $start = microtime(true);
        if (empty($params)) {
            $result = $this->statement->execute();
        } else {
            foreach ($params as $key => $value) {
                $this->_debugValues[$key] = $value;
            }
            $result = $this->statement->execute($params);
        }

        $this->getLogger()->debug($this->_debugQuery());

        $time = microtime(true) - $start;
        $ar = (int) $this->statement->rowCount();
        $this->getLogger()->debug('Affected rows: ' . $ar . ' Query took: ' . round($time * 1000, 3) . ' ms');
        return $result;
    }

    public function bindValue($parameter, $value, $data_type = false)
    {
        $this->_debugValues[$parameter] = $value;
        return $this->statement->bindValue($parameter, $value, $data_type);
    }

    public function _debugQuery($replaced = true)
    {
        $q = $this->statement->queryString;

        if (!$replaced) {
            return $q;
        }

        return preg_replace_callback('/:([0-9a-z_]+)/i', array($this, '_debugReplace'), $q);
    }

    protected function _debugReplace($m)
    {
        $v = $this->_debugValues[$m[0]];

        if ($v === null) {
            return "NULL";
        }
        if (!is_numeric($v)) {
            $v = str_replace("'", "''", $v);
        }

        return "'" . $v . "'";
    }

    /**
     * Other than execute pass all other calls to the PDOStatement object
     * @param string $function_name
     * @param array $parameters arguments
     */
    public function __call($function_name, $parameters)
    {
        return call_user_func_array(array($this->statement, $function_name), $parameters);
    }
}

#17

I've created a modern Composer-loaded project / repository for exactly this here:

为此，我创建了一个现代的组合加载项目/存储库:

pdo-debug

Find the project's GitHub home here, see a blog post explaining it here. One line to add in your composer.json, and then you can use it like this:

在这里找到项目的GitHub主页，在这里看到一篇解释它的博客文章。在你的作曲家中加入一行。json，然后可以这样使用:

echo debugPDO($sql, $parameters);

$sql is the raw SQL statement, $parameters is an array of your parameters: The key is the placeholder name (":user_id") or the number of the unnamed parameter ("?"), the value is .. well, the value.

$sql是原始sql语句，$parameters是您的参数数组:键是占位符名称(“:user_id”)或未命名参数的数量(“?”)，值为。的价值。

The logic behind: This script will simply grad the parameters and replace them into the SQL string provided. Super-simple, but super-effective for 99% of your use-cases. Note: This is just a basic emulation, not a real PDO debugging (as this is not possible as PHP sends raw SQL and parameters to the MySQL server seperated).

背后的逻辑:该脚本将简单地获取参数并将它们替换为提供的SQL字符串。超级简单，但是对于99%的用例来说超级有效。注意:这只是一个基本的模拟，而不是真正的PDO调试(因为PHP不可能将原始SQL和参数发送到隔离的MySQL服务器)。

A big thanks to bigwebguy and Mike from the * thread Getting raw SQL query string from PDO for writing basically the entire main function behind this script. Big up!

非常感谢bigwebguy和Mike来自*线程，从PDO获取原始SQL查询字符串，主要用于编写这个脚本的整个主要功能。大了!