ITPub博客

MySQL procedure SET autocommit=0; Error Code: 1064DELIMITER $$

原创 Linux操作系统 作者:mchdba 时间:2013-02-27 17:51:29 0 删除 编辑

DROP PROCEDURE IF EXISTS test.t1 $$
DELIMITER $$
CREATE PROCEDURE t1()
    BEGIN
SET autocommit=0;
      DECLARE cnt INT DEFAULT 0;     -- it is wrong order
       WHILE cnt <= 10000 DO
        INSERT INTO t1 VALUES (cnt);
        SET cnt = cnt +1;
       END WHILE;
       COMMIT;
    END$$
DELIMITER ;

and when run the sql block; there was error info , follow this:
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 'DECLARE cnt INT DEFAULT 0;     
       WHILE cnt <= 10000 DO
        INSERT INTO' at line 4

[solved way]:  declare must be set before autocommit;

DELIMITER $$
DROP PROCEDURE IF EXISTS test.t1 $$
DELIMITER $$
CREATE PROCEDURE t1()
BEGIN
DECLARE cnt INT DEFAULT 0; -- it is right order
SET autocommit=0;
WHILE cnt <= 10000 DO
INSERT INTO t1 VALUES (cnt);
SET cnt = cnt +1;
END WHILE;
COMMIT;
END$$
DELIMITER ;

Execution Time : 0.005 sec
Transfer Time  : 0.097 sec
Total Time     : 0.102 sec

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26230597/viewspace-754877/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论
Happy is the man who is living by his hobby.

注册时间:2011-09-05

  • 博文量
    232
  • 访问量
    3688757