删除 MySQL 中的所有存储过程或使用临时存储过程 [英] Drop all stored procedures in MySQL or using temporary stored procedures

问题描述

有没有可以删除MySQL中所有存储过程的语句?或者(如果第一个不可能)，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?

推荐答案

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

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.

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

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.

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

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
    CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES;

这篇关于删除 MySQL 中的所有存储过程或使用临时存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！