Lumen 8 DB:listen 重复记录 sql 日志

[wilbur-yu]

QueryLoggerServiceProvider.php

DB::listen(function (QueryExecuted $query) {
	if ($query->time < $this->app['config']->get('logging.query.slower_than', 0)) {
		return;
	}
	
	$sqlWithPlaceholders = str_replace(['%', '?'], ['%%', '%s'], $query->sql);
	
	$bindings = $query->connection->prepareBindings($query->bindings);
	$pdo      = $query->connection->getPdo();
	$realSql  = $sqlWithPlaceholders;
	$duration = $this->formatDuration($query->time / 1000);
	
	if (count($bindings) > 0) {
		$realSql = vsprintf($sqlWithPlaceholders, array_map([$pdo, 'quote'], $bindings));
	}
	
	Log::debug(sprintf('[%s] [%s] %s | %s: %s', $query->connection->getDatabaseName(), $duration, $realSql,
		request()->method(), request()->getRequestUri()));
});

日志

[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [500μs] select * from `products` where `products`.`id` = '4' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  
[2020-11-27 23:18:23] local.DEBUG: [test] [390μs] select * from `product_skus` where `product_skus`.`id` = '2' limit 1 | POST: /v1/orders  

[huang-yi]

我本地尝试了一下，并没有复现你描述的情况。你需要检查一下你的代码里面是否有导致重复调用上述DB::listen()的情况。

[wilbur-yu]

检查了下代码, 也没有重复定义或者重复调用的地方.
该监听我是定义在了一个 ServiceProvider 中.
app/Providers/QueryLoggerServiceProvider.php

<?php
declare(strict_types = 1);

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class QueryLoggerServiceProvider extends ServiceProvider
{
	/**
	 * Bootstrap the application services.
	 */
	public function boot(): void
	{
		if (!$this->app['config']->get('logging.query.enabled', false)) {
			return;
		}
		
		DB::listen(function (QueryExecuted $query) {
			if ($query->time < $this->app['config']->get('logging.query.slower_than', 0)) {
				return;
			}
			
			$sqlWithPlaceholders = str_replace(['%', '?'], ['%%', '%s'], $query->sql);
			
			$bindings = $query->connection->prepareBindings($query->bindings);
			$pdo      = $query->connection->getPdo();
			$realSql  = $sqlWithPlaceholders;
			$duration = $this->formatDuration($query->time / 1000);
			
			if (count($bindings) > 0) {
				$realSql = vsprintf($sqlWithPlaceholders, array_map([$pdo, 'quote'], $bindings));
			}
			
			Log::debug(sprintf('[%s] [%s] %s | %s: %s', $query->connection->getDatabaseName(), $duration, $realSql,
				request()->method(), request()->getRequestUri()));
		});
	}
	
	/**
	 * Register the application services.
	 */
	public function register(): void
	{
	}
	
	/**
	 * Format duration.
	 *
	 * @param  float  $seconds
	 *
	 * @return string
	 */
	private function formatDuration(float $seconds): string
	{
		if ($seconds < 0.001) {
			return round($seconds * 1000000).'μs';
		}
		
		if ($seconds < 1) {
			return round($seconds * 1000, 2).'ms';
		}
		
		return round($seconds, 2).'s';
	}
}

然后在 bootstrap/app.php 中注册

<?php

require_once __DIR__.'/../vendor/autoload.php';

(new Laravel\Lumen\Bootstrap\LoadEnvironmentVariables(
	dirname(__DIR__)
))->bootstrap();

date_default_timezone_set(env('APP_TIMEZONE', 'Asia/Shanghai'));

/*
|--------------------------------------------------------------------------
| Create The Application
|--------------------------------------------------------------------------
|
| Here we will load the environment and create the application instance
| that serves as the central piece of this framework. We'll use this
| application as an "IoC" container and router for this framework.
|
*/

$app = new Laravel\Lumen\Application(
	dirname(__DIR__)
);

$app->withFacades();

$app->withEloquent();

/*
|--------------------------------------------------------------------------
| Register Container Bindings
|--------------------------------------------------------------------------
|
| Now we will register a few bindings in the service container. We will
| register the exception handler and the console kernel. You may add
| your own bindings here if you like or you can make another file.
|
*/

$app->singleton(
	Illuminate\Contracts\Debug\ExceptionHandler::class,
	App\Exceptions\Handler::class
);

$app->singleton(
	Illuminate\Contracts\Console\Kernel::class,
	App\Console\Kernel::class
);

/*
|--------------------------------------------------------------------------
| Register Config Files
|--------------------------------------------------------------------------
|
| Now we will register the "app" configuration file. If the file exists in
| your configuration directory it will be loaded; otherwise, we'll load
| the default version. You may register other files below as needed.
|
*/
$app->configure('app');
$app->configure('auth');
$app->configure('broadcasting');
$app->configure('cache');
$app->configure('database');
$app->configure('filesystems');
$app->configure('logging');
$app->configure('queue');
$app->configure('services');
$app->configure('views');
$app->configure('wechat');
$app->configure('enum');
$app->configure('jwt');
$app->configure('cors');
$app->configure('trustedproxy');
$app->configure('horizon');

$app->alias('cache', Illuminate\Cache\CacheManager::class);

/*
|--------------------------------------------------------------------------
| Register Middleware
|--------------------------------------------------------------------------
|
| Next, we will register the middleware with the application. These can
| be global middleware that run before and after each request into a
| route or middleware that'll be assigned to some specific routes.
|
*/

$app->middleware([
	App\Http\Middleware\TrustProxiesMiddleware::class,
	Fruitcake\Cors\HandleCors::class,
	App\Http\Middleware\AcceptHeaderMiddleware::class,
	//	App\Http\Middleware\EtagMiddleware::class,
	//	App\Http\Middleware\IdDecryptMiddleware::class,
]);

$app->routeMiddleware([
	'auth' => App\Http\Middleware\AuthenticateMiddleware::class,
	//	'permission' => Spatie\Permission\Middlewares\PermissionMiddleware::class,
	//	'role'       => Spatie\Permission\Middlewares\RoleMiddleware::class,
]);

/*
|--------------------------------------------------------------------------
| Register Service Providers
|--------------------------------------------------------------------------
|
| Here we will register all of the application's service providers which
| are used to bind services into the container. Service providers are
| totally optional, so you are not required to uncomment this line.
|
*/

/**
 * Application Service Providers
 */
$app->register(App\Providers\AppServiceProvider::class);
$app->register(App\Providers\AuthServiceProvider::class);
$app->register(App\Providers\EventServiceProvider::class);
$app->register(App\Providers\FormRequestServiceProvider::class);
//$app->register(App\Providers\WechatNotificationChannelServiceProvider::class);
$app->register(App\Providers\CustomHorizonServiceProvider::class);
$app->register(App\Providers\HorizonServiceProvider::class);

/**
 * Package Service Providers
 */
$app->register(Illuminate\Redis\RedisServiceProvider::class);
$app->register(Tymon\JWTAuth\Providers\LumenServiceProvider::class);
$app->register(Fruitcake\Cors\CorsServiceProvider::class);
$app->register(Overtrue\LaravelWeChat\ServiceProvider::class);
$app->register(Bavix\Wallet\WalletServiceProvider::class);

/**
 * Dev
 */
if($app->environment() === 'local'){
	$app->register(Flipbox\LumenGenerator\LumenGeneratorServiceProvider::class);
}
$app->register(App\Providers\QueryLoggerServiceProvider::class);

$app->register(HuangYi\Shadowfax\ShadowfaxServiceProvider::class);


/*
|--------------------------------------------------------------------------
| Load The Application Routes
|--------------------------------------------------------------------------
|
| Next we will include the routes file so that they can all be added to
| the application. This will provide all of the URLs the application
| can respond to, as well as the controllers that may handle them.
|
*/

$app->router->group([
	'namespace' => 'App\Http\Controllers',
], function ($router) {
	require __DIR__.'/../routes/web.php';
});

return $app;

[dafa168]

建议放到AppServiceProvider中。

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        $this->debug();
    }

    public function debug()
    {
        //TODO:开发模式下 - DEBUG SQL
        $debug = env('APP_DEBUG');
        if ($debug) {
            /* 开启mongodb的日志监听 */
            $connections = config('database.connections');
            if ($connections) {
                foreach ($connections as $k => $item) {
                    if (isset($item['driver']) && ($item['driver'] === 'mongodb')) {
                        DB::connection($k)->enableQueryLog();
                    }
                }
            }

            //use Illuminate\Support\Str;
            DB::listen(function ($query) {
                $sql = $query->sql;
                $bindings = [];
                if ($query->bindings) {
                    foreach ($query->bindings as $v) {
                        if (is_numeric($v)) {
                            $bindings[] = $v;
                        } else {
                            $bindings[] = '"' . (string)$v . '"';
                        }
                    }
                }
                $execute = Str::replaceArray('?', $bindings, $sql);
                Log::channel('sql')->info(' SQL ：' . $execute, ['time' => $query->time ?? 0, 'connectionName' => $query->connectionName ?? '']);
            });

        }
    }
}