MySQL While 语句抛出错误并且只插入一条 NULL 记录

Question

我认为我在下面的 mysql 脚本中编写 WHILE 语句时做错了。我已经测试了下面代码的每个单独组件，并且所有内容都是独立运行的(没有抛出的错误测试输出与预期相符)，除了 WHILE(抛出错误并仅输入 NULL 值，并且仅经历一次循环迭代)。我正在关注https://dev.mysql.com/doc/refman/5.7/en/while.html记录的说明，但我似乎遗漏了一些东西。我想要做的是将几条记录(数百万条)插入到该表中。我究竟做错了什么？为什么这个 WHILE 语句不循环并且为什么它只插入 NULL 值？

来源

USE wordpress;
SET @i := 0;

WHILE @i < 10 DO
    SET @rand_value = CAST(FLOOR(RAND()*10000) as CHAR);
    SELECT @rand_value as CONCAT('rand_value at ', CAST(@i as CHAR));
    INSERT INTO wp_wpdatatable_1 (jiraissue, assignee, testcasename, description, image, automation, testresult, date, status, priority, os, testtype, location, flavor) 
    VALUES 
    (CONCAT('TEST ISSUE ', @rand_value)
    , CONCAT('ASSIGNEE ', @rand_value)
    , CONCAT('TEST CASE NAME ', @rand_value)
    , CONCAT('DESCRIPTION ', @rand_value)
    , CONCAT('IMAGE ', @rand_value)
    , CONCAT('AUTOMATION ', @rand_value)
    , CONCAT('TESTRESULT ', @rand_value)
    , CONCAT('DATE ', @rand_value)
    , CONCAT('STATUS ', @rand_value)
    , CONCAT('PRIORITY ', @rand_value)
    , CONCAT('OS ', @rand_value)
    , CONCAT('TESTTYPE ', @rand_value)
    , CONCAT('LOCATION ', @rand_value)
    , CONCAT('FLAVOR ', @rand_value));
    SET @i := @i + 1;
END WHILE;

输出

mysql> source /root/populate_wp_wpdatatable_1.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): 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 'WHILE @i < 10 DO
    SET @rand_value = CAST(FLOOR(RAND()*10000) as CHAR)' at line 1
ERROR 1064 (42000): 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 '('rand_value at ', CAST(@i as CHAR))' at line 1
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): 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 'END WHILE' at line 1

奇怪的是，您可以在输出中看到“1 row受影响”。因此，它实际上会执行 WHILE 循环的一次迭代，但它只执行一次，并且只将 NULL 值放入表中。希望有人能发现我所缺少的东西。提前致谢!

Answer 1

所以有一些问题。首先，正如 @Barmar 指出的，WHILE 语句仅在存储过程中起作用。另外，正如他指出的那样，我的选择是错误的(但也毫无意义，所以我删除了它)。不过还需要做一件事。由于您必须创建存储过程，因此还需要将 DELIMITER 设置为 ; 之外的其他内容。下面是正确的代码。

DELIMITER $$
CREATE PROCEDURE populate_wp_wpdatatable_1(x INT)
BEGIN
    SET @i = 0;
    WHILE @i < x DO
          SET @rand_value = CAST(FLOOR(RAND()*10000) as CHAR);
          INSERT INTO wp_wpdatatable_1 (jiraissue, assignee, testcasename, description, image, automation, testresult, date, status, priority, os, testtype, location, flavor) 
          VALUES 
          (CONCAT('TEST ISSUE ', @rand_value)
          , CONCAT('ASSIGNEE ', @rand_value)
          , CONCAT('TEST CASE NAME ', @rand_value)
          , CONCAT('DESCRIPTION ', @rand_value)
          , CONCAT('IMAGE ', @rand_value)
          , CONCAT('AUTOMATION ', @rand_value)
          , CONCAT('TESTRESULT ', @rand_value)
          , NOW() - INTERVAL FLOOR(RAND() * 365) DAY
          , CONCAT('STATUS ', @rand_value)
          , CONCAT('PRIORITY ', @rand_value)
          , CONCAT('OS ', @rand_value)
          , CONCAT('TESTTYPE ', @rand_value)
          , CONCAT('LOCATION ', @rand_value)
          , CONCAT('FLAVOR ', @rand_value));
          SET @i = @i + 1;
    END WHILE;
END;
$$
DELIMITER ;

所以是的，有趣的东西。再次感谢@Barmar 的帮助。