如何仅用MySQL（含phpMyAdmin场景）关联展示WordPress所有文章及分类？

Hey there! If you need to fetch all WordPress posts along with their linked categories in a single result table using pure MySQL (fully compatible with phpMyAdmin), here's a ready-to-use query based on WordPress's default database schema:

SELECT
    p.ID AS id_post,
    p.post_author,
    p.post_title,
    p.post_content,
    t.term_id AS category_id,
    t.name AS category_title
FROM
    wp_posts p
LEFT JOIN
    wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN
    wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN
    wp_terms t ON tt.term_id = t.term_id
WHERE
    p.post_type = 'post'
    AND p.post_status = 'publish'
    AND tt.taxonomy = 'category'
ORDER BY
    p.ID ASC;

Key Notes: #

• Table Prefix: Replace wp_ with your actual WordPress database table prefix (check your wp-config.php file if you're unsure what this is).
• Filtering: The query only includes published posts (post_status = 'publish') and excludes other post types (like pages) with post_type = 'post'. Adjust these clauses if you need to include drafts, pages, or custom post types.
• Multiple Categories: If a post is assigned to more than one category, it will show up as a separate row for each category association. If you prefer to combine all categories for a post into a single comma-separated column, use this modified query:

SELECT
    p.ID AS id_post,
    p.post_author,
    p.post_title,
    p.post_content,
    GROUP_CONCAT(t.term_id SEPARATOR ', ') AS category_ids,
    GROUP_CONCAT(t.name SEPARATOR ', ') AS category_titles
FROM
    wp_posts p
LEFT JOIN
    wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN
    wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN
    wp_terms t ON tt.term_id = t.term_id
WHERE
    p.post_type = 'post'
    AND p.post_status = 'publish'
    AND tt.taxonomy = 'category'
GROUP BY
    p.ID
ORDER BY
    p.ID ASC;

How It Works: #

• wp_posts: Pulls the core post data (ID, author, title, content).
• wp_term_relationships: Acts as the bridge between posts and their taxonomy entries.
• wp_term_taxonomy: Ensures we only target categories (excludes tags, custom taxonomies, etc. via tt.taxonomy = 'category').
• wp_terms: Provides the actual category IDs and names.

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