mysql - 结束 while 循环缺少分号

Question

我创建了一个简单的存储过程，它循环遍历一个表的行并将它们插入到另一个表中。由于某种原因，END WHILE 循环抛出缺少分号错误。所有代码对我来说都是正确的，并且所有分隔符都设置正确。我只是不明白为什么它会抛出这些错误，谷歌搜索这个问题只向我指出了使用不当的分隔符答案，仅此而已。任何帮助都会很好!

USE test;
DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO `test_2` (sku, qty) VALUES(sku, qty) FROM `test_dropship_upload` LIMIT i,1;
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

Answer 1

当我在一大段代码中遇到问题并且无法找到问题所在时，我通常会将代码分成较小的 block 并一次测试它们:

测试 1:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE testLoop()
BEGIN
END $$

DELIMITER ;

没有错误:过程声明和分隔符的使用都可以。

测试 2:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
END $$

DELIMITER ;

没有错误:过程中的变量声明正确。

测试 3:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
END $$

DELIMITER ;

没有错误:SELECT 查询和变量赋值正常。

测试 4:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

没有错误:WHILE 循环正常。

测试 5:

现在唯一未测试的部分是 INSERT 查询:

INSERT INTO `test_2` (sku, qty) VALUES(sku, qty) FROM `test_dropship_upload` LIMIT i,1;

查看 INSERT 的文档和 INSERT ... SELECT ，我们可以看到您的查询无效:它显然缺少 SELECT 部分，并且如果您想从另一个表插入值，则不应包含 VALUES 部分:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO `test_2` (sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT i, 1;
  SET i = i + 1;
END WHILE;

END $$

DELIMITER ;

过程创建现已完成，没有错误。

测试 6:

但是，在执行该过程时，SELECT 查询将出现语法错误:MySQL 不接受将 LIMIT 与变量一起使用。
为了使其工作，您需要使用 prepared statement 。

PREPARE stmt FROM "INSERT INTO `test_2` (sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT ?, 1";
EXECUTE stmt using @i;
DEALLOCATE PREPARE stmt;

It is also not allowed to used local variables in prepared statements :

Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters.

要避免此问题，请使用 session 变量 @i 而不是本地变量 i:

程序的最终版本:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testLoop`$$

CREATE PROCEDURE `testLoop`()
BEGIN
DECLARE n INT DEFAULT 0;
SELECT COUNT(*) FROM `test_dropship_upload` INTO n;
SET @i=0;
WHILE @i<n DO
  PREPARE stmt FROM "INSERT INTO `test_2`(sku, qty) SELECT sku, qty FROM `test_dropship_upload` LIMIT ?, 1";
  EXECUTE stmt USING @i;
  DEALLOCATE PREPARE stmt;
  SET @i = @i + 1;
END WHILE;

END $$

DELIMITER ;

您可以应用相同的方法来调试许多复杂的编程问题:从代码的简单版本开始，对其进行测试。如果它有效，请使用更多代码再次测试，如果没有找到并修复错误，然后再继续。