mysql - 无法使用 LOOP 创建 MySQL 过程

Question

已解决:

这个有效:

DROP PROCEDURE IF EXISTS drop_all_tables_from_specified_database;

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database(IN dbname CHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname CHAR(50);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = dbname COLLATE utf8_general_ci;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET foreign_key_checks = 0;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @database_name = dbname;
        SET @table_name = tblname;
        SET @sql_text = CONCAT('DROP TABLE ', @database_name, '.' , @table_name, ';');      

        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur1;

    SET foreign_key_checks = 1;
END$$
DELIMITER ;

CALL drop_all_tables_from_specified_database('test');

DROP PROCEDURE drop_all_tables_from_specified_database;

---

我正在尝试创建一个删除数据库中所有表的过程。我已将此代码保存在文件 tear-down.sql 中:

CREATE PROCEDURE drop_all_tables_from_specified_database()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_to_drop VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='test';

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO table_to_drop;
        IF done:
            LEAVE read_loop;
        END IF;
        DROP TABLE `test`.table_to_drop;
    END LOOP;

    CLOSE cur1;
END;

然后当我在 MySQL 命令行工具中执行操作时:

mysql> source tear-down.sql

我得到了一个巨大的错误:

我不明白我得到的错误，我无法弄清楚哪里出了问题。任何人都可以发现此过程中的错误吗？

编辑:

我当前的代码:

DROP PROCEDURE IF EXISTS drop_all_tables_from_specified_database;

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database(IN dbname CHAR(50))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname CHAR(50);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = dbname;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        DROP TABLE dbname.tblname;
    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;

CALL drop_all_tables_from_specified_database('test');

DROP PROCEDURE drop_all_tables_from_specified_database;

现在它运行正常，但它不会删除表。我收到这样的回复:

Answer 1

看来您需要设置替代DELIMITER:

DELIMITER $$
CREATE PROCEDURE drop_all_tables_from_specified_database()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_to_drop VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='test';

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO table_to_drop;
        /* This might need to be 'IF done <> FALSE THEN' */
        IF done THEN
            LEAVE read_loop;
        END IF;
        DROP TABLE `test`.table_to_drop;
    END LOOP;

    CLOSE cur1;
END$$
DELIMITER ;