Drupal7博客数据迁移：合并两条SELECT语句生成目标结果表

你从url_alias提取的nodeId对应node表的nid（Drupal默认路径node/[nid]里的数字是节点ID，和修订版本ID vid无关），直接用这个字段做关联键即可。

关联查询语句（兼容所有MySQL版本） #

SELECT 
  t1.nid,
  t1.vid,
  t1.title,
  t2.body_value AS body,
  t3.slug
FROM `node` AS t1
LEFT JOIN `field_revision_body` AS t2 ON t1.vid = t2.revision_id
-- 关联url_alias处理后的子查询
LEFT JOIN (
  SELECT 
    CASE WHEN url_alias.alias LIKE "%news/%" THEN REPLACE(url_alias.alias, "news/", "") END AS slug, 
    CASE WHEN url_alias.source LIKE "%node/%" THEN REPLACE(url_alias.source, "node/", "") END AS nodeId 
  FROM url_alias 
  WHERE url_alias.alias LIKE "news/%" AND url_alias.source LIKE "%node/%"
) AS t3 ON t1.nid = t3.nodeId
WHERE t1.type = 'blog';

注意事项 #

• 如果你只需要保留同时存在slug的博客记录，把LEFT JOIN t3改成INNER JOIN t3即可
• 如果存在同一个nid对应多个alias的情况，可以在子查询里加GROUP BY nodeId或者用DISTINCT去重

小提示：如果出现关联不到的情况，可能是nodeId为字符串类型和nid的数值类型不匹配，把关联条件改成ON t1.nid = CAST(t3.nodeId AS UNSIGNED)即可。

直接插入目标表的语句 #

假设你已经提前建好目标表blog_migrate，字段对应nid、vid、title、body、slug，直接在查询前加INSERT INTO即可：

INSERT INTO blog_migrate (nid, vid, title, body, slug)
SELECT 
  t1.nid,
  t1.vid,
  t1.title,
  t2.body_value AS body,
  t3.slug
FROM `node` AS t1
LEFT JOIN `field_revision_body` AS t2 ON t1.vid = t2.revision_id
LEFT JOIN (
  SELECT 
    CASE WHEN url_alias.alias LIKE "%news/%" THEN REPLACE(url_alias.alias, "news/", "") END AS slug, 
    CASE WHEN url_alias.source LIKE "%node/%" THEN REPLACE(url_alias.source, "node/", "") END AS nodeId 
  FROM url_alias 
  WHERE url_alias.alias LIKE "news/%" AND url_alias.source LIKE "%node/%"
) AS t3 ON t1.nid = t3.nodeId
WHERE t1.type = 'blog';

内容的提问来源于stack exchange，提问作者user1042800