在 MySQL 查询中使用 WHILE LOOP 时出错 [英] Error using WHILE LOOP in MySQL query

问题描述

我正在尝试在 MySQL (MariaDB 5.5.39) 中运行 SQL 查询

I'm trying to run a SQL query in MySQL (MariaDB 5.5.39)

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

但是当我运行该查询时，我收到以下错误

But when I run that query I get the following error(s)

MariaDB [elis27rel]> source /home/vagrant/test.sql;
--------------
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5
--------------

ERROR 1064 (42000) at line 1 in file: '/home/vagrant/test.sql': 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
'' at line 3
--------------
WHILE v1 > 0 DO
    SET v1 = v1 - 1
--------------

ERROR 1064 (42000) at line 5 in file: '/home/vagrant/test.sql': 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
'WHILE v1 > 0 DO
    SET v1 = v1 - 1' at line 1
--------------
END WHILE
--------------

ERROR 1064 (42000) at line 7 in file: '/home/vagrant/test.sql': 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
'END WHILE' at line 1
--------------
END
--------------

ERROR 1064 (42000) at line 8 in file: '/home/vagrant/test.sql': 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
'END' at line 1

我不知道我的代码有什么问题.我从 mysql 网站 http://dev.mysql 复制并粘贴了该代码.com/doc/refman/5.0/en/while.html 并希望使用该脚本来弄湿我的脚.任何帮助将不胜感激.

I don't know what the issues with my code are. I copied and pasted that code from the mysql website http://dev.mysql.com/doc/refman/5.0/en/while.html and was hoping to use that script to get my feet wet. Any help would be appreciated.

推荐答案

我想这就是你需要的:

DELIMITER $$

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

DELIMITER ;

分隔符告诉 MySQL 不要在分号处结束语句——这将是一个问题，因为存储过程定义没有在第一个分号处结束.

The delimiter tells MySQL not to end the statement at the semi-colon -- which would be a problem because the stored procedure definition isn't finished at the first semicolon.

这篇关于在 MySQL 查询中使用 WHILE LOOP 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！