針對 WordPress 資料庫結構,整理 14 條高頻維運 SQL,涵蓋密碼重置、批量更新、垃圾清理、資料替換等場景,直接在 phpMyAdmin 或命令列執行即可。
重設管理員密碼
UPDATE wp_users SET user_pass = MD5('新密碼') WHERE user_login = 'admin' LIMIT 1;
批量變更文章作者
UPDATE wp_posts SET post_author = 新用戶ID WHERE post_author = 舊用戶ID;
刪除文章修訂版本及關聯資料
DELETE a, b, c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON a.ID = b.object_id
LEFT JOIN wp_postmeta c ON a.ID = c.post_id
WHERE a.post_type = 'revision';
批量刪除待審評論
DELETE FROM wp_comments WHERE comment_approved = '0';
查詢未使用的標籤
SELECT * FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id
WHERE wtt.taxonomy = 'post_tag' AND wtt.count = 0;
查找並替換字串(通用)
UPDATE table_name SET field_name = REPLACE(field_name, '舊字串', '新字串');
匯出評論者唯一 Email 清單
SELECT DISTINCT comment_author_email FROM wp_comments;
一次性停用所有外掛
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
刪除所有標籤(保留分類)
DELETE a, b, c
FROM wp_terms a
LEFT JOIN wp_term_taxonomy c ON a.term_id = c.term_id
LEFT JOIN wp_term_relationships b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE c.taxonomy = 'post_tag' AND c.count = 0;
找出孤立的 Post Meta(無對應文章)
SELECT * FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;
關閉舊文章評論功能
UPDATE wp_posts SET comment_status = 'closed'
WHERE post_date < '2020-01-01' AND post_status = 'publish';
批量替換評論者網址
UPDATE wp_comments SET comment_author_url = REPLACE(comment_author_url, 'https://old.com', 'https://new.com');
批量替換評論者 Email
UPDATE wp_comments SET comment_author_email = REPLACE(comment_author_email, 'old@mail.com', 'new@mail.com');
刪除含特定網址的垃圾評論
DELETE FROM wp_comments WHERE comment_author_url LIKE '%spam-site%';
mysqldump 或備份外掛)。FOREIGN KEY 約束不會阻擋操作。