"MySQL" Empty table and delete table.

1.MySQL generates SQL to delete tables that meet the criteria:

  1  select    CONCAT (  ' DROP TABLE '    4   GROUP_CONCAT (table_name),   5  ‘;‘  6  ) as statement   7  from   8   INFORMATION_SCHEMA. TABLES   9  where  10  table_schema = ' testmybatis ' 11  and table_name like ' table_mo_% '; 

DROP TABLE table_mo_tt1,table_mo_tt222; Execute the generated statement.

2.MySQL Bulk deletion of the specified prefix table:

1 SELECT 2     CONCAT (  3         ' drop table ',  4        table_name,  5         '; ' 6     )  7 from 8    information_schema. TABLES  9WHEREten     table_name like ' table_mo_% ';

1 SELECT2 CONCAT (3' ALTER TABLE ',4 table_name,5' RENAME to ',6 table_name,7‘;‘8     )9  fromTen information_schema. TABLES One WHERE Atable_name like ' table_mo_% ';

3. Use the stored procedure to delete all tables that meet the criteria:

1 DROP PROCEDURE drop_table;2DELIMITER//3 4 5 CREATE PROCEDURE drop_table (6Db_name VARCHAR (50),7Prefix VARCHAR (16)8 )9 BEGINTen DECLARE OneDone INT DEFAULT 0; #游标的标志位 A DECLARE -A VARCHAR (20) ; DECLARE -b VARCHAR (40) ; DECLARE the cur1 CURSOR for SELECT - table_name -  from - information_schema. TABLES + WHERE -Table_schema =db_name +and table_name like concat (prefix, '% ') ; DECLARE ACONTINUE HANDLER for SQLSTATE ' 02000 ' atSET done = 1 ; OPEN Cur1; - REPEAT - FETCH cur1 into A; - IF not do Then -  -SET B =concat ( in' Drop table ', - Db_name, to‘.‘, + a -                     ) ; # Spell Delete command the# set B=concat (' TRUNCATE from ', Db_name, '. '), a); # Spell Delete command *SET @E =b; PREPARE STMT1 $  fromPanax Notoginseng @E; EXECUTE STMT1; # Execute Command - deallocate PREPARE stmt1; #释放对象 the END + IF; UNTIL Done A END the REPEAT +                         ; CLOSE Cur1; -END;// $ DELIMITER; $Call Drop_table (' Testmybatis ', ' table_mo_ ');

4. Use stored procedures to bulk empty a table that satisfies a condition

1 CREATE PROCEDURE ' up_truncate_all_table ' ()2 BEGIN3 DECLARE4Done INT DEFAULT 0;5 6 DECLARE7Tname CHAR (50);8 9 DECLARETen cur1 CURSOR for SELECT One table_name A  from - information_schema. TABLES - WHERE theTable_schema = ' db_name '; -  - DECLARE -CONTINUE HANDLER for SQLSTATE ' 02000 ' +SET done = 1; -  + OPEN Cur1; A  at  - REPEAT - FETCH cur1 into Tname; -  -  - IF not do Then in  -SET @str = concat (' TRUNCATE table ', tname); to  + PREPARE STMT1 -  from the @str; *  $ EXECUTE stmt1;Panax Notoginseng  - deallocate PREPARE stmt1; the  +  A END the IF; +  - UNTIL Done $ END $ REPEAT - ; -  the CLOSE Cur1; - Wuyi  the END -Call Up_truncate_all_table ();

"MySQL" empty table and delete table.