• 前置条件

    1. 安装完毕halo，数据库使用mysql
    2. wordpress数据库命名为wp
    3. 以下脚本在数据库halo内执行

  • 迁移脚本

  1. 导入文章数据

INSERT INTO posts
SELECT
	0 AS `type`	-- 类型
	,wpost.id AS id
	,wpost.post_date AS create_time	-- 文章创建时间
	,0 AS deleted
	,wpost.post_modified AS update_time	-- 文章更新时间
	,0 AS create_from
	,0 AS disallow_comment	-- 评论状态
	,CURRENT_TIMESTAMP AS edit_time	-- 文章编辑时间
	,wpost.post_content AS format_content	-- 格式化后内容
	,0 AS likes
	,'' AS original_content	-- markdown源
	,'' AS password
	,(CASE WHEN wpost.post_status = 'publish' THEN 0 ELSE 1 END) AS status	-- 文章状态
	,'' AS summary	-- 摘要
	,'' AS template	-- 模板
	,'' AS thumbnail
	,wpost.post_title AS title
	,0 AS top_priority	-- 置顶
	,concat('/wp/',year(wpost.post_date),'/',LPAD(month(wpost.post_date), 2, 0),'/',LPAD(DAY(wpost.post_date),2,0),'/',wpost.id,'.html') AS url	-- 文章URL
	,0 AS visits
FROM
	wp.wp_posts wpost
WHERE wpost.post_parent = 0
AND wpost.post_type != 'nav_menu_item'
AND wpost.post_type != 'page'

  1. 导入标签数据

INSERT INTO tags
SELECT 
	cterms.term_id AS id
	,CURRENT_TIMESTAMP AS create_time
	,0 AS deleted
	,CURRENT_TIMESTAMP AS update_time
	,cterms.name AS name
	,CONCAT(REPLACE(unix_timestamp(current_timestamp(3)),'.',''),cterms.term_id) AS slug_name	-- 使用官方程序请用此语句
	-- ,cterms.slug AS slug_name	-- 保持wp标签slug请用此语句
 	FROM wp.wp_terms cterms

  1. 导入标签&文章关联数据

INSERT INTO post_tags(create_time,deleted,update_time,post_id,tag_id)
SELECT 
	CURRENT_TIMESTAMP AS create_time	
	,0 AS deleted
	,CURRENT_TIMESTAMP AS update_time
	,xterms.object_id AS post_id
	,taxonomy.term_id AS tag_id 
	FROM wp.wp_term_relationships xterms 
	JOIN wp.wp_term_taxonomy taxonomy
	ON xterms.term_taxonomy_id = taxonomy.term_taxonomy_id
	WHERE taxonomy='post_tag'

  1. 导入目录数据

INSERT INTO categories
SELECT
	xterms.term_id AS id
	,CURRENT_TIMESTAMP AS create_time	
	,0 AS deleted
	,CURRENT_TIMESTAMP AS update_time
	,xterms.name AS description
	,xterms.name AS name
	,taxonomy.parent AS parent_id
	,xterms.slug AS slug_name
	FROM wp.wp_terms xterms 
	JOIN wp.wp_term_taxonomy taxonomy
	ON xterms.term_id = taxonomy.term_id
	WHERE taxonomy='category'

  1. 关联文章&目录

INSERT INTO post_categories(create_time,deleted,update_time,category_id,post_id)	
	SELECT
CURRENT_TIMESTAMP AS create_time,
0 AS deleted,
CURRENT_TIMESTAMP AS update_time,
halodb.categories.id AS id,
wp.relationships.object_id AS post_id
FROM
wp.wp_term_relationships AS relationships
JOIN wp.wp_term_taxonomy
ON wp.relationships.term_taxonomy_id = wp.wp_term_taxonomy.term_taxonomy_id 
JOIN halodb.categories
ON halodb.categories.id = wp.wp_term_taxonomy.term_id

  1. 导入评论数据

INSERT INTO comments
SELECT 
	0 AS `type`
	,kcomment.comment_ID AS id
	,kcomment.comment_date AS create_time
	,0 AS deleted
	,kcomment.comment_date AS update_time
	,kcomment.comment_author AS author
	,kcomment.comment_author_url AS author_url
	,kcomment.comment_content AS content
	,kcomment.comment_author_email AS email
	,'' AS gravatar_md5
	,kcomment.comment_author_IP AS ip_address
	,(CASE WHEN kcomment.user_id = 1 THEN 1 ELSE 0 END) AS is_admin
	,kcomment.comment_parent AS parent_id
	,kcomment.comment_post_ID AS post_id
	,(CASE WHEN kcomment.comment_approved = 'spam' THEN 2 ELSE 0 END) AS status	-- 判断垃圾评论
	,0 AS top_priority
	,kcomment.comment_agent AS user_agent
	FROM wp.wp_comments kcomment

打完收工：）