mysql用户变量三个要素,Laravel 3中的用户定义的MySQL变量？-CSDN博客

在Laravel中尝试通过Fluent查询执行包含用户定义变量的多条MySQL语句时遇到了错误。问题在于Laravel的PDO底层库不允许在一个查询中执行多条语句。解决方案是将多条语句分开，分别使用DB::query()来执行。通过DB::raw()可以正确执行设置用户变量的语句，然后单独执行更新排名的语句。

摘要由CSDN通过智能技术生成

I want to update the "rank" for a group of MySQL records with sequential numbers using a user-defined variable. The following query runs fine via the MySQL command line:

SET @rank:=0; UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC

But if I try and run it as a Fluent query using Laravel, it fails.

DB::query("SET @rank:=0; UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC");

Error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL

syntax; check the manual that corresponds to your MySQL server version for the right

syntax to use near 'UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY' at line 1

SQL: SET @rank:=0; UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC

Bindings: array (

)

[SOLVED]

DB::raw() to the rescue! The following works:

DB::query(DB::raw("SET @rank:=0"));

DB::query("UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC");

解决方案

It is not possible to execute multiple statements in one query. Laravel uses PDO under the hood which prevents this. You could attempt to call this over 2 queries instead, since @rank should be available for the duration of the connection.

DB::query("SET @rank:=0");

DB::query("UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=? ORDER BY score DESC", array(4));