In this tutorial we will learn to export all products and their custom variations from WooCommerce using a MySQL query, Running a plugin or exporting a basic one from WooCommerce can get PHP heavy and slow to run if you have a lot of products in your store. A faster way is to run a MySQL query in phpMyAdmin and then export it to CSV.
As you can execute the following SQL query against your WooCommerce database.
SELECT p.ID, p.post_title AS product_title, GROUP_CONCAT(DISTINCT t.name) AS categories,
pm.meta_value AS price, pm2.meta_value AS regular_price,
GROUP_CONCAT(DISTINCT pa.meta_value ORDER BY pa.meta_key SEPARATOR '|') AS attributes,
GROUP_CONCAT(DISTINCT pv.meta_value ORDER BY pv.meta_key SEPARATOR '|') AS variations
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_price'
JOIN wp_postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_regular_price'
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
LEFT JOIN wp_postmeta pa ON p.ID = pa.post_id AND pa.meta_key LIKE 'attribute_%'
LEFT JOIN wp_postmeta pv ON p.ID = pv.post_id AND pv.meta_key LIKE 'attribute_%'
WHERE p.post_type IN ('product', 'product_variation') AND p.post_status = 'publish'
GROUP BY p.ID;
This query returns the Product ID, Product Title, Categories (as a comma-separated list), Price, Regular Price, Attributes (as a pipe-separated list), and Variants (as a pipe-separated list) for each product, and it’s Received in the form of a done list). Variations. Note that in WooCommerce custom variations are stored as separate posts with the post type ‘product_variation’. The above query includes both the main products and their custom variations.
