如何在WordPress数据库中通过post_id获取object_id并查询Yoast SEO详情

对于文章（含自定义文章类型），Yoast的object_id直接等同于WordPress文章表（wp_posts）的ID（即post_id），对应wp_yoast_seo_meta表中object_type字段值为post。如果是分类、标签等分类法数据，object_id则对应分类表（wp_terms）的term_id，object_type值为term。

假设你的WordPress表前缀为wp_，获取post_id为123的文章SEO数据：

SELECT 
    pm.object_id AS post_id,
    pm.meta_title,
    pm.meta_description,
    pm.canonical,
    pm.primary_focus_keyword,
    pm.reading_time
FROM wp_yoast_seo_meta pm
WHERE pm.object_id = 123 
  AND pm.object_type = 'post';

同时获取已发布文章的基础信息与对应SEO数据：

SELECT 
    p.ID AS post_id,
    p.post_title,
    pm.meta_title,
    pm.meta_description,
    pm.canonical
FROM wp_posts p
LEFT JOIN wp_yoast_seo_meta pm 
    ON p.ID = pm.object_id 
    AND pm.object_type = 'post'
WHERE p.post_type = 'post' 
  AND p.post_status = 'publish';

Yoast的社交平台SEO数据存储在wp_postmeta表中，键名以_yoast_wpseo_开头，可关联查询：

SELECT 
    p.ID AS post_id,
    p.post_title,
    pm.meta_title,
    pm.meta_description,
    og.meta_value AS og_title,
    twitter.meta_value AS twitter_title
FROM wp_posts p
LEFT JOIN wp_yoast_seo_meta pm 
    ON p.ID = pm.object_id 
    AND pm.object_type = 'post'
LEFT JOIN wp_postmeta og
    ON p.ID = og.post_id 
    AND og.meta_key = '_yoast_wpseo_opengraph_title'
LEFT JOIN wp_postmeta twitter
    ON p.ID = twitter.post_id 
    AND twitter.meta_key = '_yoast_wpseo_twitter_title'
WHERE p.post_type = 'post' 
  AND p.post_status = 'publish';

注：请根据你的实际WordPress配置修改表前缀（如wp_可能改为wp_site1_）。

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