mysql while 语法错误,在MySQL查询中使用WHILE LOOP时出错

最新推荐文章于 2023-03-07 11:08:01 发布

转载最新推荐文章于 2023-03-07 11:08:01 发布 · 557 阅读

文章标签：

在尝试在MySQL（MariaDB 5.5.39）中运行一个创建存储过程的SQL查询时，遇到了语法错误。错误提示表明在查询的多个位置存在语法问题。解决方案是使用`DELIMITER $$`来改变语句结束符，使得存储过程的定义不会在第一个分号处提前结束。正确的代码应该如下所示：`DELIMITER $$ CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO SET v1 = v1 - 1; END WHILE; END; DELIMITER;` 这样，存储过程就能成功创建了。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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.

解决方案

I think this is what you need:

DELIMITER $$

CREATE PROCEDURE dowhile()

BEGIN

DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

SET v1 = v1 - 1;

END WHILE;

END;

DELIMITER ;

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.