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

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语句。

6 个解决方案

#1

Using prepared statements:

使用预处理语句:

When you prepare the statement, it is sent to the MySQL server
当您准备语句时，它被发送到MySQL服务器。
When you bind the variables + execute the statement, only the variables are sent to the MySQL server
当您绑定变量+执行语句时，只有变量被发送到MySQL服务器
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服务器上执行——没有它在每次执行语句时重新做“准备”(这就是为什么在同一语句执行几次时，准备好的语句可以对性能有好处)

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查询，而不是准备语句。

#2

You can use PDOStatement->debugDumpParams to get some informations about the prepared statement (in case you're using pdo).
您可以使用PDOStatement->debugDumpParams获取关于准备好的语句的一些信息(如果您使用pdo)。
Prepared statements are logged in MySQL's general log:
准备好的语句记录在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

So for debugging purposes active the general log and 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]标签……完全被忽视。如果语句没有执行，那么您(double/tripple)检查过没有发生错误吗?

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

#3

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. printf will interpret all as one string, taking each parameter as placing it on each replaced %s.

你只需要做一个printf，替换?在准备好的SQL字符串上增加一个%s。printf将把所有参数都解释为一个字符串，将每个参数放在每个替换的%s上。

#4

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。

#5

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 mysqli and mysqli_stmt classes 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

This is a (close to) drop-in replacement for you normal mysqli object that returns a custom mysqli_stmt object when you prepare() the query string. After binding your parameters, E_mysqli will allow you to view the resultant query string as a new property of the stmt object:

这是常规mysqli对象的一个(接近)内嵌替换，它在准备查询字符串时返回自定义的mysqli_stmt对象。在绑定参数之后，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 = 'john.doe@example.com'

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项目的自述中对此进行了解释)，但是对于应用程序中麻烦的区域进行故障诊断，或者从过程转换到面向对象的样式，这应该可以为大多数用户提供一定程度的帮助。

As I've outlined in the github project, I don't have any practical experience using the mysqli extension, 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.

免责声明——正如我所说，我延长了期限。

#6

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数据包、提取查询和它的params，这样你就可以看到准备好的语句和它的内容。

#1