如何获取Laravel delete / update / insert语句的原始SQL？ [英] How to get the raw SQL for a Laravel delete/update/insert statement?

问题描述

我知道我可以在查询构建器上使用 toSql 方法来获取原始SQL，其中包含绑定参数占位符 SELECT 语句

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.

App\User::where(['id'=>'1'])->toSql();

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

但是如何我可以得到一个 DELETE 语句？

But how can I get this for a DELETE statement?

App\User::where(['id'=>'1'])->delete()->toSql();

PHP错误：调用一个成员函数toSql（）第1行

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

这将执行该语句，但是我想获得生成的未标记的SQL，并附有问号标记值没有实际运行查询。同样的情况适用于任何修改声明，例如 INSERT 或更新

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

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

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.

我需要原始查询的原因是因为我想重新使用一个准备声明表现效率。不幸的是，雄辩的方法不会暴露准备好的语句，所以为了重用一个，我必须从底层的PDO连接自己准备。

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]);
}

然而，这偏离了抽象的SQL语法构建器。因为我现在使用的是MySQL，所以我可以在语法上包括反引号。但是现在我被困在供应商锁定中。例如，老板说我们明天要迁移到MS SQL Server，那么有可能会让人厌烦（至少）要使用反引号而不是方括号来捕捉错误。

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.

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

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

推荐答案

首先得到一个

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

然后获取语法，并使用where子句从构建器编译delete语句。

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.

这篇关于如何获取Laravel delete / update / insert语句的原始SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！