前言

搭建了个淘宝客网站开心屋,用的网上找的现成的WordPress淘客模板,自己做了些功能行的修改,整体已经可以成型。该淘宝客网站优惠券上万,几乎每日更新,用WordPress的文章更新是不可能的,所以需要批量进行更新替换。

方法1:使用导入插件

有人专门开发了批量导入插件,如wp-all-import,这个插件看上去很强大,但使用起来完全不顺手,而且在数据库里残留的信息太多,小服务器更新几次就不堪重负了。使用教程就不写了,网上有的是,试了两次不能满足自己的要求,所以舍弃。

方法2:使用SQL导入

在淘宝客后台能够获得excel列表,里面包含了上万的链接,这些链接是不能直接导入数据库的,需要先进行处理下,以适应主题显示的需求。

excel数据预处理

匹配分类

1
2
3
4
5
6
7
8
9
10
11
=IFS(COUNT(FIND({"饰","表","眼镜","箱包","个性","包装","珠宝"},D2))>0,"包包配饰",
COUNT(FIND({"妆","美发","美容"},D2))>0,"美妆彩妆",
COUNT(FIND({"女鞋","女装"},D2))>0,"女装女鞋",
COUNT(FIND({"男鞋","男装"},D2))>0,"男装男鞋",
COUNT(FIND({"护理","运动","户外"},D2))>0,"运动户外",
COUNT(FIND({"茶","酒","食","饮","营养品"},D2))>0,"零食茶饮",
COUNT(FIND({"智能","家具","电脑","电器","电子","数码","器","音像","家电","电玩","手机","存储","MP","网络","办公"},D2))>0,"数码家电",
COUNT(FIND({"情趣","内衣","动漫","手工"},D2))>0,"内衣情趣",
COUNT(FIND({"车"},D2))>0,"汽车用品",
COUNT(FIND({"玩具","婴","童","孕","尿片","奶"},D2))>0,"童装母婴",
COUNT(FIND({"五金","礼品","水产","粮油","日","清洁","床上","用具","收纳","洗护","书","花","居","家装","家庭","宠物","建材"},D2))>0,"居家生活")

匹配分类ID

1
=IFS(P2="男装男鞋",2,P2="女装女鞋",3,P2="包包配饰",4,P2="运动户外",5,P2="美妆彩妆",6,P2="童装母婴",7,P2="零食茶饮",8,P2="内衣情趣",9,P2="数码家电",10,P2="居家生活",11,P2="汽车用品",12)

生成SQL导入文件

生成SQL

1
2
3
4
5
=CLEAN(CONCATENATE("REPLACE INTO wp_posts(ID,post_title,post_excerpt,post_content,post_status) values('",A2,"','",B2,"','",L2,"','",B2,"','publish');"))
=CLEAN(CONCATENATE("REPLACE INTO wp_posts(ID,post_title,post_excerpt,post_status) values('",A2,"','",B2,"','",L2,"','publish');"))
=CLEAN(CONCATENATE("REPLACE INTO wp_term_relationships(object_id,term_taxonomy_id) values('",A2,"','",Q2,"');"))
=CLEAN(CONCATENATE("REPLACE INTO wp_postmeta(post_id,meta_key,meta_value) values('",A2,"','","hao_zhutu","','",C2,"'),('",A2,"','","hao_yuanj","','",F2,"'),('",A2,"','","hao_xianj","','",O2,"'),('",A2,"','","hao_youh","','",N2,"');"))
=CLEAN(CONCATENATE("REPLACE INTO wp_postmeta(post_id,meta_key,meta_value) values('",A2,"','","hao_zongl","','",K2,"'),('",A2,"','","hao_ljgm","','",M2,"'),('",A2,"','","hao_xiaol","','",G2,"'),('",A2,"','","hao_leix","','",J2,"');"))

SQL导入

先把数据库清理下,以免数据库残留:

1
2
3
4
DELETE FROM `wp_posts` WHERE `ID`>12;
DELETE FROM `wp_postmeta` WHERE `post_id`>12;
ALTER TABLE `wp_postmeta` AUTO_INCREMENT=200;
DELETE FROM `wp_term_relationships` WHERE `object_id`>12;

然后把之前生成的SQL文件导入到数据库即可。为了完善文章内容,可以把文章更新日期一起更新下:

1
UPDATE `wp_posts` SET `post_author`='kaixinwu.vip',`post_modified`='2018-11-8 10:37:03',`post_modified_gmt`='2018-11-8 10:37:09' WHERE `ID`>12;

以上,就完成了将淘宝客链接批量导入及更新到网站的工作了,把公式和处理方法保存下来,以后每天15分钟即可完成上万篇内容的更新了。