想要实现的功能
- 获取laravel运行时执行的sql语句
- sql相关的日志保存到指定文件（sql.log）
- 接口访问与artisan 命令日志分开保存
例如
- sql.log
[2017-10-21 22:27:42] production.DEBUG: select count(*) as aggregate from `stocks_input` where `stocks_input`.`deleted_at` is null; {"time":0.55}
[2017-10-21 22:27:42] production.DEBUG: select * from `stocks_input` where `stocks_input`.`deleted_at` is null order by `created_at` desc limit 10 offset 0; {"time":0.84}
[2017-10-21 22:27:42] production.DEBUG: select * from `goods` where `goods`.`id` = 1 limit 1; {"time":0.74}
- cli.sql.log
[2017-10-21 22:28:15] production.DEBUG: select * from `goods` where `goods`.`id` = 1 limit 1; {"time":0.76}
[2017-10-21 22:28:15] production.DEBUG: select * from `regions` where `regions`.`id` = 110102 limit 1; {"time":0.87}
[2017-10-21 22:28:15] production.DEBUG: select * from `regions` where `regions`.`id` = 110000 limit 1; {"time":0.81}
环境
- php 7.0.22
- laravel 5.2.*
具体实现
- 编写日志服务类
LogServiceProvider - 在Application中注册
LogServiceProvider - 编写sql查询日志输出事件监听器
- sql查询日志输出事件注册
LogServiceProvider类编写
<?php
namespace App\Providers;
use Carbon\Carbon;
use Illuminate\Support\ServiceProvider;
use Illuminate\Log\Writer;
use Monolog\Logger as Monolog;
/**
* 日志记录器服务
*
* @package app.Providers
*/
class LogServiceProvider extends ServiceProvider
{
/**
* Register the service provider.
*
* @return void
*/
public function register()
{
$this->app->singleton('log', function () {
return $this->createAppLogger();
});
$this->app->singleton('sql-log', function () {
return $this->createSqlLogger();
});
}
/**
* Create the app logger.
*
* @return \Illuminate\Log\Writer
*/
public function createAppLogger()
{
$log = new Writer(
new Monolog($this->channel()), $this->app['events']
);
$this->configureHandler($log, 'app');
return $log;
}
/**
* Create the sql logger.
*
* @return \Illuminate\Log\Writer
*/
public function createSqlLogger()
{
$log = new Writer(
new Monolog($this->channel()), $this->app['events']
);
$this->configureHandler($log, 'sql');
return $log;
}
/**
* Get the name of the log "channel".
*
* @return string
*/
protected function channel()
{
return $this->app->bound('env') ? $this->app->environment() : 'production';
}
/**
* Configure the Monolog handlers for the application.
*
* @param \Illuminate\Log\Writer $log
* @param string $base_name
* @return void
*/
protected function configureHandler(Writer $log, $base_name)
{
$this->{'configure' . ucfirst($this->handler()) . 'Handler'}($log, $base_name);
}
/**
* Configure the Monolog handlers for the application.
*
* @param \Illuminate\Log\Writer $log
* @param string $base_name
* @return void
*/
protected function configureSingleHandler(Writer $log, $base_name)
{
$log->useFiles(
sprintf('%s/logs/%s%s.log', $this->app->storagePath(), $this->getFilePrefix(), $base_name),
$this->logLevel()
);
}
/**
* Configure the Monolog handlers for the application.
*
* @param \Illuminate\Log\Writer $log
* @param string $base_name
* @return void
*/
protected function configureDailyHandler(Writer $log, $base_name)
{
$log->useDailyFiles(
sprintf('%s/logs/%s%s.log.%s', $this->app->storagePath(), $this->getFilePrefix(), $base_name, Carbon::now()->format('Ymd')),
$this->maxFiles(),
$this->logLevel()
);
}
/**
* Configure the Monolog handlers for the application.
*
* @param \Illuminate\Log\Writer $log
* @param string $base_name
* @return void
*/
protected function configureSyslogHandler(Writer $log, $base_name)
{
$log->useSyslog($base_name, $this->logLevel());
}
/**
* Configure the Monolog handlers for the application.
*
* @param \Illuminate\Log\Writer $log
* @param string $base_name
* @return void
*/
protected function configureErrorlogHandler(Writer $log, $base_name)
{
$log->useErrorLog($this->logLevel());
}
/**
* Get the default log handler.
*
* @return string
*/
protected function handler()
{
if ($this->app->bound('config')) {
return $this->app->make('config')->get('app.log', 'single');
}
return 'single';
}
/**
* Get the log level for the application.
*
* @return string
*/
protected function logLevel()
{
if ($this->app->bound('config')) {
return $this->app->make('config')->get('app.log_level', 'debug');
}
return 'debug';
}
/**
* Get the maximum number of log files for the application.
*
* @return int
*/
protected function maxFiles()
{
if ($this->app->bound('config')) {
return $this->app->make('config')->get('app.log_max_files', 5);
}
return 0;
}
/**
* 获取文件前缀
*
* @return string
*/
private function getFilePrefix()
{
return php_sapi_name() == 'cli' ? 'cli_' : '';
}
}
注册LogServiceProvider (bootstrap/app.php)
//追加下面一行
$app->register(new \App\Providers\LogServiceProvider($app));
sql查询日志输出事件监听器
<?php
namespace App\Listeners;
use Illuminate\Database\Events\QueryExecuted;
use DateTime;
use DB;
/**
* 查询日志输出
*
* @package app.Listeners
*/
class QueryLogTracker
{
/**
* Handle the event.
*
* @param QueryExecuted $event
* @internal param $query
* @internal param $bindings
* @internal param $time
*/
public function handle(QueryExecuted $event)
{
$time = $event->time;
$bindings = $event->bindings;
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] = DB::getPdo()->quote($binding);
} else if (null === $binding) {
$bindings[$i] = 'null';
}
}
$query = str_replace(array('%', '?', "\r", "\n", "\t"), array('%%', '%s', ' ', ' ', ' '), $event->sql);
$query = preg_replace('/\s+/uD', ' ', $query);
$query = vsprintf($query, $bindings) . ';';
app('sql-log')->debug($query, compact('time'));
}
}
注册查询日志输出事件 (app\Providers\EventServiceProvider)
protected $listen = [
...
'Illuminate\Database\Events\QueryExecuted' => [
'App\Listeners\QueryLogTracker',
],
];
PS. 由于Laravel5.2版本以上才有Illuminate\Database\Events\QueryExecuted类，故想实现本实验的朋友，请将Laravel的版本升级到5.2以上。