php Laravel 4 - 记录 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19131731/
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
Laravel 4 - logging SQL queries
提问by ericbae
There are already several questions in regards to logging the SQL query in Laravel 4. But I've tried almost all of them and it's still not working the way I want.
关于在 Laravel 4 中记录 SQL 查询已经有几个问题了。但是我已经尝试了几乎所有的问题，但它仍然没有按我想要的方式工作。
Here's my situation
这是我的情况
- in my php view file, I make AJAX request to the server
The AJAX request is received and runs a RAW parameterized Postgres SQL query (e.g.
DB::select('select * from my_table where id=?', array(1))
- 在我的 php 视图文件中，我向服务器发出 AJAX 请求
收到 AJAX 请求并运行 RAW 参数化 Postgres SQL 查询（例如
DB::select('select * from my_table where id=?', array(1))
If I use
如果我使用
Event::listen('illuminate.query', function($sql)
{
Log::error($sql);
});
I just get "select * from my_table where id=?" as the log message without the ID value actually populated.
我只是得到“select * from my_table where id=?” 作为没有实际填充 ID 值的日志消息。
If I use
如果我使用
$queries = DB::getQueryLog();
$last_query = end($queries);
Log::error(print_r($last_query, true));
I still don't have the final SQL query with the ID populated.
我仍然没有填充 ID 的最终 SQL 查询。
Finally, if I use a logging tool like https://github.com/loic-sharma/profiler- it doesn't display anything since I'm making an AJAX request.
最后，如果我使用像https://github.com/loic-sharma/profiler这样的日志工具- 它不会显示任何内容，因为我正在发出 AJAX 请求。
Have I exhausted my options? Is there still another better way?
我已经用尽了我的选择吗？还有其他更好的方法吗？
回答by Collin James
Here is what I am currently using for logging of sql queries. You should be able to drop this into your main routes file then add 'log' => true into your database config.
这是我目前用于记录 sql 查询的内容。您应该能够将其放入主路由文件中，然后将 'log' => true 添加到您的数据库配置中。
if (Config::get('database.log', false))
{
Event::listen('illuminate.query', function($query, $bindings, $time, $name)
{
$data = compact('bindings', 'time', 'name');
// Format binding data for sql insertion
foreach ($bindings as $i => $binding)
{
if ($binding instanceof \DateTime)
{
$bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
}
else if (is_string($binding))
{
$bindings[$i] = "'$binding'";
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $query);
$query = vsprintf($query, $bindings);
Log::info($query, $data);
});
}
Thanks to Jeemusu answer for the bit about inserting the bindings into the prepared statement.
感谢 Jeemusu 回答关于将绑定插入到准备好的语句中的一点。
回答by Jeemusu
You should be able to find the bindings by passing $bindingsas the second parameter of the Event function.
您应该能够通过$bindings作为 Event 函数的第二个参数传递来找到绑定。
Event::listen('illuminate.query', function($sql, $bindings, $time){
echo $sql; // select * from my_table where id=?
print_r($bindings); // Array ( [0] => 4 )
echo $time; // 0.58
// To get the full sql query with bindings inserted
$sql = str_replace(array('%', '?'), array('%%', '%s'), $sql);
$full_sql = vsprintf($sql, $bindings);
});
In Laravel 3.x I think the event listener was called laravel.query
在 Laravel 3.x 我认为事件监听器被调用 laravel.query
回答by Marwelln
Continuing on @Collin James answer.
If you want to log to a seperate file only for sql, you can do it with this:
如果你只想为 sql 登录到一个单独的文件，你可以这样做：
if (Config::get('database.log', false)) {
Event::listen('illuminate.query', function($query, $bindings, $time, $name) {
$data = compact('bindings', 'time', 'name');
// Format binding data for sql insertion
foreach ($bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else if (is_string($binding)) {
$bindings[$i] = "'$binding'";
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $query);
$query = vsprintf($query, $bindings);
$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);
});
}
With this at the top of your file:
在您的文件顶部使用它：
use Monolog\Logger;
use Monolog\Handler\StreamHandler;
This will log all your queries to a file named sql-YYYY-mm-dd.login storage/logs/.
这将您所有的疑问记录到一个指定的文件sql-YYYY-mm-dd.log在storage/logs/。
回答by Matteus Hemstr?m
While the accepted answer stands correct, this answer explains how to update loic-sharma profilerwhen making Ajax requests using jQuery. Using this approach one doesn't need to read file logs.
虽然接受的答案是正确的，但此答案解释了在使用 jQuery 发出 Ajax 请求时如何更新loic-sharma 分析器。使用这种方法不需要读取文件日志。
Step 1
第1步
The first problem is to send the updated profiler data to the client on every Ajax-request. This can be solved using the "after" events of the Laravel application.
第一个问题是在每次 Ajax 请求时将更新的分析器数据发送到客户端。这可以使用 Laravel 应用程序的“after”事件来解决。
app/filters.php:
应用程序/过滤器.php：
App::after(function($request, $response)
{
// If it's a JsonResponse and the profiler is enabled, include it in the response.
if($response instanceof \Illuminate\Http\JsonResponse && Profiler::isEnabled()) {
$profiler = Profiler::getFacadeRoot();
$profilerJson = json_encode($profiler->render());
$content = substr($response->getContent(), 0, -1) . ',"profiler":' . $profilerJson . '}';
$response->setContent($content);
}
});
The App::afterfilter will run upon every Laravel request. The first line of the closure above, makes sure that it will only continue if a the response is of type JsonResponse and the profiler is enabled. If that is the case, render the profiler and append the HTML to the JSON object.
该App::after过滤器将在每次Laravel要求运行。上面闭包的第一行确保它仅在响应类型为 JsonResponse 并且启用了探查器时才会继续。如果是这种情况，请呈现分析器并将 HTML 附加到 JSON 对象。
Note: this code assumes that the returned JSON is a object. So it will fail for arrays: Response::json(array(1,2,3)).
注意：此代码假定返回的 JSON 是一个对象。因此对于数组它将失败：Response::json(array(1,2,3)).
Step 2
第2步
Now that the updated profiler HTML is being sent to the client, we must update the DOM with the new profiler HTML using javascript. This should happen every time the client gets a JSON response. jQuery provides global Ajax event handlers, which is perfect to achive this.
现在更新的分析器 HTML 正在发送到客户端，我们必须使用 javascript 用新的分析器 HTML 更新 DOM。每次客户端收到 JSON 响应时都应该发生这种情况。jQuery 提供了全局 Ajax 事件处理程序，非常适合实现这一点。
$(document).ajaxSuccess(function(event, request, settings) {
try {
json = jQuery.parseJSON(request.responseText);
if(json.profiler) {
renderProfiler(json.profiler);
}
} catch(error) {
// Its not JSON.
return;
}
});
Here's a method to replace the old profiler with the new one:
这是一种用新的分析器替换旧的分析器的方法：
renderProfiler = function(data) {
// Remove previous
$anbu = $('.anbu');
$anbu.prev().remove(); // Removes <style> tag of profiler
$anbu.next().next().remove(); // Removes the inline script tag of profiler
$anbu.next().remove(); // Removes jQuery script tag by the profiler
$anbu.remove(); // Removes the <div> tag of profiler
$(document.body).append(data);
};
Using it
使用它
Now it is as simple as returning responses as:
现在它就像返回响应一样简单：
return Response::json(array(
'user' => Auth::user()
));
Laravel will append the profiler HTML. The javascript will catch the JSON response and update the DOM. You will have the SQL queries and timings right on the web page.
Laravel 将附加分析器 HTML。javascript 将捕获 JSON 响应并更新 DOM。您将在网页上拥有 SQL 查询和计时。
Note
笔记
While the code is tested, there might be a bug or two. This is also not exactly how I do it. Instead of sending the HTML in the json response, I extend the object with the actual data from the profiler. On the client side I render the profiler using a mustache template.
在测试代码时，可能会有一两个错误。这也不完全是我的做法。我没有在 json 响应中发送 HTML，而是使用分析器中的实际数据扩展对象。在客户端，我使用mustache 模板呈现分析器。
回答by Mahmoud Zalt
That's what I've been using:
这就是我一直在使用的：
DB::listen(function ($query, $bindings, $time, $connection) {
$fullQuery = vsprintf(str_replace(array('%', '?'), array('%%', '%s'), $query), $bindings);
$result = $connection . ' (' . $time . '): ' . $fullQuery;
dump($result);
// You can of course log the $result
});
回答by Gon?alo Queirós
While the question was originally targeted at Laravel 4, I still ended up here through google, but I'm using Laravel 5.
虽然这个问题最初是针对 Laravel 4 的，但我仍然通过谷歌来到这里，但我使用的是 Laravel 5。
There are new ways to log all queries in Laravel 5 using Middleware, but if you prefer the same approach here is the same code provided by Collin Jamesbut working for Laravel 5
有使用 Middleware 在 Laravel 5 中记录所有查询的新方法，但如果您喜欢相同的方法，这里是Collin James提供的相同代码，但适用于 Laravel 5
if (Config::get('database.log', false))
{
Event::listen('Illuminate\Database\Events\QueryExecuted', function($query)
{
$bindings = $query->bindings;
$time = $query->time;
$name = $query->connection->getName();
$data = compact('bindings', 'time', 'name');
// Format binding data for sql insertion
foreach ($bindings as $i => $binding)
{
if ($binding instanceof \DateTime)
{
$bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
}
else if (is_string($binding))
{
$bindings[$i] = "'$binding'";
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $query->sql);
$query = vsprintf($query, $bindings);
Log::info($query, $data);
});
}
