mysql - 错误 1064 : SQL syntax

Question

我想在 MySQL/MariaDB 中创建一个过程，但出现我不明白的语法错误。

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_loop$$;
CREATE PROCEDURE proc_loop()
BEGIN
    DECLARE i INT;
    DECLARE j INT;
    DECLARE n INT;
    DECLARE c VARCHAR(3);
    SET i = 1;
    SET j = 1;
    SELECT COUNT(*) INTO n FROM AnswerSets;
    WHILE i < n DO
        WHILE j < 89 DO
            SELECT CONCAT("Q", j) INTO c;
            INSERT INTO T_ANSWER_SET (U_ID, Q_ID, ANSWER) SELECT i, j, c FROM AnswerSets WHERE Id = i;
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
END$$;
DELIMITER ;

我的错误消息:

ERROR 1064 (42000) at line 16: 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 ';
CREATE PROCEDURE proc_loop()
BEGIN
    DECLARE i INT;
    DECLARE j INT;
    DECLARE n ' at line 1

提前感谢您的帮助。

Answer 1

试试这个:

DROP PROCEDURE IF EXISTS proc_loop;

DELIMITER $$

CREATE PROCEDURE `proc_loop`()
BEGIN
    DECLARE i INT;
    DECLARE j INT;
    DECLARE n INT;
    DECLARE c VARCHAR(3);
    SET i = 1;
    SET j = 1;
    SELECT COUNT(*) INTO n FROM AnswerSets;
    WHILE i < n DO
        WHILE j < 89 DO
            SELECT CONCAT("Q", j) INTO c;
            INSERT INTO T_ANSWER_SET (U_ID, Q_ID, ANSWER) SELECT i, j, c FROM AnswerSets WHERE Id = i;
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

如果这不起作用，作为一种调试方法，请尝试删除所有 proc 内容，转而使用诸如 select 1; 之类的琐碎内容，并查看错误是否仍然存在。我之前见过由于过程主体中存在轻微语法错误而错误报告的错误。

To debug the insert, temporarily comment out the insert into t_answer_set:

...
SELECT CONCAT("Q", j) INTO c;
-- INSERT INTO T_ANSWER_SET (U_ID, Q_ID, ANSWER) 
SELECT i, j, c FROM AnswerSets WHERE Id = i;
...