php 如何查看准备好的语句的内容？

How can I view the contents of a prepared statement?

提问by Stomped

I'm working on learning to use prepared statements with mysqli in PHP and usually, if I'm having a problem with a query I just echo it to the screen to see what it looks like as a first step.

我正在学习在 PHP 中使用带有 mysqli 的准备好的语句，通常，如果我在查询时遇到问题，我只是将它回显到屏幕上，以查看它作为第一步的样子。

How can I do that with a prepared statement?

我怎样才能用准备好的语句做到这一点？

I'd like to see the SQL statement after the variables are substituted.

我想看看变量替换后的SQL语句。

采纳答案by Pascal MARTIN

Using prepared statements:

使用准备好的语句：

• When you prepare the statement, it is sent to the MySQL server
• When you bind the variables + execute the statement, only the variables are sent to the MySQL server
• And the statement + bound variables are executed on the MySQL server -- without it re-doing the "preparation" each time the statement is executed (which is why prepared statements can be good for performance when the same statement is executed several times)

• 准备语句时，将其发送到 MySQL 服务器
• 绑定变量+执行语句时，只将变量发送到MySQL服务器
• 并且语句 + 绑定变量在 MySQL 服务器上执行——无需在每次执行语句时重新进行“准备” （这就是为什么在多次执行同一语句时准备好的语句可以提高性能的原因）

There is no "building" of an SQL query on the PHP side, so, there is no way to actually get that query.

在 PHP 端没有“构建”SQL 查询，因此，无法实际获取该查询。

Which means that if you want to see an SQL query, you have to use, well, SQL queries, and not prepared statements.

这意味着，如果您想查看 SQL 查询，则必须使用 SQL 查询，而不是准备好的语句。

回答by VolkerK

