The method of deleting all data tables in a mysql library in batches

The method of deleting all data tables in a mysql library in batches

Command to delete a table

Drop table table name

If you have 100 tables and execute them 100 times by hand, you will collapse when you think about it.

The following provides a scenario for bulk deleting data tables using the information_ schema library:

SELECT CONCAT ('drop table', table_name,';') FROM information_ schema.`TABLES`WHERE table_schema=' database name'

Such as getting the drop table table name through this command; such statements are then executed in batches.

Mysql > SELECT CONCAT ('drop table', table_name,';') FROM information_ schema.`TABLES` WHERE table_schema='discuz'

+-- +

| | CONCAT ('drop table', table_name,';') | |

+-- +

| | drop table common_admincp_cmenu; |

| | drop table common_admincp_group; |

| | drop table common_admincp_member; |

| | drop table common_admincp_perm; |

| | drop table common_admincp_session; |

| | drop table common_admingroup; |

| | drop table common_adminnote; |

| | drop table common_advertisement; |

| | drop table common_advertisement_custom; |

| | drop table common_banned; |

| | drop table common_block; |

| | drop table common_block_favorite; |

| | drop table common_block_item; |

| | drop table common_block_item_data; |

| | drop table common_block_permission; |

| | drop table common_block_pic; |

| | drop table common_block_style; |

| | drop table common_block_xml; |

| | drop table common_cache; |

| | drop table common_card; |

| | drop table common_card_log; |

| | drop table common_card_type; |

| | drop table common_connect_guest; |

| | drop table common_credit_log; |

| | drop table common_credit_log_field; |

| | drop table common_credit_rule; |

| | drop table common_credit_rule_log; |

| | drop table common_credit_rule_log_field; |

| | drop table common_cron; |

| | drop table common_devicetoken; |

| | drop table common_district; |

| | drop table common_diy_data; |

| | drop table common_domain; |

| | drop table common_failedip; |

| | drop table common_failedlogin; |

| | drop table common_friendlink; |

| | drop table common_grouppm; |

| | drop table common_invite; |

| | drop table common_magic; |

| | drop table common_magiclog; |

| | drop table common_mailcron; |

| | drop table common_mailqueue; |

| | drop table common_member; |

| | drop table common_member_action_log; |

| | drop table common_member_connect; |

| | drop table common_member_count; |

| | drop table common_member_crime; |

| | drop table common_member_field_forum; |

| | drop table common_member_field_home; |

| | drop table common_member_forum_buylog; |

| | drop table common_member_grouppm; |

| | drop table common_member_log; |

| | drop table common_member_magic; |

| | drop table common_member_medal; |

| | drop table common_member_newprompt; |

| | drop table common_member_profile; |

| | drop table common_member_profile_setting; |

| | drop table common_member_security; |

| | drop table common_member_secwhite; |

| | drop table common_member_stat_field; |

| | drop table common_member_status; |

| | drop table common_member_validate; |

| | drop table common_member_verify; |

| | drop table common_member_verify_info; |

| | drop table common_member_wechat; |

| | drop table common_member_wechatmp; |

| | drop table common_myapp; |

| | drop table common_myinvite; |

| | drop table common_mytask; |

| | drop table common_nav; |

| | drop table common_onlinetime; |

| | drop table common_optimizer; |

| | drop table common_patch; |

| | drop table common_plugin; |

| | drop table common_pluginvar; |

| | drop table common_process; |

| | drop table common_regip; |

| | drop table common_relatedlink; |

| | drop table common_remote_port; |

| | drop table common_report; |

| | drop table common_searchindex; |

| | drop table common_seccheck; |

| | drop table common_secquestion; |

| | drop table common_session; |

| | drop table common_setting; |

| | drop table common_smiley; |

| | drop table common_sphinxcounter; |

| | drop table common_stat; |

| | drop table common_statuser; |

| | drop table common_style; |

| | drop table common_stylevar; |

| | drop table common_syscache; |

| | drop table common_tag; |

| | drop table common_tagitem; |

