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

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中是否存在临时存储过程?类似于临时表的东西?

7 个解决方案

#1

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过程和DROP函数不允许sub - select，我认为可以通过另一个存储过程执行操作，但是，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;

#2

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.

我认为在一个给定的数据库中删除所有的过程是合理的，否则在一个长期的开发周期中会有过时的过程和函数积累和混淆所有东西的危险。

#3

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

这似乎放弃了程序……不过我不太清楚其中的含义

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

#4

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

我几乎可以编写一段代码来删除所有存储的procs，但我认为MySQL不允许在存储过程之外使用循环或游标。

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;

#5

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;

#6

@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;

我执行了从mysql的删除。proc中db不喜欢mysql;如果您有几个碱基，并且想只针对一个碱基，请使用DELETE FROM mysql。proc中db喜欢' ';

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 |当读表;做mysql -e“下表$table”DATABASE_NAME;完成了(请参见在一个命令中截断MySQL数据库中的所有表?更多细节)

Truncate all tables in a MySQL database in one command?

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

#7

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;

#1