如何使用存储过程在MySQL数据库中删除所有视图？

Question

我希望我的mysql服务器删除数据库中的所有视图。我已经为存储过程创建了一个事件：

BEGIN

DECLARE done INT DEFAULT FALSE; 
DECLARE VIEW_NAME CHAR(255);
DECLARE VIEW_NAMES CURSOR FOR SELECT table_name FROM information_schema.views;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN VIEW_NAMES;

read_loop: LOOP    
    FETCH VIEW_NAMES INTO VIEW_NAME;
    IF done THEN
        LEAVE read_loop;
    END IF;    
    DROP VIEW VIEW_NAME;
END LOOP;

CLOSE VIEW_NAMES;

然而，此过程会产生以下错误：

错误代码：1051。未知表“view_name”

我的猜测是我的提取语句由于某种原因没有提取。所以我检查了选择语句并且它确实有一个结果集。有人有关于如何解决错误或如何删除mysql数据库中所有视图的想法吗？

END

- Rafiek

---

尝试一下这个：http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html。 - Rob

为什么我需要进行检查？ - Rafiek

Answer 1

您需要使用预处理语句来执行这些查询。构建一个 'DROP VIEW...' 的字符串，然后使用预处理语句执行该语句。

例如 -

...
read_loop: LOOP    
    FETCH VIEW_NAMES INTO VIEW_NAME;
    IF done THEN
        LEAVE read_loop;
    END IF;    

    SET @query = CONCAT('DROP VIEW ', VIEW_NAME);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END LOOP;
...

---

另一种方法，无需打开光标：

SET @views = NULL;

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @views
  FROM information_schema.views 
  WHERE table_schema = 'database_name';

SET @views = CONCAT('DROP VIEW ', @views);
PREPARE stmt FROM @views;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

根据您的需要修改WHERE条件。

- Devart

---

太好了！没有注意到应该构建一个字符串。 - Rafiek

总是喜欢“无光标”版本，特别是在MySQL中。 - Marc L.