如何获取 Laravel 删除/更新/插入语句的原始 SQL?

我知道我可以在查询构建器上使用 toSql 方法来获取带有 SELECT 语句的绑定参数占位符的原始 SQL.

AppUser::where(['id'=>'1'])->toSql();

<块引用>

"select * from `users` where (`id` = ?)"

但是我怎样才能为 DELETE 语句得到这个?

AppUser::where(['id'=>'1'])->delete()->toSql();

<块引用>

PHP 错误:在第 1 行的整数上调用成员函数 toSql()

这将执行该语句，但我想获得原始的、未插值的 SQL，其中问号代表值实际运行查询.同样的情况适用于任何修改语句，例如 INSERT 或 UPDATE

但是为什么，谁在乎呢?

这很像xy问题.我的 Web 应用程序包括一个多进程架构.它在更新数据库的事件驱动循环中运行带有异步通信侦听的自定义工匠命令.

我需要原始查询的原因是因为我想重用准备好的语句以提高性能.不幸的是，eloquent 方法没有公开准备好的语句，所以为了重用一个，我必须自己从底层 PDO 连接准备它.

$sql = 'UPDATE `foo` SET `bar` = ?WHERE (`id` = ?)';$statement = DB::connection()->getPdo()->prepare($sql);而(真){$data = foo();$statement->execute([$data->bar, $data->id]);}

然而，这与抽象的 SQL 语法构建器不同.因为我目前正在使用 MySQL，所以我可以在语法上包含反引号.但现在我陷入了供应商锁定的困境.例如，老板说我们明天要迁移到 MS SQL Server，那么(至少)必须捕获使用反引号而不是方括号的错误可能会很烦人.

我想使用动态生成的 SQL 语法在准备好的语句中重复使用.

解决方案

首先获取模型表的查询构建器实例.

$builder = DB::table((new User)->getTable());

然后获取语法并使用 where 子句从构建器编译删除语句.

$sql = $builder->getGrammar()->compileDelete($builder->where('id', 1));

<块引用>

从 `users` 中删除 `id` = ?"

现在您可以自由地更换数据库驱动程序，并且仍然可以获得适当的平台语法.

I know I can use the toSql method on a query builder to get the raw SQL with binding parameter placeholders for a SELECT statement.

AppUser::where(['id'=>'1'])->toSql();

"select * from `users` where (`id` = ?)"

But how can I get this for a DELETE statement?

AppUser::where(['id'=>'1'])->delete()->toSql();

PHP error: Call to a member function toSql() on integer on line 1

This executes the statement, but I would like to get the raw, uninterpolated SQL generated with the question marks standing in for values without actually running the query. The same case holds for any modification statement, such as INSERT or UPDATE

But why, who cares?

This smells a lot like an xy problem. My web application includes a multi-process architecture. It runs custom artisan commands with asynchronous communication listening in an event-driven loop that update the database.

The reason I need the raw query is because I want to reuse a prepared statement for performance efficiency. Unfortunately, the eloquent methods do not expose the prepared statement, so in order to reuse one, I'll have to prepare it myself from the underlying PDO connection.

$sql = 'UPDATE `foo` SET `bar` = ? WHERE (`id` = ?)';

$statement = DB::connection()->getPdo()->prepare($sql);

while (true) {
    $data = foo();
    $statement->execute([$data->bar, $data->id]);
}

However, this departs from the abstracted SQL grammar builder. Because I'm using MySQL at the moment, I can syntactically include the backticks. But now I'm stuck with vendor lock-in. Say for example, the boss says we're moving to MS SQL Server tomorrow, then it's likely going to be annoying (at least) to have to catch bugs for using backticks instead of square braces.

I want to use the dynamically generated SQL grammar for reusing in a prepared statement.

解决方案

First get a query builder instance for the model's table.

$builder = DB::table((new User)->getTable());

Then get the grammar and compile the delete statement from the builder with a where clause.

$sql = $builder->getGrammar()->compileDelete($builder->where('id', 1));

"delete from `users` where `id` = ?"

Now you can freely swap out database drivers and still get the appropriate platform syntax.