mysql 5.7代码块循环,流程控制报错问题解决(实战)-You have an error in your SQL syntax

最近老司机掉坑里了,在mysql 5.7中执行代码块时报错:

错误代码:

delimiter //
create procedure batch_test(in num INT)
begin 
    DECLARE i INT DEFAULT 1; 
		SET i = num; 
    WHILE (i >=0 ) DO 
        INSERT INTO apc (a) VALUES  ('张三'); 
        SET i = i - 1; 
    END WHILE; 
end; //
delimiter ;

call batch_test(10);

报错信息:

create procedure batch_test(in num INT)
begin
DECLARE i INT DEFAULT 1;
SET i = num;
WHILE (i >=0 ) DO
INSERT INTO apc (a) VALUES (‘张三’);
SET i = i - 1;
END WHILE;
end;
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 ’ DECLARE i INT DEFAULT 1;
SET i = num;
WHILE (i >=0 ) DO
’ at line 3
时间: 0s

排错思路:

  1. 标点符号,如分号中英文检查;
  2. DELIMITER关键词在客户端中未被识别关键词,更换客户端即可;

本来是些很简单的问题,细节上需要注意下。

DROP PROCEDURE IF EXISTS batch_test_insert;
DELIMITER $$ # 更改结束符
CREATE PROCEDURE batch_test_insert(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i<=num DO
		################# 代码开始
		## 删除数据
		## delete 
		## commit;
		## 跑数据;
		select DATE_ADD( NOW( ), INTERVAL - i DAY );
		## COMMIT;
		set i = i + 1;
		################# 代码结束
	END WHILE;
END; $$

DELIMITER ;

call batch_test_insert(5);
发布了34 篇原创文章 · 获赞 10 · 访问量 7万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览