php - 为什么我的 sql 语句可以在命令行上运行，但不能在 PHP 上的 mysqli_query 上运行？

Question

我有一个在命令行上运行的 sql 查询(在我的例子中，使用 GUI 运行查询)，但在 PHP 上，通过 mysqli_query() 函数，它不起作用。该错误是重复键错误。

奇怪的是:错误并不存在。它在命令行上运行的事实证明了这一点。而且，经过进一步调查，记录显示唯一 key 从未被破解过。不过，mysqli_query() 会抛出唯一键错误。

我的查询是:

UPDATE
   frequencia 
   INNER JOIN
      (
         SELECT
            *,
            @row_number_aux : = 
            CASE
               WHEN
                  @atual_aux = disciplina_id 
               THEN
                  @row_number_aux + 1 
               ELSE
                  1 
            END
            AS "row_number", @atual_aux : = disciplina_id AS "disciplina_atual" 
         FROM
            frequencia 
         WHERE
            frequencia.data > "2019-06-17" 
            AND frequencia.aluno_id IN 
            (
               8835, 5553, 3488, 7769, 143, 4406, 1472, 698, 8731, 5221, 4240, 4934, 8426, 8107, 5180, 2146, 5589, 4565
            )
      )
      frequencia_aux 
      ON frequencia.id = frequencia_aux.id 
   INNER JOIN
      (
         SELECT
            *,
            @row_number : = 
            CASE
               WHEN
                  @atual = disciplina_id 
               THEN
                  @row_number + 1 
               ELSE
                  1 
            END
            AS "row_number", @atual : = disciplina_id AS "disciplina_atual" 
         FROM
            (
               SELECT
                  disciplina_id,
                  data,
                  tempo,
                  turma_id 
               FROM
                  frequencia 
               WHERE
                  data > "2019-06-17" 
                  AND turma_id = "87" 
               GROUP BY
                  data,
                  disciplina_id,
                  tempo 
            )
            frequencia 
      )
      aux 
      ON frequencia_aux.disciplina_id = aux.disciplina_id 
      AND frequencia_aux.data = aux.data 
      AND frequencia_aux.row_number = aux.row_number 
SET
   frequencia.turma_id = aux.turma_id,
   frequencia.tempo = aux.tempo;

我之前已经使用过@变量，没有任何问题，以及所有类型的复杂语句，并且从未遇到过这样的错误。有人经历过类似的事情吗？

Answer 1

已解决。

问题是:当直接在 mysql cli 上运行时，@ 变量似乎会自动初始化，但不是通过 mysqli_query 运行。对子查询的一个简单连接就完成了这项工作。

感谢@spencer7593 的见解。

最终的sql:

UPDATE 
   frequencia 
   INNER JOIN
      (
         SELECT
            *,
            @row_number_aux := 
            CASE
               WHEN
                  @atual_aux = disciplina_id 
               THEN
                  @row_number_aux + 1 
               ELSE
                  1 
            END
            AS "row_number", @atual_aux := disciplina_id AS "disciplina_atual" 
         FROM
            frequencia                     
            join (select @row_number_aux := 0, @atual_aux := 0) xxx

         WHERE
            frequencia.data > "2019-06-17" 
            AND frequencia.aluno_id IN 
            (
               8835, 5553, 3488, 7769, 143, 4406, 1472, 698, 8731, 5221, 4240, 4934, 8426, 8107, 5180, 2146, 5589, 4565
            )
      )
      frequencia_aux 
      ON frequencia.id = frequencia_aux.id 
   INNER JOIN
      (
         SELECT
            *,
            @row_number := 
            CASE
               WHEN
                  @atual = disciplina_id 
               THEN
                  @row_number + 1 
               ELSE
                  1 
            END
            AS "row_number", @atual := disciplina_id AS "disciplina_atual" 
         FROM
            (
               SELECT
                  disciplina_id,
                  data,
                  tempo,
                  turma_id 
               FROM
                  frequencia 
               WHERE
                  data > "2019-06-17" 
                  AND turma_id = "87" 
               GROUP BY
                  data,
                  disciplina_id,
                  tempo 
            )
            frequencia 
           join (select @row_number := 0, @atual := 0) xxx
      )
      aux 
      ON frequencia_aux.disciplina_id = aux.disciplina_id 
      AND frequencia_aux.data = aux.data 
      AND frequencia_aux.row_number = aux.row_number
SET
   frequencia.turma_id = aux.turma_id,
   frequencia.tempo = aux.tempo