给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
当前回答
如果您使用的不是Laravel,而是Elquent包,那么:
use \Illuminate\Database\Capsule\Manager as Capsule;
use \Illuminate\Events\Dispatcher;
use \Illuminate\Container\Container;
$capsule = new Capsule;
$capsule->addConnection([
// connection details
]);
// Set the event dispatcher used by Eloquent models... (optional)
$capsule->setEventDispatcher(new Dispatcher(new Container));
// Make this Capsule instance available globally via static methods... (optional)
$capsule->setAsGlobal();
// Setup the Eloquent ORM...(optional unless you've used setEventDispatcher())
$capsule->bootEloquent();
// Listen for Query Events for Debug
$events = new Dispatcher;
$events->listen('illuminate.query', function($query, $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'";`enter code here`
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $query);
$query = vsprintf($query, $bindings);
// Debug SQL queries
echo 'SQL: [' . $query . ']';
});
$capsule->setEventDispatcher($events);
其他回答
这是我放在基础模型类中的函数。只需将查询生成器对象传递给它,就会返回SQL字符串。
function getSQL($builder) {
$sql = $builder->toSql();
foreach ( $builder->getBindings() as $binding ) {
$value = is_numeric($binding) ? $binding : "'".$binding."'";
$sql = preg_replace('/\?/', $value, $sql, 1);
}
return $sql;
}
要将上次运行的查询输出到屏幕,可以使用以下命令:
\DB::enableQueryLog(); // Enable query log
// Your Eloquent query executed by using get()
dd(\DB::getQueryLog()); // Show results of log
我相信最近的查询将位于阵列的底部。
你会得到这样的东西:
array(1) {
[0]=>
array(3) {
["query"]=>
string(21) "select * from "users""
["bindings"]=>
array(0) {
}
["time"]=>
string(4) "0.92"
}
}
(感谢约书亚在下面的评论。)
将此代码添加到AppServiceProvider并获取日志文件
\DB::listen(function ($query) {
\Log::info(
$query->sql,
$query->bindings,
$query->time
);
});
我的方法是基于日志视图,只需修改app/Providers/AppServiceProvider.php文件:
将此代码添加到app/Provider/AppServiceProvider.php中
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
DB::listen(function ($query) {
$querySql = str_replace(['?'], ['\'%s\''], $query->sql);
$queryRawSql = vsprintf($querySql, $query->bindings);
Log::debug('[SQL EXEC]', [
"raw sql" => $queryRawSql,
"time" => $query->time,
]
);
});
}
我的sql句柄代码:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, username '))
->where('uid', '>=', 10)
->limit(100)
->groupBy('username')
->get()
;
dd($users);
参见日志存储/logs/laravel-2019-10-27.log:
[2019-10-27 17:39:17] local.DEBUG: [SQL EXEC] {"raw sql":"select count(*) as user_count, username from `users` where `uid` >= '10' group by `username` limit 100","time":304.21}
从laravel 5.2开始。您可以使用DB::listen获取执行的查询。
DB::listen(function ($query) {
// $query->sql
// $query->bindings
// $query->time
});
或者,如果要调试单个Builder实例,则可以使用toSql方法。
DB::table('posts')->toSql();