MySQL 如何查看查询中的参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11226103/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I view the parameters in a query?
提问by guyaloni
In order to debug my code I would like to see the explicit sql query that is executed.
为了调试我的代码，我想查看执行的显式 sql 查询。
I create the query with createQueryBuilder, and the most explicit thing I achieved is having the raw query using:
我用 来创建查询createQueryBuilder，我实现的最明确的事情是使用原始查询：
$qb->getQuery()->getSQL();
The problem is that instead of parameters I see the holders (?).
I found some solutions on the web but they are for 1.3 and 1.4, nothing for Symfony-2.
问题是，我看到的不是参数，而是持有者 ( ?)。我在网上找到了一些解决方案，但它们适用于 1.3 和 1.4，而不适用于 Symfony-2。
Ideas? Thanks!
想法？谢谢！
回答by AdrienBrault
You can access the parameters used by the placeholders using $query->getParameters(), so you could debug your query using:
您可以使用 访问占位符使用的参数$query->getParameters()，因此您可以使用以下方法调试查询：
$query = $qb->getQuery();
print_r(array(
'sql' => $query->getSQL(),
'parameters' => $query->getParameters(),
));
回答by Anjana Silva
You can easily access the SQL parameters using the following approach.
您可以使用以下方法轻松访问 SQL 参数。
$result = $qb->getQuery()->getSQL();
$param_values = '';
$col_names = '';
foreach ($result->getParameters() as $index => $param){
$param_values .= $param->getValue().',';
$col_names .= $param->getName().',';
}
//echo rtrim($param_values,',');
//echo rtrim($col_names,',');
So if you printed out the $param_valuesand $col_names, you can get the parameter values passing through the sql and respective column names.
因此，如果您打印出$param_valuesand $col_names，您可以获得通过 sql 和相应列名传递的参数值。
Note : If $paramreturns an array, you need to re iterate, as parameters inside IN (:?)usually comes is as a nested array.
注意：如果$param返回一个数组，则需要重新迭代，因为里面的参数IN (:?)通常是作为嵌套数组来的。
Meantime if you found another approach, please be kind enough to share with us :)
同时，如果您找到了另一种方法，请与我们分享:)
Thank you!
谢谢！
回答by Noman
I've also been looking for a way to get parameter-injected SQL from a DQL query to aid in debugging by allowing me to output an SQL string that I can directly paste into phpmyadmin, for instance, and add explain to it, etc.
我还一直在寻找一种从 DQL 查询中获取参数注入 SQL 的方法，以通过允许我输出一个可以直接粘贴到 phpmyadmin 中的 SQL 字符串来帮助调试，例如，并向其中添加解释等。
Anyway I based my answer on Néo's answer which, since I wasn't able to get it to work due to private method calls, I adapted by creating a function inside Doctrine\ORM\Query, as per below:
无论如何，我的答案基于 Néo 的答案，由于私有方法调用我无法让它工作，我通过在 Doctrine\ORM\Query 中创建一个函数来进行调整，如下所示：
/**
* Execute query and return the SQL with params injected.
*
* @return string
* @throws QueryException
*/
public function executeAndGetSqlWithParams(): string
{
// Execute the query to get the parser result.
$this->execute();
// Declare the SQL for use in the vsprintf function.
$sql = str_replace('?', '%s', $this->getSQL());
// Declare the SQL parameter mappings.
$parameterMappings = $this->processParameterMappings($this->_parserResult->getParameterMappings());
/**
* TODO: Possibly replace each question mark by the correct vsprintf argument using $parameterMappings[1].
*
* Right now all parameters are treated as strings.
*/
// Declare and define the SQL parameters.
$sqlParameters = [];
foreach ($parameterMappings[0] as $parameter)
{
if (is_array($parameter))
{
$sqlParameters[] = implode(',', $parameter);
}
else
{
$sqlParameters[] = $parameter;
}
}
// Return the SQL with its parameters injected.
return vsprintf($sql, $sqlParameters);
}
As its name implies, it executes the query in order to get the parameter mappings from the parser result, and then uses that along with vsprintf to replace the parameters with their values.
顾名思义，它执行查询以从解析器结果中获取参数映射，然后将其与 vsprintf 一起使用以将参数替换为其值。
This is of course a hack of the core code, and as I'm not familiar with contributing to public projects, if anyone who does wants to try and get it included there, feel free to copy it.
这当然是对核心代码的一种破解，而且由于我不熟悉为公共项目做出贡献，如果有人想尝试将其包含在其中，请随时复制它。
回答by HLGEM
I would use Profiler in SQL Server to get what is being sent to the database. Apparently there are some similar tools for mySQL. Is there a Profiler equivalent for MySql?
我会在 SQL Server 中使用 Profiler 来获取发送到数据库的内容。显然，mySQL 有一些类似的工具。是否有与 MySql 等效的 Profiler？
回答by Néo
I had to build a requete union (impossible with DQL or QueryBuilder) with 5 query already built with the QueryBuilder. So I reuse these queries but I had a problem using getParameters() function because it give the parameter in same order you have given it. One of the advantages when you use the query builder is you can create a query in order yhou want but when you retrieve parameters, you may retrieve it in messy. to avoid this i have built the following function:
我必须使用 QueryBuilder 已经构建了 5 个查询来构建一个请求联合（使用 DQL 或 QueryBuilder 不可能）。所以我重用了这些查询，但是我在使用 getParameters() 函数时遇到了问题，因为它以您给出的相同顺序给出参数。使用查询构建器的优势之一是，您可以按照需要的顺序创建查询，但是当您检索参数时，您可能会很混乱地检索它。为了避免这种情况，我构建了以下功能：
$getSqlWithParams = \Closure::bind(function(){
return [$this->getSql(), $this->processParameterMappings($this->_parserResult->getParameterMappings())];
}, null, Query::class);
now when you want retrieve sql and the sorted parameters you do :
现在，当您要检索 sql 和排序的参数时，请执行以下操作：
$getSqlWithParams()->call($query)
Don't forget use \Doctrine\ORM\Query statement. And voilà!
不要忘记使用 \Doctrine\ORM\Query 语句。瞧！
