在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屋！