|
问题:要一次性删除MySQL中多个具有某种规则的表,有没有什么技巧?
答:
echo "select concat('drop table ', table_name ,';') from TABLES where TABLE_SCHEMA ='wap' and table_name like '%2006%' ;" | mysql information_schema | sed -n '2,$p' | mysql wap
解析:
我要删除:
drop table WAPNEWS_ISDNLOG_20060101;
drop table WAPNEWS_ISDNLOG_20060102;
drop table WAPNEWS_ISDNLOG_20060103;
drop table WAPNEWS_ISDNLOG_20060104;
drop table WAPNEWS_ISDNLOG_20060105;
drop table WAPNEWS_ISDNLOG_20060106;
drop table WAPNEWS_ISDNLOG_20060107;
drop table WAPNEWS_ISDNLOG_20060108;
drop table WAPNEWS_ISDNLOG_20060109;
drop table WAPNEWS_ISDNLOG_20060110;
drop table WAPNEWS_ISDNLOG_20060111;
drop table WAPNEWS_ISDNLOG_20060112;
drop table WAPNEWS_ISDNLOG_20060113;
drop table WAPNEWS_ISDNLOG_20060114;
drop table WAPNEWS_ISDNLOG_20060115;
这些表,如果手工写太麻烦,所以采用此脚本一次执行完成了。
1.information_schema数据库是MySQL的信息库,用于管理MySQL其他数据库中表,存储过程,索引等的定义和状态的,其中TABLES表是用来描述表结构信息的,结构如下:
[PHP]
mysql> desc TABLES;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(1024) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) | YES | | NULL | |
| DATA_LENGTH | bigint(21) | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) | YES | | NULL | |
| INDEX_LENGTH | bigint(21) | YES | | NULL | |
| DATA_FREE | bigint(21) | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+-----------------+---------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
[/PHP]
2.利用concat来构造sql语句。
3.sed -n '2,$p' 的作用是跳过第一行。因为echo "select ..." | mysql information_schema会带一个头,利用sed跳过一行,从第二行开始输出。
4.把输出的sql语句传递回mysql wap,这样就会针对于wap数据库逐条执行drop table操作。 |
|