如何截断MySQL数据库中的所有表? [英] How can I truncate all tables from a MySQL Database?

问题描述

是否有任何方法可以从特定的MySQL数据库名称中截断所有表，而无需使用除SQL之外的任何其他语言?我的意思是没有linux shell脚本. (为什么?因为它将在Windows，MacOSX和linux服务器上运行).

Is there any way to truncate all tables from a specific MySQL database name without using any other language than SQL? I mean no linux shell scripts. (why? because it will run on windows, MacOSX and linux servers).

问题是客户端从控制面板网页中的列表中选择数据库名称(这将显示来自不同服务器的* nix和Windows的MySQL数据库)，然后他将要截断其中的所有表数据库(是，这是Web表单的主要任务).

the problem is that the client its selecting the database name from a list in a control panel webpage (wich will be displaying MySQL databases from different servers *nix and windows), and then he will want to truncate all the tables inside that database (yes that is the main task of the web form).

亚历克斯

推荐答案

好的，我自己解决了这是存储过程:)

Ok, I solved it by myself here is the stored procedure :)

BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE; 
    DECLARE truncatestmnt TEXT; -- this is where the truncate statement will be retrieved from cursor

    -- This is the magic query that will bring all the table names from the database
    DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; 

    OPEN c1;

    c1_loop: LOOP
    FETCH c1 INTO truncatestmnt;
    IF `done` THEN LEAVE c1_loop; END IF;
        SET @x = truncatestmnt;
        PREPARE stm1 FROM @x;
        EXECUTE stm1;
    END LOOP c1_loop; 

    CLOSE c1;
END

我正在调用给定数据库中的所有表，如果给定数据库中的表没有可遵循的模式，这将有所帮助.

What I am making its calling all tables from the given database, this will help if the tables inside the given database have no pattern to follow.

因此，通过调用DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";并将结果保存到游标中，我可以获取给定数据库内"n"个表的数量所生成的所有TRUNCATE TABLE x语句，然后只需准备并执行游标中的每个语句即可将截断给定数据库内的所有表.

So by calling DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName"; and saving results into a cursor I can fetch all the TRUNCATE TABLE x statements generated by the "n" quantity of tables inside the given database, then by just preparing and executing each statement in the cursor it will truncate all the tables inside the given database.

BTW @DatabaseName必须作为存储过程的参数提供

BTW @DatabaseName must be given as parameter to the stored procedure

希望这对其他人也有帮助:)

Hope this helps someone else too :)

亚历克斯

这篇关于如何截断MySQL数据库中的所有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！