shadowfax
shadowfax copied to clipboard
Published
20 hours ago •
huang-yi
huang-yi
Lumen 8 DB:listen 重复记录 sql 日志
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
检查了下代码, 也没有重复定义或者重复调用的地方. 该监听我是定义在了一个 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;
建议放到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 ?? '']);
});
}
}
}
