因为有时候,有时候我们只有一个数据库,但是可以用不同的前缀名,比如,我一个wordpress数据库下有wp1_和wp0_两个前缀来区分两个网站。其中wp0_前缀的是个垃圾站,现在我要删除它。但是用wp0_开头的表有很多,我们如果用
drop table 表名;
一个个的删,费时费力,所以要想法子来批量删除以wp0_开头的表。
MySQL批量删除指定前缀表的方法如下:
1.先登录mysql,进入wp0_开头的表所在的库,我这里是wordpress。
#
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
1
2
3
4
5
6
7
8
!/bin/bash
#wordpress是所要删除的表所在的数据库
#删除wordpress数据库中所有以wp0_为前缀的表
droptable=mysql -uroot -ppassword -e "SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'wordpress' AND table_name LIKE 'wp0_%';" | grep -v statement#注意使用~键中的那个点,将删除表的命令复制给droptable_sql变量
echo $droptable;#打印要删除所有以wp0_为前缀的表
mysql -uroot -ppassword -e "use wordpress;${droptable}"#执行删除命令
大家根据自己的情况修改吧,只需要替换其中的mysql用户密码,数据库名,前缀wp0_就行了。
顺带贴出网上MySQL批量删除指定前缀表的php脚本的代码,直接保存为php就行了。写的比较全面吧。
PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
批量删除MySql数据库中相同前缀的表批量删除MySQL数据库相同前缀的数据表 by sudu8
ip地址
数据库账号
数据库密码数据库要删除的前缀( 例如:bbs_)
if(@$_GET["action"]=="kill"){
if (empty($_POST['ip']))
echo "您没有填写IP地址";
echo "
";
if (empty($_POST['user']))
echo "您没有填写用户名";
echo "
";
if (empty($_POST['pass']))
echo "您没有填写密码";
echo "
";
if (empty($_POST['db']))
echo "您没有填写数据库";
echo "
";
if (empty($_POST['qz']))
echo "您没有填写前缀";
echo "
";
$ip=$_POST['ip'];
$user=$_POST['user'];
$pass=$_POST['pass'];
$db=$_POST['db'];
$qz=$_POST['qz'];
$conn=mysql_connect($ip,$user,$pass);
if (!$conn){
die ("数据库连接出错!");
}
mysql_connect($ip,$user,$pass);
mysql_select_db($db);
$rs=mysql_query('show tables');
while($arr=mysql_fetch_array($rs)){
$TF=strpos($arr[0],$qz);
if($TF===0){
$FT=mysql_query("drop table $arr[0]");
if($FT){
echo "$arr[0] 删除成功!
";
}
}
}
}
?>