如何在理论中使用mysql变量 [英] How to use mysql variables in doctrine
问题描述
https://stackoverflow.com/a/12772507/1507546
我想通过学说执行此查询,但下面出现错误
I want to execute this query through doctrine but I'm getting the error below
[Doctrine \ DBAL \ Driver \ PDOException]SQLSTATE [42000]:语法错误或访问冲突:1064 SQL语法有错误;检查与您的MariaDB服务器版本相对应的手册,以在附近使用正确的语法第1行的'@counter:= 0'
[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@counter := 0' at line 1
这是我的代码
$sql = <<<S
SET @counter = 0;
Select sub.orderid,sub.value,(@counter := @counter +1) as counter
FROM
(
select orderid,
round(sum(unitprice * quantity),2) as value
from order_details
group by orderid
) sub
order by 2 desc
limit 10
S;
stmt = $this->_em->getConnection()->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(AbstractQuery::HYDRATE_ARRAY);
推荐答案
大多数sql API在没有额外配置的情况下都不允许多个语句.您需要将它们作为单独的语句传递:
Most sql APIs don't allow multiple statements without extra configuration. You'll need to pass them in as separate statements:
$this->_em->getConnection()->exec("SET @counter = 0"); // May need tweaking, I'm not familiar with Doctrine
$sql = <<<S
Select sub.orderid,sub.value,(@counter := @counter +1) as counter
FROM
(
select orderid,
round(sum(unitprice * quantity),2) as value
from order_details
group by orderid
) sub
order by 2 desc
limit 10
S;
stmt = $this->_em->getConnection()->prepare($sql);
这篇关于如何在理论中使用mysql变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!