| | drop table common_task; |

| | drop table common_taskvar; |

| | drop table common_template; |

| | drop table common_template_block; |

| | drop table common_template_permission; |

| | drop table common_uin_black; |

| | drop table common_usergroup; |

| | drop table common_usergroup_field; |

| | drop table common_visit; |

| | drop table common_word; |

| | drop table common_word_type; |

| | drop table connect_disktask; |

| | drop table connect_feedlog; |

| | drop table connect_memberbindlog; |

| | drop table connect_postfeedlog; |

| | drop table connect_tthreadlog; |

| | drop table forum_access; |

| | drop table forum_activity; |

| | drop table forum_activityapply; |

| | drop table forum_announcement; |

| | drop table forum_p_w_upload; |

| | drop table forum_p_w_upload_0; |

| | drop table forum_p_w_upload_1; |

| | drop table forum_p_w_upload_2; |

| | drop table forum_p_w_upload_3; |

| | drop table forum_p_w_upload_4; |

| | drop table forum_p_w_upload_5; |

| | drop table forum_p_w_upload_6; |

| | drop table forum_p_w_upload_7; |

| | drop table forum_p_w_upload_8; |

| | drop table forum_p_w_upload_9; |

| | drop table forum_p_w_upload_exif; |

| | drop table forum_p_w_upload_unused; |

| | drop table forum_attachtype; |

| | drop table forum_bbcode; |

| | drop table forum_collection; |

| | drop table forum_collectioncomment; |

| | drop table forum_collectionfollow; |

| | drop table forum_collectioninvite; |

| | drop table forum_collectionrelated; |

| | drop table forum_collectionteamworker; |

| | drop table forum_collectionthread; |

| | drop table forum_creditslog; |

| | drop table forum_debate; |

| | drop table forum_debatepost; |

| | drop table forum_faq; |

| | drop table forum_filter_post; |

| | drop table forum_forum; |

| | drop table forum_forum_threadtable; |

| | drop table forum_forumfield; |

| | drop table forum_forumrecommend; |

| | drop table forum_groupcreditslog; |

| | drop table forum_groupfield; |

| | drop table forum_groupinvite; |

| | drop table forum_grouplevel; |

| | drop table forum_groupuser; |

| | drop table forum_hotreply_member; |

| | drop table forum_hotreply_number; |

| | drop table forum_p_w_picpathtype; |

| | drop table forum_medal; |

| | drop table forum_medallog; |

| | drop table forum_memberrecommend; |

| | drop table forum_moderator; |

| | drop table forum_modwork; |

| | drop table forum_newthread; |

| | drop table forum_onlinelist; |

| | drop table forum_order; |

| | drop table forum_poll; |

| | drop table forum_polloption; |

| | drop table forum_polloption_p_w_picpath; |

| | drop table forum_pollvoter; |

| | drop table forum_post; |

| | drop table forum_post_location; |

| | drop table forum_post_moderate; |

| | drop table forum_post_tableid; |

| | drop table forum_postcache; |

| | drop table forum_postcomment; |

| | drop table forum_postlog; |

| | drop table forum_poststick; |

| | drop table forum_promotion; |

| | drop table forum_ratelog; |

| | drop table forum_relatedthread; |

| | drop table forum_replycredit; |

| | drop table forum_rsscache; |

| | drop table forum_sofa; |

| | drop table forum_spacecache; |

| | drop table forum_statlog; |

| | drop table forum_thread; |

| | drop table forum_thread_moderate; |

| | drop table forum_threadaddviews; |

| | drop table forum_threadcalendar; |

| | drop table forum_threadclass; |

| | drop table forum_threadclosed; |

| | drop table forum_threaddisablepos; |

| | drop table forum_threadhidelog; |

| | drop table forum_threadhot; |

| | drop table forum_threadp_w_picpath; |

| | drop table forum_threadlog; |

| | drop table forum_threadmod; |

| | drop table forum_threadpartake; |

| | drop table forum_threadpreview; |

| | drop table forum_threadprofile; |

