从 PDO 准备好的语句中获取原始 SQL 查询字符串 [英] Getting raw SQL query string from PDO prepared statements

问题描述

有没有办法在准备好的语句上调用 PDOStatement::execute() 时执行原始 SQL 字符串?出于调试目的，这将非常有用.

Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.

推荐答案

我假设您的意思是您想要最终的 SQL 查询，并将参数值插入其中.我知道这对调试很有用，但这不是准备好的语句的工作方式.参数不会与客户端准备好的语句组合，因此 PDO 永远不应访问与其参数组合的查询字符串.

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

做prepare()时SQL语句发送到数据库服务器，做execute()时单独发送参数.MySQL 的一般查询日志确实显示了在执行() 之后插入值的最终 SQL.以下是我的一般查询日志的摘录.我从 mysql CLI 运行查询，而不是从 PDO，但原理是一样的.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

如果您设置 PDO 属性 PDO::ATTR_EMULATE_PREPARES，您也可以获得您想要的.在这种模式下，PDO 将参数插入到 SQL 查询中，并在您执行 () 时发送整个查询.这不是一个真正的预准备查询.您将通过在 execute() 之前将变量插入到 SQL 字符串中来规避预准备查询的好处.

You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute(). This is not a true prepared query. You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

来自@afilina 的重新评论:

Re comment from @afilina:

不，文本 SQL 查询不在执行过程中与参数结合.所以 PDO 没有什么可以向您展示的.

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

在内部，如果您使用 PDO::ATTR_EMULATE_PREPARES，PDO 会复制 SQL 查询并将参数值插入其中，然后再进行准备和执行.但是 PDO 不会公开这个修改后的 SQL 查询.

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

PDOStatement 对象有一个属性 $queryString，但这仅在 PDOStatement 的构造函数中设置，并且在使用参数重写查询时不会更新.

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

要求 PDO 公开重写的查询是合理的功能请求.但即使这样也不会给你完整"的查询，除非你使用 PDO::ATTR_EMULATE_PREPARES.

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

这就是为什么我在上面展示了使用 MySQL 服务器的一般查询日志的解决方法，因为在这种情况下，即使是带有参数占位符的准备好的查询也会在服务器上重写，参数值回填到查询字符串中.但这仅在日志记录期间完成，而不是在查询执行期间完成.

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

这篇关于从 PDO 准备好的语句中获取原始 SQL 查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！