laravel5日志设置篇(2/3) – 记录sql日志

想要实现的功能

  • 获取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.*

具体实现

  1. 编写日志服务类LogServiceProvider
  2. 在Application中注册LogServiceProvider
  3. 编写sql查询日志输出事件监听器
  4. 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以上。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注