• You can use PDOStatement->debugDumpParamsto get some informations about the prepared statement (in case you're using pdo).
• Prepared statements are logged in MySQL's general log:

• 您可以使用PDOStatement->debugDumpParams来获取有关准备好的语句的一些信息（以防您使用pdo）。
• 准备好的语句记录在 MySQL 的一般日志中：

For prepared statements that are executed with the mysql_stmt_prepare() and mysql_stmt_execute() C API functions, the server writes Prepare and Execute lines to the general query log so that you can tell when statements are prepared and executed.
[...] the server writes the following lines to the general query log:
Prepare [1] SELECT ?
Execute [1] SELECT 3

对于使用 mysql_stmt_prepare() 和 mysql_stmt_execute() C API 函数执行的预准备语句，服务器将 Prepare 和 Execute 行写入常规查询日志，以便您知道何时准备和执行语句。
[...] 服务器将以下行写入一般查询日志：
Prepare [1] SELECT ?
执行 [1] 选择 3

So for debugging purposes active the general logand keep an eye on that file.

因此，出于调试目的，请激活常规日志并密切关注该文件。

edit: oh, the question has a [mysqli] tag... completely overlooked that.
If the statement isn't executed at all have you (double/tripple) checked that no error occurred along the way?

编辑：哦，问题有一个 [mysqli] 标签......完全忽略了这一点。
如果该语句根本没有执行，您（双重/三重）是否检查过在此过程中没有发生错误？

echo "<pre>Debug: start</pre>\n";

$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
if ($mysqli->connect_error) {
  die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('CREATE TEMPORARY TABLE foo (id int auto_increment, x int, primary key(id))');
if ( false=== $result) { 
 die('error : '. $mysqli->error);
}

$stmt = $mysqli->prepare('INSERT INTO foo (x) VALUES (?)');
if ( false===$stmt ) {
  die ('prepare() failed: ' . $mysqli->error);
}

$result = $stmt->bind_param('i', $x);
if ( false===$result ) {
  die('bind_param() failed');
}

$x = 1;
$result = $stmt->execute();
if ( false===$result ) {
  die('execute() failed: '.$stmt->error);
}

echo "<pre>Debug: end</pre>\n";

回答by NetVicious

I usually do this when I need to debug a prepared sql with parameters.

当我需要用参数调试准备好的 sql 时，我通常会这样做。

Example of prepare and execute:

准备和执行示例：

$sql = "SELECT VAL1, VAL2 FROM TABLE(?, '?', '?', '?', '?', ?, '?', '?', '?')";
$prep = ibase_prepare( $sql ) or die("Error");
$query = ibase_execute($prep, $param1, $param2, .....) or $err = true;
                              ^^^^^^^^^^^^^^^^^^^^^^^

The easy way to debug the resulting SQL of the sentence it's:

调试语句的结果 SQL 的简单方法是：

printf( str_replace('?', '%s', $sql), $param1, $param2, ....);
                                      ^^^^^^^^^^^^^^^^^^^^^^

You only need to do one printf, replacing the ?on the prepared SQL string by one %s. printfwill interpret all as one string, taking each parameter as placing it on each replaced %s.

您只需要执行一个 printf，将?准备好的 SQL 字符串上的替换为一个%s。printf将 all 解释为一个字符串，将每个参数视为将其放置在每个已替换的%s.

回答by myesain

I recently updated this project to include composer integration, unit testing and to better handle accepting arguments by reference (this requires updating to php 5.6).

我最近更新了这个项目以包含 Composer 集成、单元测试并更好地处理通过引用接受参数（这需要更新到 php 5.6）。

---

---

I've created a set of classes that extend the default mysqliand mysqli_stmtclasses to allow you to view a rendition of the potential query string, which should provide what you're looking for:

我创建了一组扩展默认mysqli和mysqli_stmt类的类，以允许您查看潜在查询字符串的再现，它应该提供您要查找的内容：

https://github.com/noahheck/E_mysqli

https://github.com/noahheck/E_mysqli

This is a (close to) drop-in replacement for you normal mysqliobject that returns a custom mysqli_stmtobject when you prepare()the query string. After binding your parameters, E_mysqliwill allow you to view the resultant query string as a new property of the stmtobject:

这是一个（接近）普通mysqli对象的替代品，它mysqli_stmt在您prepare()查询字符串时返回一个自定义对象。绑定参数后，E_mysqli将允许您查看结果查询字符串作为stmt对象的新属性：

$mysqli = new E_mysqli($dbHost, $dbUser, $dbPass, $dbName);

$query = "INSERT INTO registration SET name = ?, email = ?";

$stmt = $mysqli->prepare($query);

$stmt->bind_param("ss", $_POST['name'], $_POST['email']);

$stmt->execute();

echo $stmt->fullQuery;

would result in:

会导致：

INSERT INTO registration SET name = 'John Doe', email = '[email protected]'

There are some caveats with using this extension (explained in the README at the github project), but for troubleshooting troublesome areas of your application, or transitioning to an object oriented style from procedural, this should provide a level of help for most users.

使用此扩展有一些注意事项（在 github 项目的 README 中进行了解释），但是对于解决应用程序的麻烦区域，或从程序转换到面向对象的样式，这应该为大多数用户提供一定程度的帮助。

As I've outlined in the github project, I don't have any practical experience using the mysqliextension, and this project was created at the request of users of it's sister project, so any feedback that can be provided from devs using this in production would be greatly appreciated.

正如我在 github 项目中概述的那样，我没有使用该mysqli扩展的任何实践经验，并且该项目是应其姊妹项目用户的要求创建的，因此在生产中使用此扩展的开发人员可以提供任何反馈将不胜感激。

Disclaimer- As I said, I made this extension.

免责声明- 正如我所说，我做了这个扩展。

回答by Orderbynull

You can use tool like Lottip. The idea is act like MySQL proxy. It parses MySQL packets, extracts query and it's params so you can see prepared statements with it's content.

您可以使用Lottip 之类的工具。这个想法就像 MySQL 代理一样。它解析 MySQL 数据包，提取查询和它的参数，以便您可以查看准备好的语句及其内容。

回答by chelmertz

Agreeing with Pascal MARTIN (+1) so I suggest another technique for debugging: var_dump()or log every variable you're inserting into the statement, that way you should be able to figure out if it is wrong data or logically wrong SQL.

同意 Pascal MARTIN (+1)，所以我建议使用另一种调试技术：var_dump()或记录您插入语句中的每个变量，这样您就应该能够确定它是错误的数据还是逻辑上错误的 SQL。