前言

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

方法1：使用导入插件

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

方法2：使用SQL导入

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

excel数据预处理

匹配分类

=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

=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

=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导入

先把数据库清理下，以免数据库残留：

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`='sobaigu.com',`post_modified`='2018-11-8 10:37:03',`post_modified_gmt`='2018-11-8 10:37:09' WHERE `ID`>12;

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

其他方法

自己会折腾代码的，可以参照这个方法试下：http://blog.sina.com.cn/s/blog_5d972ae00100gjij.html

淘宝客链接怎么批量导入到网站