如何在phpMyAdmin中声明游标？添加游标代码后存储过程报错求助

首先，你的存储过程报错主要有两个关键原因：

1. DECLARE语句的位置不符合MySQL规则 #

在MySQL的存储过程中，所有DECLARE声明（包括变量、游标、条件处理程序）必须放在BEGIN块的最顶部，不能嵌套在WHILE循环这类流程控制语句内部。你把游标c1的声明放在了WHILE循环里，这直接违反了语法规则，导致解析报错。

2. 游标声明语法错误 #

你写的DECLARE c1 FOR ...缺少了CURSOR关键字，正确的游标声明语法应该是DECLARE c1 CURSOR FOR ...。

修正后的完整存储过程代码 #

DELIMITER $$ 
CREATE PROCEDURE addTickets (IN howMany INT) 
BEGIN 
    -- 所有DECLARE必须放在BEGIN的最顶部
    DECLARE L1 INT default 0; 
    DECLARE L2 INT default 0; 
    DECLARE L3 INT default 0; 
    DECLARE L4 INT default 0; 
    DECLARE L5 INT default 0; 
    DECLARE L6 INT default 0; 
    DECLARE count1 INT;
    -- 游标声明也必须放在这里
    DECLARE c1 CURSOR FOR SELECT i FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() LIMIT 6;

    set count1 = 0; 
    while count1 < howMany DO 
        OPEN c1; 
        FETCH c1 INTO L1; 
        FETCH c1 INTO L2; 
        FETCH c1 INTO L3; 
        FETCH c1 INTO L4; 
        FETCH c1 INTO L5; 
        FETCH c1 INTO L6; 
        CLOSE c1; 

        INSERT INTO kupony VALUES(NULL, 1, L1, L2, L3, L4, L5, L6, -1, '2018-01-01', -1); 
        set count1 = count1 + 1; 
    end while; 
END$$ 
DELIMITER ;

更简洁的优化方案（无需游标） #

其实你不需要用游标来实现这个需求，MySQL支持直接将查询结果赋值给多个变量，这样代码更简洁高效：

DELIMITER $$ 
CREATE PROCEDURE addTickets (IN howMany INT) 
BEGIN 
    DECLARE L1 INT default 0; 
    DECLARE L2 INT default 0; 
    DECLARE L3 INT default 0; 
    DECLARE L4 INT default 0; 
    DECLARE L5 INT default 0; 
    DECLARE L6 INT default 0; 
    DECLARE count1 INT;

    set count1 = 0; 
    while count1 < howMany DO 
        -- 依次获取6个不重复的随机值
        SELECT i INTO L1 FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() LIMIT 1;
        SELECT i INTO L2 FROM integers WHERE i BETWEEN 1 AND 49 AND i != L1 ORDER BY RAND() LIMIT 1;
        SELECT i INTO L3 FROM integers WHERE i BETWEEN 1 AND 49 AND i NOT IN (L1, L2) ORDER BY RAND() LIMIT 1;
        SELECT i INTO L4 FROM integers WHERE i BETWEEN 1 AND 49 AND i NOT IN (L1, L2, L3) ORDER BY RAND() LIMIT 1;
        SELECT i INTO L5 FROM integers WHERE i BETWEEN 1 AND 49 AND i NOT IN (L1, L2, L3, L4) ORDER BY RAND() LIMIT 1;
        SELECT i INTO L6 FROM integers WHERE i BETWEEN 1 AND 49 AND i NOT IN (L1, L2, L3, L4, L5) ORDER BY RAND() LIMIT 1;

        INSERT INTO kupony VALUES(NULL, 1, L1, L2, L3, L4, L5, L6, -1, '2018-01-01', -1); 
        set count1 = count1 + 1; 
    end while; 
END$$ 
DELIMITER ;

如果想进一步提升效率，还可以用单次查询获取6个不重复值，避免多次查询数据库：

DELIMITER $$ 
CREATE PROCEDURE addTickets (IN howMany INT) 
BEGIN 
    DECLARE count1 INT;
    set count1 = 0; 
    while count1 < howMany DO 
        -- 一次性获取6个不重复随机值并插入
        INSERT INTO kupony 
        SELECT NULL, 1, t1.i, t2.i, t3.i, t4.i, t5.i, t6.i, -1, '2018-01-01', -1
        FROM (SELECT i FROM integers WHERE i BETWEEN 1 AND 49 ORDER BY RAND() LIMIT 6) AS temp
        CROSS JOIN (SELECT @row := 0) AS init
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 1) AS t1
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 2) AS t2
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 3) AS t3
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 4) AS t4
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 5) AS t5
        CROSS JOIN (SELECT i FROM temp WHERE @row := @row + 1 = 6) AS t6;

        set count1 = count1 + 1; 
    end while; 
END$$ 
DELIMITER ;

内容的提问来源于stack exchange，提问作者gushy22