把wordpress站的内容导入到thinkcmf中

随着SAE价格的水涨船高,本人在SAE上的云豆也坚持不了几天了,为了节省点云豆,准备把两个站合并为一个。旧站是wordpress搭建的,新站为了更好的做二次开发,采用了thinkcmfx这个cms,虽然两个都是php+mysql的,但想要将数据整合起来,还是需要了解两套系统的数据库的。

wordpress中,一篇文章主要存在4个表中

  1. wp_post – 帖子的主题,内容等
  2. wp_postmeta – 帖子的扩展信息,包括缩略图,点击数等
  3. wp_terms – 帖子的标签、分类表
  4. wp_term_relationships – 帖子和标签,分类的关联
    Thinkcmfx的表结构跟wordpress类似,只是将postmeta的内容直接存在了posts表中,使用了一个smeta字段,用json方式存放扩展信息。

我的数据转移思路是:在本地操作,将wordpress的数据对应好字段,存到thinkcmf的数据库中,再导出为sql文件到正式环境去导入:

先在wordpress数据库中建cp_posts表,用sql将wordpress中的内容插入到cp_posts表中,使用的sql如下:

 INSERT INTO `cp_posts` (`post_author`, `post_keywords`, `post_source`, `post_date`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `post_modified`, `post_content_filtered`, `post_parent`, `post_type`, `post_mime_type`, `comment_count`, `smeta`, `post_hits`, `post_like`, `istop`, `recommended`)
 SELECT `post_author`,
 `post_title` as post_keywords,
 (select wp_terms.name from wp_term_relationships inner join wp_terms on term_id=term_taxonomy_id where object_id=p.ID and term_id in (3,4,5,6,7)) as post_source,
 `post_date`, `post_content`, `post_title`, `post_excerpt`, 1 as post_status, 1 as comment_status, `post_modified`, `post_content_filtered`, 0 as post_parent, null as post_type, `post_mime_type`, `comment_count`, (select concat('{"thumb":"',guid,'"}') from wp_posts where ID = (SELECT meta_value
 FROM wp_postmeta
 WHERE `post_id` = p.ID
 AND `meta_key` = '_thumbnail_id')
 ) as smeta, (select meta_value from wp_postmeta where `post_id`=ID and `meta_key`='views') as post_hits,0 as post_like, 0 as istop, 0 as recommended FROM `wp_posts` as p WHERE `post_status` = 'publish' and `post_type`='post'
 `</pre>
此sql将分类的名称放到了post_source字段中,以便后续关联,如果旧站的分类名字和新站的名字不一样,那就要更新一下才能对应上:
<pre>`
 update cp_posts set post_source = '知识库' where post_source = '扩展模块' ;`

update cp_posts set post_source = '知识库' where post_source = '常见问题' ;

update cp_posts set post_source = '企业动态' where post_source = '通知公告' ;</pre>
将wordpress数据库中的cp_posts表导出到sql文件,就可以到正式站上去导入了。

然后就可以通过类型名字,在cp_term_relationships表里面插入关联记录了
<pre>` INSERT INTO `cp_term_relationships` (`object_id`, `term_id`, `listorder`, `status`)
 (
 SELECT `cp_posts`.`id` AS object_id, `term_id` , 0, 1
 FROM `cp_posts`
 INNER JOIN `cp_terms` ON `post_source` = `cp_terms`.`name`
 )