Mysql批量删除相同前缀的表

今天上午想备份一下微擎的数据库哎,打开phpmyadmin一看好几百张表,有些表是之前安装的应用自带的,现在不用了,不想备份这谢谢表了,就想着批量把相同前缀的这些表删掉。这里只写具体方法:

use information_schema;
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'jiaozhou' AND table_name LIKE 'ims_mihua%';

查询出来后,得到的statement语句形如下面这种格式:

DROP TABLE ims_mihua_sq_account,ims_mihua_sq_address,ims_mihua_sq_admin,ims_mihua_sq_adv,ims_mihua_sq_area,ims_mihua_sq_balance,ims_mihua_sq_black,ims_mihua_sq_cart,ims_mihua_sq_cash,ims_mihua_sq_channel,ims_mihua_sq_chat,ims_mihua_sq_city,ims_mihua_sq_citys,ims_mihua_sq_collect,ims_mihua_sq_comment,ims_mihua_sq_discount,ims_mihua_sq_discount_record,ims_mihua_sq_fields,ims_mihua_sq_footmark,ims_mihua_sq_get_redpackage,ims_mihua_sq_goods,ims_mihua_sq_goods_cate,ims_mihua_sq_group,ims_mihua_sq_info,ims_mihua_sq_info_comment,ims_mihua_sq_infoorder,ims_mihua_sq_integral,ims_mihua_sq_member,ims_mihua_sq_msg,ims_mihua_sq_mstime,ims_mihua_sq_option,ims_mihua_sq_order,ims_mihua_sq_order_goods,ims_mihua_sq_order_record,ims_mihua_sq_param,ims_mihua_sq_qiandao,ims_mihua_sq_redmsg,ims_mihua_sq_redpackage,ims_mihua_sq_refund,ims_mihua_sq_ring,ims_mihua_sq_ring_zan,ims_mihua_sq_sensitiveword,ims_mihua_sq_share_history,ims_mihua_sq_shop,ims_mihua_sq_shop_admin,ims_mihua_sq_shop_apply,ims_mihua_sq_shop_cate,ims_mihua_sq_shop_cfg,ims_mi;

复制sql语句到phpmyadmin里面执行一下就ok了 



2018.10.10补充


最近发现用group_concat 如果查询字段过长会出现查询数据展示不全的问题,原因是group_concat 有个默认长度配置  group_concat_max_len  用 

show varialbes like 'group_concat_max_len' 可以查询该长度  默认是1024

那么解决方法就是改变这个配置咯  

SET SESSION group_concat_max_len = 10240; or SET GLOBAL group_concat_max_len = 10240;

接下来在查询一下 :

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name SEPARATOR ' ') , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'wq' AND table_name LIKE 'ims_ewei%';

完美~

Adam博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论