昊天SEO

MySQL批量删除指定前缀表的方法

因为有时候,有时候我们只有一个数据库,但是可以用不同的前缀名,比如,我一个wordpress数据库下有wp1_和wp0_两个前缀来区分两个网站。其中wp0_前缀的是个垃圾站,现在我要删除它。但是用wp0_开头的表有很多,我们如果用

drop table 表名;

一个个的删,费时费力,所以要想法子来批量删除以wp0_开头的表。

MySQL批量删除指定前缀表的方法如下:
1.先登录mysql,进入wp0_开头的表所在的库,我这里是wordpress。

#mysql -uroot -ppassword

mysql> SHOW DATABASES;          //显示所有数据库
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| rss                |
| wordpress          |
+——————–+
4 rows in set (0.08 sec)

mysql> use wordpress;             //进入wordpress数据库
Database changed

2.查看以wp0_开头的所有表,有两种方法。

第一种方法:是直接用show tables;命令但是如果表太多,而且前缀不同,很不方便查看。
mysql> show tables;       //显示wordpress中的所有表
+——————————-+
| Tables_in_wordpress           |
+——————————-+
| inl_link_struct_to_links      |
| inl_link_structures           |
| wp0_bannerize                 |
| wp0_commentmeta               |
| wp0_comments                  |
| wp0_gd_manager                |
| wp0_links                     |
| wp0_moresecurelogin           |
| wp0_options                   |
| wp0_pluginsTalkPLinker        |
| wp0_pluginsTalkPLinkerOptions |
| wp0_post_relationships        |
| wp0_postmeta                  |
| wp0_posts                     |
| wp0_postviews_plus            |
| wp0_sam_ads                   |
| wp0_sam_blocks                |
| wp0_sam_errors                |
| wp0_sam_places                |
| wp0_sam_zones                 |
| wp0_term_relationships        |
| wp0_term_taxonomy             |
| wp0_terms                     |
| wp0_usermeta                  |
| wp0_users                     |
| wp0_wfBadLeechers             |
| wp0_wfBlocks                  |
| wp0_wfBlocksAdv               |
| wp0_wfConfig                  |
| wp0_wfCrawlers                |
| wp0_wfFileMods                |
| wp0_wfHits                    |
| wp0_wfHoover                  |
| wp0_wfIssues                  |
| wp0_wfLeechers                |
| wp0_wfLockedOut               |
| wp0_wfLocs                    |
| wp0_wfLogins                  |
| wp0_wfNet404s                 |
| wp0_wfReverseCache            |
| wp0_wfScanners                |
| wp0_wfStatus                  |
| wp0_wfThrottleLog             |
| wp0_wfVulnScanners            |
| wp0_wp_bot_counter            |
| wp0_wp_rp_tags                |
| wp0_yarpp_related_cache       |
+——————————-+
47 rows in set (0.00 sec)

第二种方法:用一下语句:

mysql> Select CONCAT( ‘drop table ‘, table_name, ‘;’ )
-> FROM information_schema.tables
-> Where table_name LIKE ‘wp0_%’;

其中的wp0_就是我们要删除的表的前缀,大家可以根据自己的情况修改。
显示的是要我们删除的所有以wp0_开头的表。只要按照下面的顺序一一输入就可以删除了
+——————————————-+
| CONCAT( ‘drop table ‘, table_name, ‘;’ )  |
+——————————————-+
| drop table wp0_bannerize;                 |
| drop table wp0_commentmeta;               |
| drop table wp0_comments;                  |
| drop table wp0_gd_manager;                |
| drop table wp0_links;                     |
| drop table wp0_moresecurelogin;           |
| drop table wp0_options;                   |
| drop table wp0_pluginsTalkPLinker;        |
| drop table wp0_pluginsTalkPLinkerOptions; |
| drop table wp0_post_relationships;        |
| drop table wp0_postmeta;                  |
| drop table wp0_posts;                     |
| drop table wp0_postviews_plus;            |
| drop table wp0_sam_ads;                   |
| drop table wp0_sam_blocks;                |
| drop table wp0_sam_errors;                |
| drop table wp0_sam_places;                |
| drop table wp0_sam_zones;                 |
| drop table wp0_term_relationships;        |
| drop table wp0_term_taxonomy;             |
| drop table wp0_terms;                     |
| drop table wp0_usermeta;                  |
| drop table wp0_users;                     |
| drop table wp0_wfBadLeechers;             |
| drop table wp0_wfBlocks;                  |
| drop table wp0_wfBlocksAdv;               |
| drop table wp0_wfConfig;                  |
| drop table wp0_wfCrawlers;                |
| drop table wp0_wfFileMods;                |
| drop table wp0_wfHits;                    |
| drop table wp0_wfHoover;                  |
| drop table wp0_wfIssues;                  |
| drop table wp0_wfLeechers;                |
| drop table wp0_wfLockedOut;               |
| drop table wp0_wfLocs;                    |
| drop table wp0_wfLogins;                  |
| drop table wp0_wfNet404s;                 |
| drop table wp0_wfReverseCache;            |
| drop table wp0_wfScanners;                |
| drop table wp0_wfStatus;                  |
| drop table wp0_wfThrottleLog;             |
| drop table wp0_wfVulnScanners;            |
| drop table wp0_wp_bot_counter;            |
| drop table wp0_wp_rp_tags;                |
| drop table wp0_yarpp_related_cache;       |
+——————————————-+
45 rows in set (0.02 sec)

总之,可以看到,其中以wp0_开头的表不下20是多个,如果我们用drop table 表名;这个命令要输入很多次,所以最好的方法是写成脚本,我这里携程shell脚本,网上还有php的,其实差不多。
MySQL批量删除指定前缀表的方法(脚本))如下:

这里假设我mysql的用户为root,密码为root

 

大家根据自己的情况修改吧,只需要替换其中的mysql用户密码,数据库名,前缀wp0_就行了。

 

顺带贴出网上MySQL批量删除指定前缀表的php脚本的代码,直接保存为php就行了。写的比较全面吧。

本文地址官网    新乡网站建设,seo

分享到:更多 ()
a