Export a list of posts, tags and categories from WordPress
Last Updated: May 6, 2019
This complex SQL query can generate a complete table of your WordPress posts, plus a comma-separated list of both categories and tags.
SELECT p.id, p.post_title, p.post_type, GROUP_CONCAT( DISTINCT( c.name ) ) AS categories, GROUP_CONCAT( DISTINCT( t.name ) ) AS tags FROM wp_posts p JOIN wp_term_relationships cr ON( p.id = cr.object_id ) JOIN wp_term_taxonomy ct ON( ct.term_taxonomy_id = cr.term_taxonomy_id AND ct.taxonomy = 'category' ) JOIN wp_terms c ON( ct.term_id = c.term_id ) JOIN wp_term_relationships tr ON( p.id = tr.object_id ) JOIN wp_term_taxonomy tt ON( tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'post_tag' ) JOIN wp_terms t ON( tt.term_id = t.term_id ) WHERE p.post_status = 'publish' GROUP BY p.id
Adapted from this post on StackOverflow.