SQL 如何在执行查询之前从 Laravel 的查询构建器获取原始查询字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20045732/
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 can I get the raw query string from Laravel's query builder BEFORE executing the query?
提问by Matteo Riva
I have a complex query created by a few conditions, and I would like to get the final SQL query from the builder object is about to execute. Can I do that?
我有一个由几个条件创建的复杂查询，我想从即将执行的构建器对象中获取最终的 SQL 查询。我可以这样做吗？
回答by Antonio Carlos Ribeiro
You can get it doing:
你可以这样做：
$query = DB::table('brands')
->join('products','a','=','c')
->whereNull('whatever');
echo $query->toSql();
But Laravel will not show you parameters in your query, because they are bound after preparation of the query.
但是 Laravel 不会在您的查询中显示参数，因为它们是在准备好查询之后绑定的。
So you can also do:
所以你也可以这样做：
print_r( $query->getBindings() );
回答by andi79h
For debugging this might come quite handy as it returns the SQL with the bindings, so you can instantly put it into the database console.
对于调试，这可能会非常方便，因为它返回带有绑定的 SQL，因此您可以立即将其放入数据库控制台。
/**
* Combines SQL and its bindings
*
* @param \Eloquent $query
* @return string
*/
public static function getEloquentSqlWithBindings($query)
{
return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
return is_numeric($binding) ? $binding : "'{$binding}'";
})->toArray());
}
Found this snip at https://gist.github.com/thonyx/c061d56dc620284ab22526294b43518a, so all credits to https://gist.github.com/thonyx:)
在https://gist.github.com/thonyx/c061d56dc620284ab22526294b43518a 上找到了这个片段，所以所有功劳都归功于https://gist.github.com/thonyx:)
回答by epicgear
Piggy back off andi79h's answer. The function works well, except it's assuming binding would not have any quotes that could break the query.
小猪退出andi79h的回答。该函数运行良好，除非它假设绑定不会有任何可能破坏查询的引号。
I added "addslashes" to $bindings to make it a bit safer. Although, ideally, it should be run through mysqli_real_escape_string() if you have an active connection to work with. https://www.php.net/manual/en/mysqli.real-escape-string.php
我在 $bindings 中添加了“addslashes”以使其更安全。虽然，理想情况下，如果您有活动连接可以使用，它应该通过 mysqli_real_escape_string() 运行。 https://www.php.net/manual/en/mysqli.real-escape-string.php
/**
* Combines SQL and its bindings
*
* @param \Eloquent $query
* @return string
*/
public static function getEloquentSqlWithBindings($query)
{
return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
$binding = addslashes($binding);
return is_numeric($binding) ? $binding : "'{$binding}'";
})->toArray());
}
回答by Felix Eve
I wanted line breaks and uppercase mysql keywords in my queries so added to andi79h's answer. Not particuarly elegant and I'm sure there are lots of edge cases it won't work with but I still find it useful...
我想在我的查询中使用换行符和大写 mysql 关键字，因此添加到 andi79h 的答案中。不是特别优雅，我确定有很多边缘情况它无法使用，但我仍然觉得它很有用......
function debug_query($query) {
$query = vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
return is_numeric($binding) ? $binding : "'{$binding}'";
})->toArray());
$double_linebreak_words = ['(', ')'];
$double_linebreak_words_replace = array_map(function($str){ return PHP_EOL . $str . PHP_EOL; }, $double_linebreak_words);
$query = str_replace($double_linebreak_words, $double_linebreak_words_replace, $query);
$mysql_keywords = ['ADD', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'AS', 'ASC', 'AUTO_INCREMENT', 'BDB', 'BERKELEYDB', 'BETWEEN', 'BIGINT', 'BINARY', 'BLOB', 'BOTH', 'BTREE', 'BY', 'CASCADE', 'CASE', 'CHANGE', 'CHAR', 'CHARACTER', 'CHECK', 'COLLATE', 'COLUMN', 'COLUMNS', 'CONSTRAINT', 'CREATE', 'CROSS', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'DEC', 'DECIMAL', 'DEFAULT', 'DELAYED', 'DELETE', 'DESC', 'DESCRIBE', 'DISTINCT', 'DISTINCTROW', 'DIV', 'DOUBLE', 'DROP', 'ELSE', 'ENCLOSED', 'ERRORS', 'ESCAPED', 'EXISTS', 'EXPLAIN', 'FALSE', 'FIELDS', 'FLOAT', 'FOR', 'FORCE', 'FOREIGN', 'FROM', 'FULLTEXT', 'FUNCTION', 'GEOMETRY', 'GRANT', 'GROUP', 'HASH', 'HAVING', 'HELP', 'HIGH_PRIORITY', 'HOUR_MINUTE', 'HOUR_SECOND', 'IF', 'IGNORE', 'INDEX', 'INFILE', 'INNER', 'INNODB', 'INSERT', 'INTEGER', 'INTERVAL', 'INTO', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LEADING', 'LEFT', 'LIKE', 'LIMIT', 'LINES', 'LOAD', 'LOCALTIME', 'LOCALTIMESTAMP', 'LOCK', 'LONG', 'LONGBLOB', 'LONGTEXT', 'LOW_PRIORITY', 'MASTER_SERVER_ID', 'MATCH', 'MEDIUMBLOB', 'MEDIUMINT', 'MEDIUMTEXT', 'MIDDLEINT', 'MINUTE_SECOND', 'MOD', 'MRG_MYISAM', 'NATURAL', 'NOT', 'NULL', 'NUMERIC', 'ON', 'OPTIMIZE', 'OPTION', 'OPTIONALLY', 'ORDER', 'OUTER', 'OUTFILE', 'PRECISION', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE', 'PURGE', 'READ', 'REAL', 'REFERENCES', 'REGEXP', 'RENAME', 'REPLACE', 'REQUIRE', 'RESTRICT', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'RTREE', 'SELECT', 'SET', 'SHOW', 'SMALLINT', 'SOME', 'SONAME', 'SPATIAL', 'SQL_BIG_RESULT', 'SQL_CALC_FOUND_ROWS', 'SQL_SMALL_RESULT', 'SSL', 'STARTING', 'STRAIGHT_JOIN', 'STRIPED', 'TABLE', 'TABLES', 'TERMINATED', 'THEN', 'TINYBLOB', 'TINYINT', 'TINYTEXT', 'TO', 'TRAILING', 'TRUE', 'TYPES', 'UNION', 'UNIQUE', 'UNLOCK', 'UNSIGNED', 'UPDATE', 'USAGE', 'USE', 'USER_RESOURCES', 'USING', 'VALUES', 'VARBINARY', 'VARCHAR', 'VARCHARACTER', 'VARYING', 'WARNINGS', 'WHEN', 'WHERE', 'WITH', 'WRITE', 'XOR', 'YEAR_MONTH', 'ZEROFILL', 'INT', 'OR', 'IS', 'IN'];
$mysql_keywords = array_map(function($str){ return " $str "; }, $mysql_keywords);
$mysql_keywords_lc = array_map(function($str){ return strtolower($str); }, $mysql_keywords);
$query = str_replace($mysql_keywords_lc, $mysql_keywords, $query);
$linebreak_before_words = ['INNER JOIN', 'LEFT JOIN', 'OUTER JOIN', 'RIGHT JOIN', 'WHERE', 'FROM', 'GROUP BY', 'SELECT'];
$linebreak_before_words_replace = array_map(function($str){ return PHP_EOL . $str; }, $linebreak_before_words);
$query = str_replace($linebreak_before_words, $linebreak_before_words_replace, $query);
$linebreak_after_words = [','];
$linebreak_after_words_replace = array_map(function($str){ return $str . PHP_EOL; }, $linebreak_after_words);
$query = str_replace($linebreak_after_words, $linebreak_after_words_replace, $query);
$query = str_replace('select ', 'SELECT ', $query);
return $query;
}
