mysqlはすべてのテーブルを削除して、データベースを削除しません——ライブラリの走る小さいスクリプトを削除して、ストレージの過程ですべてのテーブルを削除します

ストアド・プロシージャ・スクリプトの作成

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE count INT;
    DECLARE tb VARCHAR(200);
    DECLARE dbname VARCHAR(200) DEFAULT DATABASE();
    DECLARE tbnames cursor FOR SELECT CONCAT('DROP TABLE `',dbname,'`.`',table_name,'`') FROM information_schema.tables WHERE table_schema = dbname;
    SELECT count(*) INTO count FROM information_schema.tables WHERE table_schema = dbname;
    OPEN tbnames;
    loop_i:LOOP
        IF count = 0 THEN 
            LEAVE loop_i;
        END IF;
        FETCH tbnames INTO tb;
        SET @tb = tb;
        PREPARE stmt FROM @tb;  
        EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;
        SET count = count - 1;
	END LOOP;
    CLOSE tbnames;
END$$
DELIMITER ;

使用時にストアド・プロシージャを呼び出す

call drop_all_tables();

 
mysqlコマンドラインでの効果:

mysql> use test;#                  
Database changed
mysql> DELIMITER $$  
mysql> CREATE PROCEDURE `drop_all_tables`()  
    -> BEGIN  
    ->     DECLARE count INT;  
    ->     DECLARE tb VARCHAR(200);  
    ->     DECLARE dbname VARCHAR(200) DEFAULT DATABASE();  
    ->     DECLARE tbnames cursor FOR SELECT CONCAT('DROP TABLE `',dbname,'`.`',table_name,'`') FROM information_schema.tables WHERE table_schema = dbname;  
    ->     SELECT count(*) INTO count FROM information_schema.tables WHERE table_schema = dbname;  
    ->     OPEN tbnames;  
    ->     loop_i:LOOP  
    ->         IF count = 0 THEN   
    ->             LEAVE loop_i;  
    ->         END IF;  
    ->         FETCH tbnames INTO tb;  
    ->         SET @tb = tb;  
    ->         PREPARE stmt FROM @tb;    
    ->         EXECUTE stmt;    
    ->         DEALLOCATE PREPARE stmt;  
    ->         SET count = count - 1;  
    ->     END LOOP;  
    ->     CLOSE tbnames;  
    -> END$$  
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call drop_all_tables();
Query OK, 1 row affected (0.00 sec)

 
関数計算日期間の稼働時間
https://blog.csdn.net/Vincent_Field/article/details/88410161