Laravel Eloquent：获取模型查询生成的 SQL 语句 1 个改进

补充下，如果想查看所有的 query，还可以使用 监听查询事件：

DB::listen(function ($sql) { 
    var_dump($sql->sql, $sql->bindings, $query->time); 
});

API 开发用这个包也很方便
https://github.com/overtrue/laravel-query-...

    DB::enableQueryLog();

    if (class_exists(QueryExecuted::class, true)) {
        DB::listen(function ($queryExecuted) {
            $query = $queryExecuted->sql;
            $bindings = $queryExecuted->bindings;
            $spend = $queryExecuted->time;
            $db = $queryExecuted->connectionName;
            $i = 0;
            $message = sprintf(
                "query %s => %s ( %s ms )",
                $db,
                preg_replace_callback('/\?/', function ($matches) use ($bindings, &$i) {
                    if (! empty($bindings[$i])) {
                        return '\''.$bindings[$i++].'\'';
                    } else {
                        return '\''.'\'';
                    }

                }, $query),
                $spend
            );
            $tmp = ['sql' => $message, 'db' => $db, 'cost' => $spend]);
           var_dump($tmp);
        });
    } else {
        DB::listen(function ($query, $bindings, $spend, $db) {
            $i = 0;
            $message = sprintf(
                "query %s => %s ( %s ms )",
                $db,
                preg_replace_callback('/\?/', function ($matches) use ($bindings, &$i) {
                    if (! empty($bindings[$i])) {
                        return '\''.$bindings[$i++].'\'';
                    } else {
                        return '\''.'\'';
                    }

                }, $query),
                $spend
            );
           $tmp = ['sql' => $message, 'db' => $db, 'cost' => $spend]);
           var_dump($tmp);
        });
    }

@leoyang QueryExecuted这个类哪个版本开始有的

还有能输出当前链接使用的ip和端口吗

https://learnku.com/courses/laravel-packag...
这个比较好使用

使用helper str_replace_array()可以把SQL和binding结合成完整的SQL语句

可以在中间件实现

class QueryLogMiddleware
{
    public function handle($request, Closure $next)
    {
        return $next($request);
    }

    /**
     * 程序发送响应之后的操作，记录慢查询日志或查询全日志
     *
     * @param Request $request
     * @param         $response
     */
    public function terminate(Request $request, $response)
    {
        $queryLogs = \DB::getQueryLog();
        if (empty($queryLogs)) {
            return;
        }

        $currentUrl = $request->fullUrl();

        $enableFullQueryLog = config('database.enableQueryLog');

        if ($enableFullQueryLog) {
            $this->writeFullQueryLog($queryLogs, $currentUrl);
        }

        $enableSlowQueryLog = config('database.enableSlowLog');

        if ($enableSlowQueryLog) {
            $this->slowQueryLog($queryLogs, $currentUrl);
        }

    }

    public function slowQueryLog(array $data, string $url)
    {
        //大于一秒
        $slow = array_filter($data, function ($value) {
            return $value['time'] > 1000;
        });
        $slow = $this->formatQueryString($slow);

        if (!empty($slow)) {
            \Log::error('found slow query', [
                'url'  => $url,
                'sqls' => $slow
            ]);
        }
        //todo DingTalk notify and throttle limit
    }

    /**
     * 写全查询log到文件
     *
     * @param array  $data
     * @param string $url
     */
    public function writeFullQueryLog(array $data, string $url)
    {
        $logs = json_encode($this->formatQueryString($data));

        $fileName = 'logs' . DIRECTORY_SEPARATOR . "query-" . date('Y-m-d') . '.log';
        $logFile = fopen(
            storage_path($fileName),
            'a+'
        );
        fwrite($logFile, date('Y-m-d H:i:s') . ': ' . $url . " | " . $logs . PHP_EOL);
        fclose($logFile);
    }

    /**
     * 拼接SQL字符串
     *
     * @param array $data
     *
     * @return array
     */
    public function formatQueryString(array $data)
    {
        return array_map(function ($value) {
            return [
                'time' => array_get($value, 'time') . 'ms',
                'sql'  => Str::replaceArray('?', array_get($value, 'bindings'), array_get($value, 'query'))
            ];
        }, $data);
    }
}