前言
收集了一个基于wordpress的资源站数据库，在整理数据的过程中，想将文章标题等导入到excel的表格中方便查看。下面是导出过程。
| 表名 | 相关字段 | 说明 |
|---|---|---|
| wp_terms | term_id, name | 标签,分类,链接 |
| wp_posts | post_title,post_content,post_status,post_type | 文章表 |
| wp_links | link_id,link_url,link_name | 友链表 |
| wp_term_taxonomy | term_taxonomy_id,term_id,parent,taxonomy | 分类信息 |
| wp_term_relationships | object_id,term_taxonomy_id,term_order | 关联表 |
导出语句
select
a.post_title,
(select name from wp_terms where term_id = b.parent) as parent1,
c.name as parent2
into outfile '/tmp/yuanma4.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY '|'
from wp_term_taxonomy b
join wp_terms c
on b.term_id = c.term_id
join wp_term_relationships d
on
b.term_taxonomy_id = d.term_taxonomy_id
join wp_posts a
on
a.ID = d.object_id
where b.taxonomy = 'category'
and
b.parent != 0;csv转xls
# 数据处理
vim打开文件，替换特殊符号","
%s /,/，/g
cat yuanma4.csv | awk -F'|' '{print $1","$2","$3}' > yuanma.csv
带下载链接的升级版本
导出数据
select
a.ID,
a.post_title,
(select name from wp_terms where term_id = b.parent) as parent1,
c.name as parent2,
e.meta_value
into outfile '/tmp/yuanma5.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY '|'
from wp_term_taxonomy b
join wp_terms c
on b.term_id = c.term_id
join wp_term_relationships d
on b.term_taxonomy_id = d.term_taxonomy_id
join wp_posts a
on a.ID = d.object_id
join wp_postmeta e
on a.ID = e.post_id
where b.taxonomy = 'category'
and e.meta_key = '_riplus_down_info'
and b.parent != 0;数据处理
cat yuanma5.csv | awk -F'[|"]' '{print $1","$2","$3","$4","$12","$16}'
评论 (0)