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

it不难
2022-07-19 / 0 评论 / 8 阅读 / 正在检测是否收录...

前言

表名相关字段说明
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

评论 (0)

取消