导出“查询"来自"mysqli->准备"； [英] Export "query" from "mysqli->prepare"

问题描述

是否可以导出以mysqli::prepare和::bind_param格式设置的查询?

Is it possible to export the query formatted by mysqli::prepare and ::bind_param?

示例:

<?php
$mysqli = new mysqli('host', 'user', 'pass', 'table');
if(mysqli_connect_errno()){
    printf('Connect failed: %s\n', mysqli_connect_error());
    exit;
}

$data=7290;

if ($stmt = $mysqli->prepare('SELECT `id`,`info` FROM `propertys` WHERE id>?')){
    $stmt->bind_param('i',$data);
    $stmt->execute();
    $stmt->bind_result($id,$info);
    while($q=$stmt->fetch()){
        echo $id,': ',$info,'<br>';
    }
    $stmt->close();
}
$mysqli->close();
?>

我想导出由mysql::prepare和bind_param执行的QUERY函数 因此(这是一个虚构的示例):

I would like to export the QUERY functions performed by mysql::prepare and bind_param so (this is an imaginary example):

if ($stmt = $mysqli->prepare('SELECT `id`,`info` FROM `propertys` WHERE id>?')){
    $stmt->bind_param('i',$data);
    $stmt->execute();
    echo $stmt->exportQuery();//Function does not exist, just for example

函数::exportQuery将如下所示:

SELECT `id`,`info` FROM `propertys` WHERE id>7290

有什么解决办法吗?

谢谢.

推荐答案

我知道这对于调试很有用，但它不是准备好的语句的工作方式.参数不会与客户端上的准备好的语句组合在一起. PHP永远都不能访问查询字符串及其参数.

I know 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. PHP should never have access to the query string combined with its parameters.

当您执行prepare()时，SQL语句将发送到数据库服务器，而当您执行execute()时，将分别发送参数. MySQL的常规查询日志的确显示了最终SQL，其中包含在execute()之后插入的值.以下是我的一般查询日志的摘录.我从mysql CLI而不是从PHP运行查询，但是原理是相同的.

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 PHP, 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

---

发表您的评论

---

Re your comment:

@Baily是正确的，MySQL没有客户端解决方案返回带有插值参数的完整查询.这不是PHP的错.

@Baily is correct, MySQL has no client-side solution to return the full query with parameters interpolated. It's not the fault of PHP.

要启用我上面提到的日志记录，请在MySQL客户端中或通过API通过PHP提交此命令:

To enable the logging that I mention above, use this command, either in the MySQL client or submitted from PHP via an API:

SET GLOBAL general_log = ON;

收集完信息后，您应该关闭日志，因为记录每个查询确实会花费一些开销.

You should turn off the log when you're done collecting information, because it does cost some overhead to be logging every query.

SET GLOBAL general_log = OFF;

PS:动态更改日志记录设置需要MySQL 5.1或更高版本.在早期版本中，更改日志记录时必须重新启动mysqld.

PS: Changing the logging settings dynamically requires MySQL 5.1 or later. In earlier versions, you have to restart mysqld when you change logging.

这篇关于导出“查询"来自"mysqli->准备"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！