mysql:cursor似乎永远不会结束

kmpatx3s 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(535)

我正在使用aurora/mysql。我有一个光标，它似乎永远不会结束/完成。
真正让我困惑的是，在我在网上看到的例子中，我从来没有看到他们将“done”或“finish”变量设置为一个。下面是我的剧本。

DELIMITER $$

DROP PROCEDURE IF EXISTS mass_backup $$

CREATE PROCEDURE mass_backup () 
    BEGIN
        DECLARE copy_table VARCHAR(255);
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE table_cursor CURSOR FOR
            SELECT table_name FROM information_schema.tables WHERE table_schema='app_prod_schema';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

        OPEN table_cursor;

        REPEAT
            FETCH table_cursor INTO copy_table;
            SELECT copy_table;
            SET @var1 = copy_table;
            SET @copySql = CONCAT('SELECT * FROM ',@var1,' INTO OUTFILE S3 ''''s3://somebucket.com-work-bucket/''', @var1 , 
                    '.csv', 'FIELDS TERMINATED BY '','' LINES TERMINATED BY ''\n''' );
            SELECT @copySql;
        UNTIL done END REPEAT;
        CLOSE table_cursor;
    END $$

DELIMITER ;

CALL mass_backup()

这个光标到底为什么要永远运行？非常感谢您的帮助。

mysql database-cursor amazon-aurora

来源：https://stackoverflow.com/questions/50080311/mysql-cursor-doesnt-seem-to-ever-end

1条答案

按热度按时间

im9ewurl1#

我认为我在repeat循环中的选择弄乱了“no more rows”处理程序。我去掉了重复动作，放了一个循环。这似乎是一种魅力，让我的发言，我期待。

DELIMITER $$

DROP PROCEDURE IF EXISTS mass_backup $$

CREATE PROCEDURE mass_backup () 
    BEGIN
        DECLARE copy_table VARCHAR(255);
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE table_cursor CURSOR FOR
            SELECT table_name FROM information_schema.tables WHERE table_schema='app_prod_schema' ORDER BY table_name;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

        DROP TEMPORARY TABLE IF EXISTS stmtsTable;
        CREATE TEMPORARY TABLE stmtsTable (backupStmt varchar(200));

        OPEN table_cursor;
        curloop:
        LOOP
            FETCH table_cursor INTO copy_table;
                IF done THEN
            LEAVE curloop;
            END IF;
            #SELECT copy_table;
            SET @var1 = copy_table;
            SET @copySql = CONCAT('SELECT * FROM ',@var1,' INTO OUTFILE S3 \'s3://somebucket/dbbackups/', @var1 , 
                    '.csv\'', ' FIELDS TERMINATED BY \',\' LINES TERMINATED BY \'\\n\'' );
            INSERT INTO stmtsTable(backupStmt)             
            SELECT @copySql;
        END LOOP;
        CLOSE table_cursor;
        SELECT * FROM stmtsTable;
END $$

赞(0）回复(0）举报 2021-06-21

我来回答

mysql:cursor似乎永远不会结束

1条答案

相关问题

热门标签

最新问答