Laravel5中，监视查询SQL

创建event handler

写道

php artisan handler:event QueryLogTracker

 

QueryLogTracker.php

<?php

namespace App\Handlers\Events;

use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Log\Writer;
use Monolog\Logger as Monolog;
use App;

class QueryLogTracker
{
    protected $myLogger;
    /**
     * Create the event handler.
     *
     * @return void
     */
    public function __construct()
    {
        $this->myLogger = new Writer( new Monolog( 'SQL log' ) );
        $this->myLogger->useFiles(App::storagePath().'/logs/sql.log' );
    }

    /**
     * Handle the event.
     *
     * @param  illuminate.query  $event
     * @return void
     */
    public function handle($query, $bindings, $time, $name)
    {
            $data = compact( 'bindings', 'time', 'name' );
         
            // Format binding data for sql insertion
            foreach( $bindings as $i => $binding )
            {
                if( $binding instanceof \DateTime )
                {
                    $bindings[$i] = $binding->format( '\'Y-m-d H:i:s\'' );
                }
                else if( is_string( $binding ) )
                {
                    $bindings[$i] = "'$binding'";
                }
            }
         
            // Insert bindings into query
            $query = str_replace( array( '%', '?' ), array( '%%', '%s' ), $query );
            $query = vsprintf( $query, $bindings );
         
            $this->myLogger->info( $query, $data );
    }
}

 修改 EventServiceProvider.php

    protected $listen = [
        'App\Events\SomeEvent' => [
            'App\Listeners\EventListener',
        ],
        'illuminate.query' => [
            'App\Handlers\Events\QueryLogTracker',
        ],
    ];

 

--------------------------------------------2015/12/29 更新-------------------------------------------------

最新版laravel5.2放出后，上面的例子将不再实用。

首先已经没有了php artisan handler:event这个命令，其次也没有了“illuminate.query”这个字符串事件。

实现方法更新为以下：

1.先在类EventServiceProvider中注册事件监听

 

    protected $listen = [
        'App\Events\SomeEvent' => [
            'App\Listeners\EventListener',
        ],
        'Illuminate\Database\Events\QueryExecuted' => [
            'App\Listeners\QueryLogTracker',
        ],
    ];

 2.执行以下命令，添加未创建的事件和监听器

 

写道

php artisan event:generate

 

3.编辑类QueryLogTracker

<?php

namespace App\Listeners;

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Log\Writer;
use Monolog\Logger as Monolog;
use App;

class QueryLogTracker
{
    /**
     * Create the event listener.
     *
     * @return void
     */
    public function __construct()
    {
        $this->myLogger = new Writer( new Monolog( 'SQL log' ) );
        $this->myLogger->useDailyFiles(App::storagePath().'/logs/sql.log' );
    }

    /**
     * Handle the event.
     *
     * @param  QueryExecuted  $event
     * @return void
     */
    public function handle(QueryExecuted $event)
    {
            $sql = $event->sql;
            $time = $event->time;
            $bindings = $event->bindings;
            $name = $event->connectionName;
            $data = compact( 'bindings', 'time', 'name' );

            // Format binding data for sql insertion
            foreach( $bindings as $i => $binding )
            {
                if( $binding instanceof \DateTime )
                {
                    $bindings[$i] = $binding->format( '\'Y-m-d H:i:s\'' );
                }
                else if( is_string( $binding ) )
                {
                    $bindings[$i] = "'$binding'";
                }
            }

            // Insert bindings into query
            $query1 = str_replace( array( '%', '?' ), array( '%%', '%s' ), $sql );
            $query2 = vsprintf( $query1, $bindings );

            $this->myLogger->info( $query2.PHP_EOL, $data);
    }
}