如何通过Post_id查询自定义分类法值并使用MySQL更新表

在WordPress站点中，已导入约100万条专业人员列表，仅70%完成索引，剩余未索引数据存入wp_leftover_pros_temp临时表。该表缺少**Specialties（专业）和Business Affiliations（商业关联）**字段，需从WordPress分类相关表中获取对应数据补全。

WordPress分类数据存储结构 #

分类数据分布在三个表中，结构示例如下：

1. wp_term_relationships表 #

object_id   term_taxonomy_id  term_order
      5705         53192          1
      5705         55596          1

2. wp_term_taxonomy表 #

term_taxonomy_id    term_id   taxonomy     description   parent  count
   53192               53186     hospitals                          121
   55596               55590     specialty                          57479

3. wp_terms表 #

term_id   name                      slug                      term_group    order
   53186     Texas Orthopedic Hospital texas-orthopedic-hospital 0             0
   55590     Physician Assistant       physician-assistant       0             0

当前问题 #

现有MySQL更新语句只能针对特定term_taxonomy_id更新专业值，示例如下：

UPDATE wp_leftover_pros_temp wp
JOIN wp_term_relationships wtr ON (wp.`post_id` = wtr.`object_id`)
JOIN wp_term_taxonomy wtt ON (wtr.`term_taxonomy_id` = wtt.`term_taxonomy_id`) 
JOIN wp_terms wt ON (wt.`term_id` = wtt.`term_id`)  
SET wp.specialty  = wt.name
WHERE wp.post_id = wtr.object_id AND wtr.term_taxonomy_id = '55596'

由于存在400余种专业，手动编写400余条更新语句效率极低，需实现按post_id匹配，批量更新所有专业人员对应的specialty字段。

解决方案 #

修改更新语句，通过taxonomy字段筛选专业类别的数据，无需指定具体term_taxonomy_id，即可批量完成更新：

UPDATE wp_leftover_pros_temp wp
JOIN wp_term_relationships wtr ON wp.post_id = wtr.object_id
JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
JOIN wp_terms wt ON wtt.term_id = wt.term_id
SET wp.specialty = wt.name
WHERE wtt.taxonomy = 'specialty';

特殊情况处理 #

如果存在同一post_id对应多个专业的情况，可使用GROUP_CONCAT合并多个专业名称，语句如下：

UPDATE wp_leftover_pros_temp wp
JOIN (
    SELECT wtr.object_id, GROUP_CONCAT(wt.name SEPARATOR ', ') AS specialties
    FROM wp_term_relationships wtr
    JOIN wp_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
    JOIN wp_terms wt ON wtt.term_id = wt.term_id
    WHERE wtt.taxonomy = 'specialty'
    GROUP BY wtr.object_id
) AS specialty_data ON wp.post_id = specialty_data.object_id
SET wp.specialty = specialty_data.specialties;

内容的提问来源于stack exchange，提问作者J.R.