通过现有数据导出新表SQL

Date: 20140217

Auth: JIN

 

需求： 导出一个表的两个列的表的SQL语句（包含数据）

方法：创立一个临时表

mysql> desc kw_keywords;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| keyword | varchar(100) | YES | MUL | | |
| cat_id | int(1) unsigned | YES | | NULL | |
| search_num | int(10) unsigned | YES | | NULL | |
| priority | int(10) unsigned | YES | | NULL | |
| is_embody | int(1) | YES | | NULL | |
| url | varchar(256) | YES | | | |
| remark | varchar(100) | YES | | | |
| type | int(11) | YES | | NULL | |
| addtime | datetime | YES | | NULL | |
| updatetime | datetime | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec

确认数据需要的数据
select keyword,url from kw_keywords where remark='人工筛选';

创建指定列的表
create table tb_word select keyword,url from kw_keywords where remark='人工筛选';

create table tb_word select keyword as word,url from kw_keywords where date_format(addtime,'%Y-%m-%d') in ('2014-02-25', '2014-02-28');

确认
mysql> select * from tb_word limit 10;

导出
# mysqldump -S /data/mysql/3310/mysql.sock -ppwd keyword tb_word > tb_word_20140217.sql