MySQL 中的 WHILE 语法错误 [英] WHILE syntax-error in MySQL

问题描述

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

I'm trying to use a while loop in a one-off query on a MySQL (5.1.41-3ubuntu12.10-log) database:

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 在查询开始时返回语法错误:

The syntax in the body is correct and the predicate used in the while statement also executes without any trouble on it's own. MySQL returns a syntax error on the beginning of the query:

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

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

I realize this might not be The Right Way of doing things, but this is a very badly (or rather not-at-all) thought out database, so it would be awesome if I could get it to work this way.

谢谢，

罗宾

推荐答案

看起来好像您正在尝试将此过程代码作为匿名块运行.虽然这在某些数据库(如 Oracle)中有效，但在 MySQL 中无法完成.

It looks as though you are trying to run this procedural code as an anonymous block. While this works in some databases (like Oracle) it can't be done in MySQL.

如果你想运行这个，那么把它放在一个存储过程中，然后调用这个过程.因此:

If you want to run this then put it in a stored procedure and then call the procedure. Hence:

创建程序

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 您可能想要调查 HAVING 子句您的选择语句...

PS You might want to investigate the HAVING clause for your select statements...

这篇关于MySQL 中的 WHILE 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！