mysql empty table_【MySQL】 empty table and delete table.

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_');