【MySQL】 empty table and delete table.

標籤：

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 WHERE10     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 WHERE10     table_name LIKE ‘table_mo_%‘;

 1 SELECT 2     CONCAT( 3         ‘ALTER TABLE ‘, 4         table_name, 5         ‘RENAME TO ‘, 6         table_name, 7         ‘;‘ 8     ) 9 FROM10     information_schema. TABLES11 WHERE12     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 BEGIN10     DECLARE11         done INT DEFAULT 0 ; #遊標的標誌位12         DECLARE13             a VARCHAR (20) ; DECLARE14                 b VARCHAR (40) ; DECLARE15                     cur1 CURSOR FOR SELECT16                         table_name17                     FROM18                         information_schema. TABLES19                     WHERE20                         table_schema = DB_NAME21                     AND table_name LIKE concat(prefix, ‘%‘) ; DECLARE22                         CONTINUE HANDLER FOR SQLSTATE ‘02000‘23                     SET done = 1 ; OPEN cur1 ;24                     REPEAT25                         FETCH cur1 INTO a ;26                     IF NOT done THEN27 28                     SET b = concat(29                         ‘drop table ‘,30                         DB_NAME,31                         ‘.‘,32                         a33                     ) ; # 拼刪除命令 34                     # set b=concat(‘TRUNCATE from ‘,DB_NAME,‘.‘,a); # 拼刪除命令 35                     SET @E = b ; PREPARE stmt136                     FROM37                         @E ; EXECUTE stmt1 ; # 執行命令 38                         DEALLOCATE PREPARE stmt1 ; #釋放對象 39                     END40                     IF ; UNTIL done41                     END42                     REPEAT43                         ; 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 DECLARE10     cur1 CURSOR FOR SELECT11         table_name12     FROM13         INFORMATION_SCHEMA. TABLES14     WHERE15         table_schema = ‘db_name‘;16 17 DECLARE18     CONTINUE HANDLER FOR SQLSTATE ‘02000‘19 SET done = 1;20 21 OPEN cur1;22 23 24 REPEAT25     FETCH cur1 INTO tname;26 27 28 IF NOT done THEN29 30 SET @str = concat(‘truncate table ‘, tname);31 32 PREPARE stmt133 FROM34     @str;35 36 EXECUTE stmt1;37 38 DEALLOCATE PREPARE stmt1;39 40 41 END42 IF;43 44 UNTIL done45 END46 REPEAT47 ;48 49 CLOSE cur1;50 51 52 END 53 CALL up_truncate_all_table ();

 

【MySQL】 empty table and delete table.