给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
当前回答
我创建了一些简单的函数来从一些查询中获取SQL和绑定。
/**
* getSql
*
* Usage:
* getSql( DB::table("users") )
*
* Get the current SQL and bindings
*
* @param mixed $query Relation / Eloquent Builder / Query Builder
* @return array Array with sql and bindings or else false
*/
function getSql($query)
{
if( $query instanceof Illuminate\Database\Eloquent\Relations\Relation )
{
$query = $query->getBaseQuery();
}
if( $query instanceof Illuminate\Database\Eloquent\Builder )
{
$query = $query->getQuery();
}
if( $query instanceof Illuminate\Database\Query\Builder )
{
return [ 'query' => $query->toSql(), 'bindings' => $query->getBindings() ];
}
return false;
}
/**
* logQuery
*
* Get the SQL from a query in a closure
*
* Usage:
* logQueries(function() {
* return User::first()->applications;
* });
*
* @param closure $callback function to call some queries in
* @return Illuminate\Support\Collection Collection of queries
*/
function logQueries(closure $callback)
{
// check if query logging is enabled
$logging = DB::logging();
// Get number of queries
$numberOfQueries = count(DB::getQueryLog());
// if logging not enabled, temporarily enable it
if( !$logging ) DB::enableQueryLog();
$query = $callback();
$lastQuery = getSql($query);
// Get querylog
$queries = new Illuminate\Support\Collection( DB::getQueryLog() );
// calculate the number of queries done in callback
$queryCount = $queries->count() - $numberOfQueries;
// Get last queries
$lastQueries = $queries->take(-$queryCount);
// disable query logging
if( !$logging ) DB::disableQueryLog();
// if callback returns a builder object, return the sql and bindings of it
if( $lastQuery )
{
$lastQueries->push($lastQuery);
}
return $lastQueries;
}
用法:
getSql( DB::table('users') );
// returns
// [
// "sql" => "select * from `users`",
// "bindings" => [],
// ]
getSql( $project->rooms() );
// returns
// [
// "sql" => "select * from `rooms` where `rooms`.`project_id` = ? and `rooms`.`project_id` is not null",
// "bindings" => [ 7 ],
// ]
其他回答
我的方法是基于日志视图,只需修改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,而是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);
Use:
$data = DB::select('select * from users where id = :id', ['id' => 1]);
print_r($data);
输出如下:
Array ( [0] => stdClass Object ( [id] => 1 [name] => parisa [last] => naderi [username] => png [password] => 2132 [role] => 0 ) )
如果您正在使用修补程序并希望记录形成的SQL查询,可以执行以下操作
$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.3.5 — cli) by Justin Hileman
>>> DB::listen(function ($query) { dump($query->sql); dump($query->bindings); dump($query->time); });
=> null
>>> App\User::find(1)
"select * from `users` where `users`.`id` = ? limit 1"
array:1 [
0 => 1
]
6.99
=> App\User {#3131
id: 1,
name: "admin",
email: "admin@example.com",
created_at: "2019-01-11 19:06:23",
updated_at: "2019-01-11 19:06:23",
}
>>>
从laravel 5.2开始。您可以使用DB::listen获取执行的查询。
DB::listen(function ($query) {
// $query->sql
// $query->bindings
// $query->time
});
或者,如果要调试单个Builder实例,则可以使用toSql方法。
DB::table('posts')->toSql();