| | drop table forum_threadprofile_group; |

| | drop table forum_threadrush; |

| | drop table forum_threadtype; |

| | drop table forum_trade; |

| | drop table forum_tradecomment; |

| | drop table forum_tradelog; |

| | drop table forum_typeoption; |

| | drop table forum_typeoptionvar; |

| | drop table forum_typevar; |

| | drop table forum_warning; |

| | drop table home_album; |

| | drop table home_album_category; |

| | drop table home_appcreditlog; |

| | drop table home_blacklist; |

| | drop table home_blog; |

| | drop table home_blog_category; |

| | drop table home_blog_moderate; |

| | drop table home_blogfield; |

| | drop table home_class; |

| | drop table home_click; |

| | drop table home_clickuser; |

| | drop table home_comment; |

| | drop table home_comment_moderate; |

| | drop table home_docomment; |

| | drop table home_doing; |

| | drop table home_doing_moderate; |

| | drop table home_favorite; |

| | drop table home_feed; |

| | drop table home_feed_app; |

| | drop table home_follow; |

| | drop table home_follow_feed; |

| | drop table home_follow_feed_archiver; |

| | drop table home_friend; |

| | drop table home_friend_request; |

| | drop table home_friendlog; |

| | drop table home_notification; |

| | drop table home_pic; |

| | drop table home_pic_moderate; |

| | drop table home_picfield; |

| | drop table home_poke; |

| | drop table home_pokearchive; |

| | drop table home_share; |

| | drop table home_share_moderate; |

| | drop table home_show; |

| | drop table home_specialuser; |

| | drop table home_userapp; |

| | drop table home_userappfield; |

| | drop table home_visitor; |

| | drop table mobile_setting; |

| | drop table mobile_wechat_authcode; |

| | drop table mobile_wechat_masssend; |

| | drop table mobile_wechat_resource; |

| | drop table mobile_wsq_threadlist; |

| | drop table portal_article_content; |

| | drop table portal_article_count; |

| | drop table portal_article_moderate; |

| | drop table portal_article_related; |

| | drop table portal_article_title; |

| | drop table portal_article_trash; |

| | drop table portal_p_w_upload; |

| | drop table portal_category; |

| | drop table portal_category_permission; |

| | drop table portal_comment; |

| | drop table portal_comment_moderate; |

| | drop table portal_rsscache; |

| | drop table portal_topic; |

| | drop table portal_topic_pic; |

| | drop table security_evilpost; |

| | drop table security_eviluser; |

| | drop table security_failedlog; |

| | drop table ucenter_admins; |

| | drop table ucenter_applications; |

| | drop table ucenter_badwords; |

| | drop table ucenter_domains; |

| | drop table ucenter_failedlogins; |

| | drop table ucenter_feeds; |

| | drop table ucenter_friends; |

| | drop table ucenter_mailqueue; |

| | drop table ucenter_memberfields; |

| | drop table ucenter_members; |

| | drop table ucenter_mergemembers; |

| | drop table ucenter_newpm; |

| | drop table ucenter_notelist; |

| | drop table ucenter_pm_indexes; |

| | drop table ucenter_pm_lists; |

| | drop table ucenter_pm_members; |

| | drop table ucenter_pm_messages_0; |

| | drop table ucenter_pm_messages_1; |

| | drop table ucenter_pm_messages_2; |

| | drop table ucenter_pm_messages_3; |

| | drop table ucenter_pm_messages_4; |

| | drop table ucenter_pm_messages_5; |

| | drop table ucenter_pm_messages_6; |

| | drop table ucenter_pm_messages_7; |

| | drop table ucenter_pm_messages_8; |

| | drop table ucenter_pm_messages_9; |

| | drop table ucenter_protectedmembers; |

| | drop table ucenter_settings; |

| | drop table ucenter_sqlcache; |

| | drop table ucenter_tags; |

| | drop table ucenter_vars; |

+-- +

297 rows in set (0.02 sec)

Mysql >

Copy delete table command out, replace | copy to mysql for execution.