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

监听查询事件， 打印query log

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

1. 首先新增一个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),
           ],
   
           ………………
       ],
   

2. 在 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);
         }
       });
     }
   }
   

3. 然后测试，要确保 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  
   

4. 自定义函数打印最后一条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;
       }   
   }