laravel 监听sql语句,获取操作数据库的日志

创建监听类

App\Listeners\DbQueryExecutedListener

<?php

namespace App\Listeners;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Session\Store;

/**
 * Class DbQueryExecutedListener.
 * @author http://code.cent123.com/
 */
class DbQueryExecutedListener
{
    protected $session;

    /**
     * Create the event listener.
     */
    public function __construct(Store $session)
    {
        $this->session = $session;
    }

    /**
     * Handle the event.
     */
    public function handle(QueryExecuted $event)
    {
        $id        = $this->session->get('id', 0);
        $arr_data  = $this->getSqlType($event);
        $this->writeSqlLog($event);

        file_put_contents('sqlListener.log', var_export($arr_data, true) . "\r\n", 8);
    }

    /**
     * @param $event
     * @return array
     */
    private function getSqlType($event)
    {
        $sql  = $event->sql;
        $type = substr($sql, 0, 6);
        switch ($type) {
            case 'delete':
            case 'select':
                $pattern = '/from `(\w+)`/';  //from `business_shop_level`
                preg_match($pattern, $sql, $match);
                $table = $match['1'] ?? '';

                break;
            case 'insert':
                $pattern = '/into `(\w+)`/';  //from `business_shop_level`
                preg_match($pattern, $sql, $match);
                $table = $match['1'] ?? '';

                break;
            case 'update':
                $pattern = '/update `(\w+)`/';  //from `business_shop_level`
                preg_match($pattern, $sql, $match);
                $table = $match['1'] ?? '';

                break;
            default:
                $table = '';
        }

        return [
            'type'  => $type,
            'table' => $table,
            'sql'   => $sql,
        ];
    }

    /**
     * @param $event
     * @return string
     */
    private function getExecutedSql($event)
    {
        $sql = str_replace('?', "'%s'", $event->sql);

        return vsprintf($sql, $event->bindings);
    }

    /**
     * @param $event
     */
    private function writeSqlLog($event)
    {
        $data = $this->getExecutedSql($event);
        $log  = date('/* Y-m-d H:i:s */') . '  ' . $data . ";\r\n";

        file_put_contents('sqlListener.sql', $log, 8);
    }

}

引入监听器

App\Providers\EventServiceProvider

namespace App\Providers;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Foundation\Support\Providers\EventServiceProvider as ServiceProvider;

class EventServiceProvider extends ServiceProvider
{
    /**
     * The event listener mappings for the application.
     *
     * @var array
     */
    protected $listen = [

        QueryExecuted::class => [
            'App\Listeners\DbQueryExecutedListener',
        ],
    ];

    /**
     * Register any events for your application.
     */
    public function boot()
    {
        parent::boot();
    }
}

在laravel中执行任何sql都会被监听到