Laravel 记录SQL日志

Laravel 默认只在sql语法错误时提示完整的sql日志，但实际情况接口慢，筛选条件和预期不符等，都需要看到sql语句，通过sql语句判断问题所在

下面介绍实现方式

第一步

修改 AppServiceProvider.php

vi app/Providers/AppServiceProvider.php

<?php

namespace App\Providers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //https://learnku.com/docs/laravel/9.x/database/12245#97d96c
        $this->registerSqlDebug();
    }

    protected function registerSqlDebug()
    {
        if (config('logging.enable_log_sql', false)) {
            $print = false;
            if ($this->app->environment('local') && env('IS_UNIT')) {
                $print = true;
            }

            DB::listen(function ($query) use ($print) {
                $sql = $query->sql;
                foreach ($query->bindings as $binding) {
                    $value = is_numeric($binding) ? $binding : "'{$binding}'";
                    $sql   = preg_replace('/\?/', (string) $value, $sql, 1);
                }
                $sql              = sprintf('【%s】 %s', $this->format_duration($query->time / 1000), $sql);
                Log::channel('sql')->debug($sql);
                if ($print) {
                    dump($sql);
                }
            });

        }
    }

    private function format_duration($seconds): string
    {
        if ($seconds < 0.001) {
            return round($seconds * 1000000) . 'μs';
        } elseif ($seconds < 1) {
            return round($seconds * 1000, 2) . 'ms';
        }

        return round($seconds, 2) . 's';
    }
}

第二步

修改 config/logging.php

增加sql日志开关

    /**
     * 开启sql日志
     */
    'enable_log_sql' => env('LOG_SQL_ENABLED', true),

日志默认输出到 storage/logs/laravel.log文件，为了区分开，增加以下配置

        'sql'           => [
            'driver' => 'daily',
            'path'   => storage_path('logs/debug/sql.log'),
            'level'  => env('LOG_LEVEL', 'debug'),
            'days'   => 14,
        ],