php 如何在 Laravel 5 中执行查询？DB::getQueryLog() 返回空数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27753868/
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 to Get the Query Executed in Laravel 5? DB::getQueryLog() Returning Empty Array
提问by Arsen
I'm trying to view the log for a query, but DB::getQueryLog()is just returning an empty array:
我正在尝试查看查询的日志，但DB::getQueryLog()只是返回一个空数组：
$user = User::find(5);
print_r(DB::getQueryLog());
Result
结果
Array
(
)
How can I view the log for this query?
如何查看此查询的日志？
回答by Marty Aghajanyan
By default, the query log is disabled in Laravel 5: https://github.com/laravel/framework/commit/e0abfe5c49d225567cb4dfd56df9ef05cc297448
默认情况下，Laravel 5 中禁用查询日志：https: //github.com/laravel/framework/commit/e0abfe5c49d225567cb4dfd56df9ef05cc297448
You will need to enable the query log by calling:
您将需要通过调用启用查询日志：
DB::enableQueryLog();
or register an event listener:
或者注册一个事件监听器：
DB::listen(
function ($sql, $bindings, $time) {
// $sql - select * from `ncv_users` where `ncv_users`.`id` = ? limit 1
// $bindings - [5]
// $time(in milliseconds) - 0.38
}
);
Some Tips
一些技巧
1. Multiple DB connections
1. 多个数据库连接
If you have more than one DB connection you must specify which connection to log
如果您有多个数据库连接，则必须指定要记录的连接
To enables query log for my_connection:
启用查询日志my_connection：
DB::connection('my_connection')->enableQueryLog();
To get query log for my_connection:
获取查询日志my_connection：
print_r(
DB::connection('my_connection')->getQueryLog()
);
2. Where to enable query log ?
2. 在哪里启用查询日志？
For an HTTP request lifecycle, you can enable query log in the handlemethod of some BeforeAnyDbQueryMiddlewaremiddlewareand then retrieve the executed queries in the terminatemethod of the same middleware.
对于HTTP请求生命周期，可以在handle某个BeforeAnyDbQueryMiddleware中间件的方法中开启查询日志，然后terminate在同一个中间件的方法中检索执行的查询。
class BeforeAnyDbQueryMiddleware
{
public function handle($request, Closure $next)
{
DB::enableQueryLog();
return $next($request);
}
public function terminate($request, $response)
{
// Store or dump the log data...
dd(
DB::getQueryLog()
);
}
}
A middleware's chain will not run for artisan commands, so for CLI execution you can enable query log in the artisan.startevent listener.
中间件的链不会针对工匠命令运行，因此对于 CLI 执行，您可以在artisan.start事件侦听器中启用查询日志。
For example you can put it in the bootstrap/app.phpfile
例如你可以把它放在bootstrap/app.php文件中
$app['events']->listen('artisan.start', function(){
\DB::enableQueryLog();
});
3. Memory
3. 记忆
Laravel keeps all queries in memory. So in some cases, such as when inserting a large number of rows, or having a long running job with a lot of queries, this can cause the application to use excess memory.
Laravel 将所有查询保存在内存中。因此，在某些情况下，例如插入大量行时，或长时间运行包含大量查询的作业时，这可能会导致应用程序使用过多内存。
In most cases you will need the query log only for debugging, and if that is the case I would recommend you enable it only for development.
在大多数情况下，您只需要在调试时使用查询日志，如果是这种情况，我建议您仅在开发时启用它。
if (App::environment('local')) {
// The environment is local
DB::enableQueryLog();
}
References
参考
回答by Skeets
If all you really care about is the actual query (the last one run) for quick debugging purposes:
如果您真正关心的是用于快速调试的实际查询（最后一次运行）：
DB::enableQueryLog();
# your laravel query builder goes here
$laQuery = DB::getQueryLog();
$lcWhatYouWant = $laQuery[0]['query']; # <-------
# optionally disable the query log:
DB::disableQueryLog();
do a print_r()on $laQuery[0]to get the full query, including the bindings. (the $lcWhatYouWantvariable above will have the variables replaced with ??)
做一个print_r()上$laQuery[0]得到充分的查询，包括绑定。（$lcWhatYouWant上面的变量会将变量替换为??）
If you're using something other than the main mysql connection, you'll need to use these instead:
如果您使用的不是主 mysql 连接，则需要使用这些连接：
DB::connection("mysql2")->enableQueryLog();
DB::connection("mysql2")->getQueryLog();
(with your connection name where "mysql2" is)
（使用“mysql2”所在的连接名称）
回答by Rubén Ruíz
Put this on routes.php file:
把它放在 routes.php 文件中：
\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
echo'<pre>';
var_dump($query->sql);
var_dump($query->bindings);
var_dump($query->time);
echo'</pre>';
});
Submitted by msurguy, source code in this page. You will find this fix-code for laravel 5.2 in comments.
通过msurguy，源代码在提交该页面。你会在评论中找到这个 laravel 5.2 的修复代码。
回答by Vineet Garg
You need to first enable query logging
您需要先启用查询日志记录
DB::enableQueryLog();
Then you can get query logs by simply:
然后您可以通过简单的方式获取查询日志：
dd(DB::getQueryLog());
It would be better if you enable query logging before application starts, which you can do in a BeforeMiddleware and then retrieve the executed queries in AfterMiddleware.
如果您在应用程序启动之前启用查询日志记录会更好，您可以在 BeforeMiddleware 中执行此操作，然后在 AfterMiddleware 中检索已执行的查询。
回答by Luís Cruz
Apparently with Laravel 5.2, the closure in DB::listenonly receives a single parameter.
显然在 Laravel 5.2 中，闭包DB::listen只接收一个参数。
So, if you want to use DB::listenin Laravel 5.2, you should do something like:
所以，如果你想DB::listen在 Laravel 5.2 中使用，你应该这样做：
DB::listen(
function ($sql) {
// $sql is an object with the properties:
// sql: The query
// bindings: the sql query variables
// time: The execution time for the query
// connectionName: The name of the connection
// To save the executed queries to file:
// Process the sql and the bindings:
foreach ($sql->bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else {
if (is_string($binding)) {
$sql->bindings[$i] = "'$binding'";
}
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);
$query = vsprintf($query, $sql->bindings);
// Save the query to file
$logFile = fopen(
storage_path('logs' . DIRECTORY_SEPARATOR . date('Y-m-d') . '_query.log'),
'a+'
);
fwrite($logFile, date('Y-m-d H:i:s') . ': ' . $query . PHP_EOL);
fclose($logFile);
}
);
回答by larp
For laravel 5.8you just add ddor dump.
对于laravel 5.8，您只需添加dd或dump。
Ex:
前任：
DB::table('users')->where('votes', '>', 100)->dd();
or
或者
DB::table('users')->where('votes', '>', 100)->dump();
回答by doncadavona
Use toSql()instead of get()like so:
使用toSql()而不是get()这样：
$users = User::orderBy('name', 'asc')->toSql();
echo $users;
// Outputs the string:
'select * from `users` order by `name` asc'
回答by ChrisH
(Laravel 5.2) I find the simplest way is just to add one code line to monitor the sql queries:
(Laravel 5.2) 我发现最简单的方法就是添加一行代码来监控 sql 查询：
\DB::listen(function($sql) {var_dump($sql); });
回答by Saint Father
In continuing of the Apparently with Laravel 5.2, the closure in DB::listen only receives a single parameter...response above : you can put this code into the Middleware script and use it in the routes.
继续使用 Laravel 5.2，DB::listen 中的闭包只接收一个参数......上面的响应：您可以将此代码放入中间件脚本并在路由中使用它。
Additionally:
此外：
use Monolog\Logger;
use Monolog\Handler\StreamHandler;
$log = new Logger('sql');
$log->pushHandler(new StreamHandler(storage_path().'/logs/sql-' . date('Y-m-d') . '.log', Logger::INFO));
// add records to the log
$log->addInfo($query, $data);
回答by ch271828n
This code is for:
此代码用于：
- Laravel 5.2
- Log the statements into the mysql database
- Laravel 5.2
- 将语句记录到 mysql 数据库中
Here is the code, which is based on @milz 's answer:
这是基于@milz 的回答的代码：
DB::listen(function($sql) {
$LOG_TABLE_NAME = 'log';
foreach ($sql->bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else {
if (is_string($binding)) {
$sql->bindings[$i] = "'$binding'";
}
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);
$query = vsprintf($query, $sql->bindings);
if(stripos($query, 'insert into `'.$LOG_TABLE_NAME.'`')===false){
$toLog = new LogModel();
$toLog->uId = 100;
$toLog->sql = $query;
$toLog->save();
}
});
The core is the if(stripos...line, which prevents the recursion of inserting the insert into logsql statement into database.
核心是if(stripos...行，防止递归插入insert into logsql语句到数据库中。
