php - 通过 mysql_query(...) 执行时有效的 mysql 查询返回错误

Question

我有以下 PHP 代码:

$result = mysql_query('
            set @num := 0, @type := "";
            UPDATE orders INNER JOIN (
                SELECT id, user_id, created, row_number 
                FROM 
                (
                    SELECT id, user_id, created,
                    @num := if(@type = user_id, @num + 1, 1) AS row_number,
                    @type := user_id AS dummy
                    FROM orders
                    WHERE status = "queue" AND type="order"
                    ORDER BY user_id, created asc 
                ) AS grouped_orders 
                WHERE grouped_orders.row_number <= 2
            ) m ON orders.id = m.id 
            SET orders.status = "process", orders.lock_id = "hash";
        ');

当我跳过 $result = mysql_query(' and '); 并将此查询复制粘贴到我的 PHP MyAdmin 面板时，它会起作用 - 但是当我执行它时来自 PHP 的 mysql_query(...) 我收到以下错误:

您的 SQL 语法有误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'UPDATE 命令 INNER JOIN (
选择 id、user_id、已创建、row_number
FROM' 在第 2 行

我尝试用括号做一些事情，向上/向下移动并折叠成一行，但它不起作用...

不幸的是，在我工作的服务器上没有 mysqli。

Answer 1

尝试 multi-query而不是 mysql_query 因为你正在执行多个查询

编辑

尝试在您的 select due subselects 中定义您的变量:

UPDATE orders INNER JOIN (
      SELECT id, user_id, created, row_number 
      FROM 
      (
           SELECT id, user_id, created,
           @num := if(@type = user_id, @num + 1, 1) AS row_number,
           @type := user_id AS dummy
           FROM orders
           WHERE status = "queue" AND type="order"
           ORDER BY user_id, created asc 
            ) AS grouped_orders, (SELECT @num:=0) r, (SELECT @type:="") t
            WHERE grouped_orders.row_number <= 2
        ) m ON orders.id = grouped_orders.id 
        SET orders.status = "process", orders.lock_id = "hash";