在MySQL中删除所有存储过程或使用临时存储过程。

Question

是否有一种语句可以删除MySQL中所有的存储过程？ 如果第一个不可行，那么MySQL中是否有临时存储过程这样的东西？类似于临时表。

- Kenston Choi

---

嗨，虽然听起来有点好笑：)) 但这是我从 MS SQL Server（http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx）借鉴的一些内容，它说：“SQL Server 2000 还支持临时存储过程，就像临时表一样，在断开连接时会自动删除。” 但我的目的是在会话结束后将所有内容都删除，尽管我仍然有备份计划，比如使用语言循环存储过程名称并逐个删除它们（还没有检查是否可能）。 - Kenston Choi

@ebyrob - 你说得很对。MySQL不支持匿名的过程性代码块。我的评论反映了从Oracle DBMS进行的无效转移，该数据库允许执行任意过程性代码而无需定义永久存储过程。 - APC

Answer 1

我本以为这样可以做到，但我愿意接受更正：

（编辑以整合评论中提供的好建议）

delete from mysql.proc WHERE db LIKE <yourDbName>;

(正如下面评论中Balmipour所指出的那样，指定数据库是个好主意。)

我认为想要删除给定数据库中的所有过程是有效的，否则在长时间的开发周期中，会存在废弃的过程和函数堆积，使得一切变得混乱。

- user1070300

---

可以用！实现起来简单多了！！ :) - Steven de Salas

@user1070300 我担心这将会破坏所有程序，包括与你的数据库无关的MySQL程序，以及如果你在服务器上有多个数据库，则来自所有数据库的程序。请考虑向此请求添加“WHERE db LIKE <yourDbName>”，因为它看起来非常危险。 - Balmipour

@Balmipour 说得好，虽然它不再回答最初的请求了，那就是一个可以删除MySQL中所有存储过程的语句。 - user1070300

mysql.proc在MySQL 5.7中是否仍然是与元信息交互的可行方法？这个命令似乎比读取information_schema.ROUTINES并生成动态SQL命令更方便。 - ebyrob

另外，在执行任何不确定的删除操作之前，运行一次 select 语句是个好主意：SELECT * FROM mysql.proc WHERE db LIKE 'yourDbName'; - Manatax

Answer 2

由于DROP PROCEDURE和DROP FUNCTION不允许子查询，我认为通过另一个存储过程执行操作可能是可行的，但遗憾的是，MySQL不允许存储过程删除其他存储过程。

我试图通过创建预处理语句来欺骗MySQL并从存储过程中分离出删除调用，但是我没有成功。

因此，我的唯一贡献就是这个选择语句，它创建了一个语句列表，以删除所有存储过程和函数。

SELECT
    CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES;

- Ivar Bonsaksen

---

这将为所有可用的数据库模式创建删除函数。请谨慎使用！ - Dasun

Answer 3

这似乎会删除这些程序...不过我不确定会有什么影响

DELETE FROM mysql.proc WHERE db = 'Test' AND type = 'PROCEDURE';

- Levitron

---

请参见 https://stackoverflow.com/questions/49276518/mysql-5-7-drop-stored-procedures-via-mysql-proc/49279921#49279921。 - JonBrave

Answer 4

我几乎可以让一段代码工作起来来删除所有存储过程，但是我认为MySQL不允许我在存储过程之外使用LOOP或CURSOR。

我可以编写一个SQL文件来计算给定模式的存储过程数量，并且我有迭代表格并删除过程的代码，但我无法使其运行：

SELECT COUNT(ROUTINE_NAME)
  INTO @remaining
  FROM information_schema.ROUTINES
  WHERE ROUTINE_SCHEMA = SCHEMA()
    AND ROUTINE_TYPE = 'FUNCTION';

kill_loop: LOOP
  IF @remaining < 1 THEN
    LEAVE kill_loop;
  END IF;
  SELECT ROUTINE_NAME
    INTO @cur_func_name
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = SCHEMA()
      AND ROUTINE_TYPE = 'FUNCTION'
    LIMIT 1;
  DROP FUNCTION @cur_func_name;
  @remaining = @remaining - 1;
END LOOP;

- treat your mods well

---

感谢您提出的这个想法，它将有助于未来的用户！我不确定应该如何解决光标/循环方面的问题... - Kenston Choi

@Kenston：最终我写了一个MySQL脚本，输出可以删除存储过程的SQL语句 -- 因此输出可以被传回到mysql中。 - treat your mods well

Answer 5

我使用select语句来显示特定数据库中所有Drop Procedure语句，并将其复制到查询窗口中。请注意保留HTML标签。

SET group_concat_max_len = 4096;

SELECT GROUP_CONCAT(Procedures SEPARATOR '; ') From (SELECT CONCAT(
"DROP PROCEDURE IF EXISTS `",SPECIFIC_NAME, '`') AS Procedures 
FROM information_schema.ROUTINES R WHERE R.ROUTINE_TYPE = "PROCEDURE" 
AND R.ROUTINE_SCHEMA = 'my_database_name') As CopyThis; 

- Abdul Saleem

Answer 6

在创建存储过程和函数的语句列表后，您可以避免手动复制粘贴列出的查询，方法如下：

mysql>SELECT
    CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

- Angelin Nadar

---

谢谢您的想法，不过这应该作为对被接受答案的评论。我实际上是使用Java运行的，但这对其他人仍然有用。 - Kenston Choi

人们忽略注释，包括我在内，而且它们也不太引人注意。 - Angelin Nadar

Answer 7

@user1070300的回答似乎可以工作，但看起来它会删除很多东西。

快速执行DESC mysql.proc;让我添加了WHERE到我的请求中，以避免删除已经存在的MySQL存储过程，这当然是不必要的。

我执行了DELETE FROM mysql.proc WHERE db NOT LIKE 'mysql';
如果您有多个数据库并且只想针对其中一个，请改用DELETE FROM mysql.proc WHERE db LIKE '<yourDbName>'；

顺便说一句，如果像我一样，您正在完全清除数据库并且需要同时删除表格，您可以使用此Linux Shell脚本： mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done （请参见在一个命令中截断MySQL数据库中的所有表了解更多详情）

在一个命令中截断MySQL数据库中的所有表

- Balmipour

---

如果您要清除整个数据库，重新创建模式是否更容易？ - Manatax

@manatax 当然可能是这样，但我不记得它们，我猜我有不使用这种方法的充分理由。缺少执行此操作的权限可能是其中之一。不想干扰其他数据库设置是另一个原因（我怀疑用户会受到影响，但我确定某些方面会受到影响）。我没有足够的经验来给你更精确的答案，但无论如何，我的回答的主要目的是：不要盲目删除所有内容，添加where语句！（我很高兴答案被编辑以考虑到这一点） - Balmipour