SQL语句批量删除wordpress没有文章与之关联的tags标签

三步操作，依次清理未被文章使用的标签相关记录，确保 wp_terms、wp_term_taxonomy 和 wp_term_relationships 三张表都干干净净。

第一步：删除未被文章使用的标签（post_tag）

DELETE FROM wp_terms 
WHERE term_id IN (
    SELECT term_id FROM (
        SELECT t.term_id
        FROM wp_terms t
        INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
        LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
        WHERE tt.taxonomy = 'post_tag'
        GROUP BY t.term_id
        HAVING COUNT(tr.object_id) = 0
    ) AS unused_tags
);

DELETE FROM wp_terms

WHERE term_id IN (

SELECT term_id FROM (

SELECT t.term_id

FROM wp_terms t

INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id

LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id

WHERE tt.taxonomy = 'post_tag'

GROUP BY t.term_id

HAVING COUNT(tr.object_id) = 0

) AS unused_tags

);

第二步：删除没有文章关联的标签分类法记录 wp_term_taxonomy

DELETE FROM wp_term_taxonomy
WHERE taxonomy = 'post_tag'
AND term_taxonomy_id NOT IN (
    SELECT DISTINCT term_taxonomy_id
    FROM wp_term_relationships
);

DELETE FROM wp_term_taxonomy

WHERE taxonomy = 'post_tag'

AND term_taxonomy_id NOT IN (

SELECT DISTINCT term_taxonomy_id

FROM wp_term_relationships

);

第三步（可选）：删除孤立的 term_relationships 记录（虽然极少发生）
这个是保险步骤，如果某些文章被删除但留下孤立的 term 关联，可清理：

DELETE FROM wp_term_relationships
WHERE object_id NOT IN (
    SELECT ID FROM wp_posts
);

DELETE FROM wp_term_relationships

WHERE object_id NOT IN (

SELECT ID FROM wp_posts

);

可选：一键查询将被删除的标签（备份或审核）

SELECT t.term_id, t.name
FROM wp_terms t
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'
GROUP BY t.term_id
HAVING COUNT(tr.object_id) = 0;

SELECT t.term_id, t.name

FROM wp_terms t

INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id

LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id

WHERE tt.taxonomy = 'post_tag'

GROUP BY t.term_id

HAVING COUNT(tr.object_id) = 0;