21. 监听查询事件，打印 Query Log

ogenes

于 2023-06-04 08:25:10 发布

阅读量460

点赞数

分类专栏： Laravel-Element-Admin 文章标签： php 数学建模开发语言

本文链接：https://blog.csdn.net/weixin_39857866/article/details/130844969

版权

Laravel-Element-Admin 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

监听查询事件，打印query log

在开发环境，打印出每次请求的SQL，方便做sql分析优化。

首先新增一个log channel 单独保存 query log，这里就叫做 query

vim config/logging.php

'channels' => [
        ……………………

        'daily' => [
            'driver' => 'daily',
            'path' => env('LOG_PATH', storage_path('logs/')) . 'laravel.log',
            'level' => env('LOG_LEVEL', 'debug'),
            'days' => env('LOG_MAX_FILES', 14),
        ],
    
        'query' => [
            'driver' => 'daily',
            'path' => env('LOG_PATH', storage_path('logs/')) . 'query.log',
            'level' => env('LOG_LEVEL', 'debug'),
            'days' => env('LOG_MAX_FILES', 3),
        ],

        ………………
    ],

在 app/Providers/AppServiceProvider.php:boot()中增加代码

public function boot()
{
  //仅在 local 环境生效（取值于.env文件中的APP_ENV）。
  if (app()->environment('local')) {
    
    //加入这一行代码是为了配合自定义函数 getLastSql() 简化打印最后一条sql，当然也可以直接查看sql日志。
    DB::connection()->enableQueryLog();
    // DB::connection('mysql_other')->enableQueryLog();//如果有多个库，需要执行多次
    
    //自动记录sql日志
    DB::listen(static function ($query) {
      $sql = str_replace(['%', '?'], ['%%', '%s'], $query->sql);
      if ($query->bindings) {
        $sql = sprintf($sql, ...array_map(static function ($val) {
          return is_string($val) ? "'{$val}'" : $val;
        }, $query->bindings));
        #这里指定 channel 为 query
        $log = "ConnectionName: {$query->connectionName}; Use Time {$query->time}; SQL:";
        $log .= PHP_EOL . $sql;
        Log::channel('query')->info($log);
      }
    });
  }
}

然后测试，要确保 php的用户对 env(‘LOG_PATH’, storage_path(‘logs/’)) 有写入权限

[2022-09-05 07:24:07] local.INFO: ConnectionName: mysql; Use Time 14.86; SQL:
select * from `user` where `account` = 'admin' limit 1  
[2022-09-05 07:24:19] local.INFO: ConnectionName: mysql; Use Time 6.65; SQL:
select * from `user` where `id` = '1' limit 1  
[2022-09-05 07:24:20] local.INFO: ConnectionName: mysql; Use Time 0.73; SQL:
select * from `user` where `id` = '1' limit 1  
[2022-09-05 07:24:20] local.INFO: ConnectionName: mysql; Use Time 3.49; SQL:
select * from `user` where `id` = 3 and `username` = '张三子' and `account` = 'zhangsan' and `mobile` = '' and `email` = ''  
[2022-09-05 07:24:25] local.INFO: ConnectionName: mysql; Use Time 7; SQL:
select * from `user` where `id` = '1' limit 1  
[2022-09-05 07:24:25] local.INFO: ConnectionName: mysql; Use Time 0.89; SQL:
select * from `user` where `id` = '1' limit 1

自定义函数打印最后一条sql

if (!function_exists('getLastSql')) {
    
    /**
     * 调试方法：打印最后一条SQL
     * @param string $connection
     * @return string
     */
    function getLastSql(string $connection = ''): string
    {
        $queryLogs = DB::connection($connection)->getQueryLog();
        $query = $queryLogs ? array_pop($queryLogs) : [];
        $sql = $query ? str_replace(['%', '?'], ['%%', '%s'], $query['query']) : '';
        if (!empty($query['bindings'])) {
            $sql = sprintf($sql, ...array_map(static function ($val) {
                return is_string($val) ? "'{$val}'" : $val;
            }, $query['bindings']));
        }
        return $sql;
    }   
}