mysql - MySQL 中的 WHILE 语法错误

Question

我正在尝试在 MySQL (5.1.41-3ubuntu12.10-log) 数据库的一次性查询中使用 while 循环:

WHILE ((SELECT COUNT(*) FROM 
    (SELECT id, COUNT(*) AS cnt
        FROM foo
        GROUP BY id
        ORDER BY COUNT(*) DESC) cnts
    WHERE cnt > 1) != 0) DO
BEGIN

SET @curr_id = (SELECT id FROM 
            (SELECT id, COUNT(*) AS cnt
                FROM foo
                GROUP BY id
                ORDER BY COUNT(*) DESC) cnts
            WHERE cnt > 1
            LIMIT 1);


SET @new_id = (SELECT MAX(id) + 1
        FROM foo);

UPDATE foo 
    SET id = @new_id 
    WHERE id = @curr_id 
    LIMIT 1;

END WHILE;     

这样做的目的是当有多个记录具有相同的 id 时，用下一个 id 更新其中一个。

正文中的语法是正确的，while 语句中使用的谓词也可以毫无问题地自行执行。 MySQL 在查询的开头返回语法错误:

Error Code : 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 'WHILE ((SELECT count(*) FROM 
    (SELECT id, COUNT(*) AS cnt
        FROM stock_produ' at line 1

我意识到这可能不是做事的正确方法，但这是一个非常糟糕(或者根本没有)深思熟虑的数据库，所以如果我能的话那就太棒了让它以这种方式工作。

谢谢，

罗宾

Answer 1

看起来您正试图将此过程代码作为匿名 block 运行。虽然这适用于某些数据库(如 Oracle)，但无法在 MySQL 中完成。

如果要运行它，请将其放入存储过程中，然后调用该过程。因此:

创建程序

DELIMITER $$

CREATE PROCEDURE `foo_update_routine`()
BEGIN
  WHILE ((SELECT COUNT(*) FROM 
    (SELECT id, COUNT(*) AS cnt
      FROM foo
      GROUP BY id
      ORDER BY COUNT(*) DESC
    ) cnts
    WHERE cnt > 1) != 0) 
  DO
    SET @curr_id = (SELECT id FROM 
      (SELECT id, COUNT(*) AS cnt
         FROM foo
         GROUP BY id
         ORDER BY COUNT(*) DESC
      ) cnts
      WHERE cnt > 1
      LIMIT 1);

    SET @new_id = (SELECT MAX(id) + 1 FROM foo);

    UPDATE foo SET id = @new_id 
      WHERE id = @curr_id 
      LIMIT 1;

  END WHILE;
END $$

调用流程

CALL `foo_update_routine`;

PS 你可能想调查 the HAVING clause对于您的选择语句...