如何合并MySQL代码片段实现：匹配正则且不属于指定分类的文章添加至目标分类并移除原分类

Got it, let's work through this together. You need to add matching posts to Category A and remove them from Category B, but only if they meet two conditions: they match your regex pattern, and they aren't already in Category C. Here's how to combine your two code snippets into a cohesive, executable workflow:

First, let's clarify the values we're working with from your code:

• Category A: term_taxonomy_id = 141
• Category B: term_taxonomy_id = 160
• Category C: term_taxonomy_id = 161

Solution 1: Use a Transaction for Atomicity (Recommended) #

This ensures that both operations (add to A, remove from B) either complete fully or roll back if something fails—no partial updates.

START TRANSACTION;

-- Step 1: Add eligible posts to Category A
REPLACE INTO wp_term_relationships (term_taxonomy_id, object_id)
SELECT '141', ID
FROM wp_posts
WHERE 
  -- Match regex in title or content
  (post_title REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)' 
   OR post_content REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)')
  -- Only target posts
  AND post_type = 'post'
  -- Exclude posts already in Category C
  AND ID NOT IN (
    SELECT object_id 
    FROM wp_term_relationships 
    WHERE term_taxonomy_id = 161
  );

-- Step 2: Remove those same posts from Category B
DELETE FROM wp_term_relationships
WHERE term_taxonomy_id = 160
  AND object_id IN (
    SELECT ID
    FROM wp_posts
    WHERE 
      (post_title REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)' 
       OR post_content REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)')
      AND post_type = 'post'
      AND ID NOT IN (
        SELECT object_id 
        FROM wp_term_relationships 
        WHERE term_taxonomy_id = 161
      )
  );

COMMIT;

Solution 2: Use a Temporary Table for Efficiency #

If you're working with a large dataset, using a temporary table avoids running the regex match twice, which speeds things up:

-- Create a temp table to store IDs of eligible posts
CREATE TEMPORARY TABLE eligible_posts (
  post_id INT PRIMARY KEY
);

-- Populate the temp table with posts that meet your criteria
INSERT INTO eligible_posts (post_id)
SELECT ID
FROM wp_posts
WHERE 
  (post_title REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)' 
   OR post_content REGEXP '((?<!\\b[A-Z]|\\b[A-Z][A-Z])\\$|\\bAU?\\$|CA?N?\\$)[^\\d\\r\\n]?((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)?|((?:\\d{1,10}[,. ])*\\d{1,10})[ .]?(k)? ?(?:USD?\\b|dollar|cash|\\bbucks\\b|GBP|EURO?S?\\b|AUD\\b|CAD\\b|INR\\b)')
  AND post_type = 'post'
  AND ID NOT IN (
    SELECT object_id 
    FROM wp_term_relationships 
    WHERE term_taxonomy_id = 161
  );

-- Add eligible posts to Category A
REPLACE INTO wp_term_relationships (term_taxonomy_id, object_id)
SELECT '141', post_id FROM eligible_posts;

-- Remove eligible posts from Category B
DELETE FROM wp_term_relationships
WHERE term_taxonomy_id = 160
  AND object_id IN (SELECT post_id FROM eligible_posts);

-- Clean up (temp tables are auto-deleted when your session ends)
DROP TEMPORARY TABLE eligible_posts;

Key Notes: #

• The REPLACE command is safe here—it will update existing entries if the post is already in Category A, avoiding duplicates.
• We escaped backslashes in the regex (\\b instead of \b) because MySQL requires double escaping for regex special characters.
• The transaction approach ensures data consistency—if one operation fails, the other will roll back too.

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