批量删除mysql一个库所有数据表方法
发表于：2024-11-19 作者：千家信息网编辑
千家信息网最后更新 2024年11月19日，批量删除mysql一个库所有数据表方法删除表的命令drop table 表名;如果有100张表，手工执行100次，想想就崩溃。下面提供一个使用information_schema库的方案来批量删除数据
千家信息网最后更新 2024年11月19日批量删除mysql一个库所有数据表方法
如果有100张表，手工执行100次，想想就崩溃。
下面提供一个使用information_schema库的方案来批量删除数据表：
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';
如通过这条命令来得到drop table 表名;这样的语句，然后批量执行。
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>
复制删除表命令出来，替换了|复制到mysql执行即可。
批量删除mysql一个库所有数据表方法
删除表的命令
drop table 表名;如果有100张表，手工执行100次，想想就崩溃。
下面提供一个使用information_schema库的方案来批量删除数据表：
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';
如通过这条命令来得到drop table 表名;这样的语句，然后批量执行。
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>
复制删除表命令出来，替换了|复制到mysql执行即可。
数据
命令
数据表
方法
手工
数据库
方案
语句
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全相关的名字
计算机软件开发产品概括
如何提高网络服务器安全性
网络安全员考试模拟题
深圳市互联网金融网络科技有
我的世界hehe11服务器ip
梧州网络安全学院攻防技术
村镇银行软件开发
服务器断开我的世界怎么解决
服务器自带3.0驱动的系统
面试软件开发自我介绍
从网络安全上来看
软件开发专业较好的二本院校
网络安全法》第五十九条规
sql实训数据库
宁夏银川软件开发怎么收费
国美互联网生态分享科技公司
市网络安全知识应知应会
plc 设备网络安全
数据库复写
绝地求生韩服单独服务器
编制软件开发项目管理规范依据
jsp查询数据库一个记录
ping服务器超时
密云区综合网络技术服务优点
海康视频服务器输出视频
阿里云lnmp服务器
db2数据库加schema名
云数据库技术原理
网络安全测评可分三类
