php Doctrine - 如何打印出真正的 sql，而不仅仅是准备好的语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2095394/
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
Doctrine - How to print out the real sql, not just the prepared statement?
提问by Rory
We're using Doctrine, a PHP ORM. I am creating a query like this:
我们正在使用 Doctrine，一个 PHP ORM。我正在创建这样的查询：
$q = Doctrine_Query::create()->select('id')->from('MyTable');
and then in the function I'm adding in various where clauses and things as appropriate, like this
然后在函数中，我添加了各种 where 子句和适当的东西，就像这样
$q->where('normalisedname = ? OR name = ?', array($string, $originalString));
Later on, before execute()-ing that query object, I want to print out the raw SQL in order to examine it, and do this:
稍后，在execute()-ing 该查询对象之前，我想打印出原始 SQL 以检查它，并执行以下操作：
$q->getSQLQuery();
However that only prints out the prepared statement, not the full query. I want to see what it is sending to the MySQL, but instead it is printing out a prepared statement, including ?'s. Is there some way to see the 'full' query?
但是，这只打印出准备好的语句，而不是完整的查询。我想看看它向 MySQL 发送了什么，但它打印的是一个准备好的语句，包括?'s. 有什么方法可以查看“完整”查询？
采纳答案by Pascal MARTIN
Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :
Doctrine 没有向数据库服务器发送“真正的 SQL 查询”：它实际上是使用准备好的语句，这意味着：
- Sending the statement, for it to be prepared (this is what is returned by
$query->getSql()) - And, then, sending the parameters (returned by
$query->getParameters()) - and executing the prepared statements
- 发送语句，以便准备好（这是 返回的内容
$query->getSql()） - 然后，发送参数（由 返回
$query->getParameters()） - 并执行准备好的语句
This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.
这意味着 PHP 端永远不会有“真正的”SQL 查询——因此，Doctrine 无法显示它。
回答by Andy.Diaz
A working example:
一个工作示例：
$qb = $this->createQueryBuilder('a');
$query=$qb->getQuery();
// SHOW SQL:
echo $query->getSQL();
// Show Parameters:
echo $query->getParameters();
回答by alex toader
You can check the query executed by your app if you log all the queries in mysql:
如果您在 mysql 中记录所有查询，则可以检查应用程序执行的查询：
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
there will be more queries not only the one that you are looking for but you can grep for it.
将会有更多查询，不仅是您要查找的查询，而且您可以搜索它。
but usually ->getSql();works
但通常->getSql();有效
Edit:
编辑：
to view all the mysql queries I use
查看我使用的所有 mysql 查询
sudo vim /etc/mysql/my.cnf
and add those 2 lines:
并添加这两行：
general_log = on
general_log_file = /tmp/mysql.log
and restart mysql
并重新启动mysql
回答by dsamblas
I have created a Doctrine2 Logger that does exactly this. It "hydrates" the parametrized sql query with the values using Doctrine 2 own data type conversors.
我已经创建了一个 Doctrine2 Logger 来做到这一点。它使用 Doctrine 2 自己的数据类型转换器将参数化的 sql 查询与值“水合”。
<?php
namespace Drsm\Doctrine\DBAL\Logging;
use Doctrine\DBAL\Logging\SQLLogger,
Doctrine\DBAL\Types\Type,
Doctrine\DBAL\Platforms\AbstractPlatform;
/**
* A SQL logger that logs to the standard output and
* subtitutes params to get a ready to execute SQL sentence
* @author [email protected]
*/
class EchoWriteSQLWithoutParamsLogger implements SQLLogger
{
const QUERY_TYPE_SELECT="SELECT";
const QUERY_TYPE_UPDATE="UPDATE";
const QUERY_TYPE_INSERT="INSERT";
const QUERY_TYPE_DELETE="DELETE";
const QUERY_TYPE_CREATE="CREATE";
const QUERY_TYPE_ALTER="ALTER";
private $dbPlatform;
private $loggedQueryTypes;
public function __construct(AbstractPlatform $dbPlatform, array $loggedQueryTypes=array()){
$this->dbPlatform=$dbPlatform;
$this->loggedQueryTypes=$loggedQueryTypes;
}
/**
* {@inheritdoc}
*/
public function startQuery($sql, array $params = null, array $types = null)
{
if($this->isLoggable($sql)){
if(!empty($params)){
foreach ($params as $key=>$param) {
$type=Type::getType($types[$key]);
$value=$type->convertToDatabaseValue($param,$this->dbPlatform);
$sql = join(var_export($value, true), explode('?', $sql, 2));
}
}
echo $sql . " ;".PHP_EOL;
}
}
/**
* {@inheritdoc}
*/
public function stopQuery()
{
}
private function isLoggable($sql){
if (empty($this->loggedQueryTypes)) return true;
foreach($this->loggedQueryTypes as $validType){
if (strpos($sql, $validType) === 0) return true;
}
return false;
}
}
Usage Example:; The following peace of code will echo on standard output any INSERT,UPDATE,DELETE SQL sentences generated with $em Entity Manager,
用法示例：; 以下代码将在标准输出上回显任何使用 $em 实体管理器生成的 INSERT、UPDATE、DELETE SQL 语句，
/**@var \Doctrine\ORM\EntityManager $em */
$em->getConnection()
->getConfiguration()
->setSQLLogger(
new EchoWriteSQLWithoutParamsLogger(
$em->getConnection()->getDatabasePlatform(),
array(
EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_UPDATE,
EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_INSERT,
EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_DELETE
)
)
);
回答by ladenedge
getSqlQuery()does technically show the whole SQL command, but it's a lot more useful when you can see the parameters as well.
getSqlQuery()从技术上讲，它确实显示了整个 SQL 命令，但是当您也可以看到参数时，它会更有用。
echo $q->getSqlQuery();
foreach ($q->getFlattenedParams() as $index => $param)
echo "$index => $param";
To make this pattern more reusable, there's a nice approach described in the commentsat Raw SQL from Doctrine Query Object.
为了使这种模式更可重用，Doctrine Query Object 的 Raw SQL的注释中描述了一种很好的方法。
回答by Ben James
There is no other real query, this is how prepared statements work. The values are bound in the database server, not in the application layer.
没有其他真正的查询，这就是准备好的语句的工作方式。这些值绑定在数据库服务器中，而不是在应用程序层中。
See my answer to this question: In PHP with PDO, how to check the final SQL parametrized query?
请参阅我对这个问题的回答：在带有 PDO 的 PHP 中，如何检查最终的 SQL 参数化查询？
(Repeated here for convenience:)
（为方便起见，在此重复：）
Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL. You create a prepared statement at the database, and then send the parameter values alone.
So what is probably sent to the database will be a
PREPARE ..., thenSET ...and finallyEXECUTE ....You won't be able to get some SQL string like
SELECT * FROM ..., even if it would produce equivalent results, because no such query was ever actually sent to the database.
使用带有参数化值的准备好的语句不仅仅是动态创建 SQL 字符串的另一种方式。您在数据库中创建一个准备好的语句，然后单独发送参数值。
所以可能发送到数据库的将是 a
PREPARE ..., thenSET ...和 finallyEXECUTE ....您将无法获得一些 SQL 字符串
SELECT * FROM ...，即使它会产生等效的结果，因为实际上从未将此类查询发送到数据库。
回答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 moledet
My solution:
我的解决方案：
/**
* Get SQL from query
*
* @author Yosef Kaminskyi
* @param QueryBilderDql $query
* @return int
*/
public function getFullSQL($query)
{
$sql = $query->getSql();
$paramsList = $this->getListParamsByDql($query->getDql());
$paramsArr =$this->getParamsArray($query->getParameters());
$fullSql='';
for($i=0;$i<strlen($sql);$i++){
if($sql[$i]=='?'){
$nameParam=array_shift($paramsList);
if(is_string ($paramsArr[$nameParam])){
$fullSql.= '"'.addslashes($paramsArr[$nameParam]).'"';
}
elseif(is_array($paramsArr[$nameParam])){
$sqlArr='';
foreach ($paramsArr[$nameParam] as $var){
if(!empty($sqlArr))
$sqlArr.=',';
if(is_string($var)){
$sqlArr.='"'.addslashes($var).'"';
}else
$sqlArr.=$var;
}
$fullSql.=$sqlArr;
}elseif(is_object($paramsArr[$nameParam])){
switch(get_class($paramsArr[$nameParam])){
case 'DateTime':
$fullSql.= "'".$paramsArr[$nameParam]->format('Y-m-d H:i:s')."'";
break;
default:
$fullSql.= $paramsArr[$nameParam]->getId();
}
}
else
$fullSql.= $paramsArr[$nameParam];
} else {
$fullSql.=$sql[$i];
}
}
return $fullSql;
}
/**
* Get query params list
*
* @author Yosef Kaminskyi <[email protected]>
* @param Doctrine\ORM\Query\Parameter $paramObj
* @return int
*/
protected function getParamsArray($paramObj)
{
$parameters=array();
foreach ($paramObj as $val){
/* @var $val Doctrine\ORM\Query\Parameter */
$parameters[$val->getName()]=$val->getValue();
}
return $parameters;
}
public function getListParamsByDql($dql)
{
$parsedDql = preg_split("/:/", $dql);
$length = count($parsedDql);
$parmeters = array();
for($i=1;$i<$length;$i++){
if(ctype_alpha($parsedDql[$i][0])){
$param = (preg_split("/[' ' )]/", $parsedDql[$i]));
$parmeters[] = $param[0];
}
}
return $parmeters;}
Example of usage:
用法示例：
$query = $this->_entityRepository->createQueryBuilder('item');
$query->leftJoin('item.receptionUser','users');
$query->where('item.customerid = :customer')->setParameter('customer',$customer)
->andWhere('item.paymentmethod = :paymethod')->setParameter('paymethod',"Bonus");
echo $this->getFullSQL($query->getQuery());
回答by dudapiotr
More clear solution:
更清晰的解决方案：
/**
* Get string query
*
* @param Doctrine_Query $query
* @return string
*/
public function getDqlWithParams(Doctrine_Query $query){
$vals = $query->getFlattenedParams();
$sql = $query->getDql();
$sql = str_replace('?', '%s', $sql);
return vsprintf($sql, $vals);
}
回答by lac_dev
You can use :
您可以使用 ：
$query->getSQL();
If you are using MySQL you can use Workbench to view running SQL statements. You can also use view the running query from mysql by using the following :
如果您使用 MySQL，您可以使用 Workbench 查看正在运行的 SQL 语句。您还可以使用以下命令从 mysql 中查看正在运行的查询：
SHOW FULL PROCESSLIST \G
