How to print executed SQL statements in laravel (two methods)

It is a common thing to use Eloquent ORM to query the database in Laravel, but when we face some complex query requirements, we need to debug by viewing the executed SQL statements. So how to print the executed SQL statements?

Laravel provides two ways to print executed SQL statements, one is through log output, and the other is through event listener printing.

Through log output

SQL statements executed through log output in Laravel are the most convenient way, and there are corresponding log recording and viewing methods in each environment.

We can turn on the logging of SQL statements in the configuration file:

// 在config/database.php文件中，找到default下的connections数组，增加以下选项：
'log_queries' => true,  // 开启SQL日志记录
'log_channel' => 'daily', // 日志存储方式，也可使用syslog、errorlog等方式
'log_level' => 'debug', // 日志级别

After adding the log_queries option, Laravel will automatically record the executed SQL statements, and the logs will be output to the storage/logs directory.

After executing the query statement in the code, we can output the SQL statement in the following way:

DB::enableQueryLog();
// 执行查询语句
$users = DB::table('users')->get();
// 获取执行的SQL语句
$sql = DB::getQueryLog()[0]['query'];

In the above code, first call the DB::enableQueryLog() method to enable recording of the SQL statement , after executing the query, you can obtain all executed SQL statements through the DB::getQueryLog() method. If you want to obtain the last SQL statement, you can also use the DB::getLastQuery() method.

However, it should be noted that using this method to record a large number of SQL statements in the production environment will cause the log volume to increase, which is not conducive to system maintenance and troubleshooting. Therefore, do not frequently record SQL statements in the production environment. Statement log.

Through the event listener

Laravel provides a way to listen to SQL execution events. Developers can output SQL statements by listening to SQL execution events. This method is safer and more reliable and will not be used in production. A large number of logs are generated in the environment.

By defining a listener, the corresponding SQL statement can be printed out when executing any query statement. The following is the implementation code:

// 在AppServiceProvider的boot方法中，添加以下代码
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Events\QueryExecuted;
use Log;

// 注册SQL执行监听器
DB::listen(function (QueryExecuted $queryExecuted) {
    $sql = str_replace("?", "'%s'", $queryExecuted->sql);
    $bindings = $queryExecuted->connection->prepareBindings($queryExecuted->bindings);
    $fullSql = vsprintf($sql, $bindings);

    Log::debug('SQL:'.$fullSql);
});

In the above code, we use DB::listen() to register an event listener, which will be triggered when any query statement is executed.

In the listener, the SQL statement of the current query and related binding parameters and other information are obtained through the QueryExecuted event, and then output to the log through the Log::debug() method.

Through the above two methods, we have achieved the purpose of printing SQL statements in Laravel, which can make debugging and troubleshooting more convenient, and plays an irreplaceable role in development.

The above is the detailed content of How to print executed SQL statements in laravel (two methods). For more information, please follow other related articles on the PHP Chinese website!