1.MySQL生成删除满足条件的表的sql：
1 SELECT
2 CONCAT(
3 '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;执行生成的语句即可。
2.MySQL批量删除指定前缀表：
1 SELECT
2 CONCAT(
3 'drop table ',
4 table_name,
5 ';'
6 )
7 FROM
8 information_schema. TABLES
9 WHERE
10 table_name LIKE 'table_mo_%';
1 SELECT
2 CONCAT(
3 'ALTER TABLE ',
4 table_name,
5 'RENAME TO ',
6 table_name,
7 ';'
8 )
9 FROM
10 information_schema. TABLES
11 WHERE
12 table_name LIKE 'table_mo_%';
3.使用存储过程删除所有满足条件的表：
1 DROP PROCEDURE drop_table;
2 DELIMITER //
3
4
5 CREATE PROCEDURE drop_table (
6 DB_NAME VARCHAR (50),
7 prefix VARCHAR (16)
8 )
9 BEGIN
10 DECLARE
11 done INT DEFAULT 0 ; #游标的标志位
12 DECLARE
13 a VARCHAR (20) ; DECLARE
14 b VARCHAR (40) ; DECLARE
15 cur1 CURSOR FOR SELECT
16 table_name
17 FROM
18 information_schema. TABLES
19 WHERE
20 table_schema = DB_NAME
21 AND table_name LIKE concat(prefix, '%') ; DECLARE
22 CONTINUE HANDLER FOR SQLSTATE '02000'
23 SET done = 1 ; OPEN cur1 ;
24 REPEAT
25 FETCH cur1 INTO a ;
26 IF NOT done THEN
27
28 SET b = concat(
29 'drop table ',
30 DB_NAME,
31 '.',
32 a
33 ) ; # 拼删除命令
34 # set b=concat('TRUNCATE from ',DB_NAME,'.',a); # 拼删除命令
35 SET @E = b ; PREPARE stmt1
36 FROM
37 @E ; EXECUTE stmt1 ; # 执行命令
38 DEALLOCATE PREPARE stmt1 ; #释放对象
39 END
40 IF ; UNTIL done
41 END
42 REPEAT
43 ; CLOSE cur1 ;
44 END ;//
45 DELIMITER ;
46 call drop_table('testmybatis', 'table_mo_');
4.使用存储过程批量清空满足条件的表
1 CREATE PROCEDURE `up_truncate_all_table` ()
2 BEGIN
3 DECLARE
4 done INT DEFAULT 0;
5
6 DECLARE
7 tname CHAR (50);
8
9 DECLARE
10 cur1 CURSOR FOR SELECT
11 table_name
12 FROM
13 INFORMATION_SCHEMA. TABLES
14 WHERE
15 table_schema = 'db_name';
16
17 DECLARE
18 CONTINUE HANDLER FOR SQLSTATE '02000'
19 SET done = 1;
20
21 OPEN cur1;
22
23
24 REPEAT
25 FETCH cur1 INTO tname;
26
27
28 IF NOT done THEN
29
30 SET @str = concat('truncate table ', tname);
31
32 PREPARE stmt1
33 FROM
34 @str;
35
36 EXECUTE stmt1;
37
38 DEALLOCATE PREPARE stmt1;
39
40
41 END
42 IF;
43
44 UNTIL done
45 END
46 REPEAT
47 ;
48
49 CLOSE cur1;
50
51
52 END
53 CALL up_truncate_all_table ();