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

Drop all stored procedures in MySQL or using temporary stored procedures

提问by Kenston Choi

Is there a statement that can drop all stored procedures in MySQL? Alternatively (if the first one is not possible), is there such thing as temporary stored procedures in MySQL? Something similar to temporary tables?

是否有可以删除 MySQL 中所有存储过程的语句？或者（如果第一个不可能），MySQL 中是否有临时存储过程之类的东西？类似于临时表的东西？

回答by user1070300

I would have thought that this would do it, but I'm open to corrections:

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

(EDITED to incorporate a good point provided in the comments)

（编辑以纳入评论中提供的一个好观点）

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

(As pointed out by Balmipour in the comments below, it's a good idea to specify the database.)

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

I think it's valid to want to drop all procedures in a given database, otherwise in a long development cycle there's a danger of obsolete procedures and functions accumulating and muddying everything up.

我认为想要删除给定数据库中的所有过程是有效的，否则在漫长的开发周期中，存在过时的过程和函数积累和混淆一切的危险。

回答by Ivar Bonsaksen

Since DROP PROCEDURE and DROP FUNCTION does not allow sub selects, I thought it might be possible to perform the operation through another stored procedure, but alas, MySQL does not allow stored procedures to drop other stored procedures.

由于 DROP PROCEDURE 和 DROP FUNCTION 不允许子选择，我认为可能可以通过另一个存储过程来执行操作，但唉，MySQL 不允许存储过程删除其他存储过程。

I tried to trick MySQL to to this anyway by creating prepared statements and thus separating the drop call somewhat from the stored procedure, but I've had no luck.

我试图通过创建准备好的语句来欺骗 MySQL，从而将 drop 调用与存储过程分开，但我没有运气。

So therefore my only contribution is this select statement which creates a list of the statements needed to drop all stored procedures and functions.

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

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

回答by Levitron

This seems to drop the procedures...not sure about implications though

这似乎放弃了程序......虽然不确定影响

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

回答by treat your mods well

I can almostget working a piece of code to drop all stored procs, but I think MySQL isn't letting me use a LOOP ora CURSOR outside of a stored procedure.

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

I can write a SQL file that counts the number of stored procedures for a given schema, and I have the code to iterate through the table and drop procedures, but I can't get it to run:

我可以编写一个 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;

回答by Sayka

I use select statement to display all Drop Procedure statements of a specific database in one cell. Then copy it to query window.

我使用 select 语句在一个单元格中显示特定数据库的所有 Drop Procedure 语句。然后将其复制到查询窗口。

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; 

回答by Angelin Nadar

after the select statement which creates a list of the statements needed to drop all stored procedures and functions. You can avoid the manual work of copy pasting the listed queries as follows:

在创建删除所有存储过程和函数所需的语句列表的 select 语句之后。您可以避免手动复制粘贴列出的查询，如下所示：

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;

回答by Balmipour

@user1070300's answer seems to work, but it looks like it can drop a lot of things.

@user1070300 的答案似乎有效，但看起来它可以丢弃很多东西。

A quick DESC mysql.proc;led me to add a WHERE to my request, so as to spare already existing MySQL procedures, which I had, of course, no reason to drop.

很快DESC mysql.proc;我就在我的请求中添加了一个 WHERE，以节省已经存在的 MySQL 程序，当然，我没有理由放弃。

I executed DELETE FROM mysql.proc WHERE db NOT LIKE 'mysql';
If you have several bases and want to target only one, use DELETE FROM mysql.proc WHERE db LIKE '<yourDbName>'instead;

我执行了DELETE FROM mysql.proc WHERE db NOT LIKE 'mysql';
如果您有多个基地并且只想定位一个，请DELETE FROM mysql.proc WHERE db LIKE '<yourDbName>'改用；

Btw, if like me, you are completely clearing your database and need to also drop your tables, you can use this linux shell script : mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done(see Truncate all tables in a MySQL database in one command?for more details)

顺便说一句，如果像我一样，你正在完全清除你的数据库并且还需要删除你的表，你可以使用这个 linux shell 脚本：（ mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done请参阅在一个命令中截断 MySQL 数据库中的所有表？了解更多详细信息）

Truncate all tables in a MySQL database in one command?

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