wordpress直接通过数据库导出文章标题、分类信息到xls表格

行云流水
2022-07-19 / 0 评论 / 112 阅读 / 正在检测是否收录...

前言

收集了一个基于wordpress的资源站数据库,在整理数据的过程中,想将文章标题等导入到excel的表格中方便查看。下面是导出过程。
表名相关字段说明
wp_termsterm_id, name标签,分类,链接
wp_postspost_title,post_content,post_status,post_type文章表
wp_linkslink_id,link_url,link_name友链表
wp_term_taxonomyterm_taxonomy_id,term_id,parent,taxonomy分类信息
wp_term_relationshipsobject_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)

取消
只有登录/注册用户才可评论