MySQL实现：移除col_name1中与col_name2重复的字符串词汇

针对你这个需求，我整理了一个适用于MySQL 8.0+的方案，能精准移除col_name1中和col_name2重复的词汇，直接看代码和解释：

首先先还原测试表结构和数据（方便验证效果）：

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    col_name1 VARCHAR(255),
    col_name2 VARCHAR(255)
);

INSERT INTO test_table VALUES
(1, 'hello world', 'hello test'),
(2, 'the stack over', 'over the flow'),
(3, 'hello from my sql fiddle', 'hello my sql');

接下来是核心的UPDATE语句，用递归CTE实现单词拆分、重复匹配和结果拼接：

WITH RECURSIVE split_col1 AS (
    SELECT 
        id,
        col_name1 AS original,
        SUBSTRING_INDEX(col_name1, ' ', 1) AS word,
        SUBSTRING(col_name1, LENGTH(SUBSTRING_INDEX(col_name1, ' ', 1)) + 2) AS remaining
    FROM test_table
    UNION ALL
    SELECT 
        id,
        original,
        SUBSTRING_INDEX(remaining, ' ', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 2)
    FROM split_col1
    WHERE remaining != ''
),
split_col2 AS (
    SELECT 
        id,
        SUBSTRING_INDEX(col_name2, ' ', 1) AS word,
        SUBSTRING(col_name2, LENGTH(SUBSTRING_INDEX(col_name2, ' ', 1)) + 2) AS remaining
    FROM test_table
    UNION ALL
    SELECT 
        id,
        SUBSTRING_INDEX(remaining, ' ', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 2)
    FROM split_col2
    WHERE remaining != ''
),
duplicate_words AS (
    SELECT DISTINCT s1.id, s1.word
    FROM split_col1 s1
    JOIN split_col2 s2 ON s1.id = s2.id AND s1.word = s2.word
),
filtered_words AS (
    SELECT 
        s1.id,
        GROUP_CONCAT(s1.word SEPARATOR ' ') AS filtered_col1
    FROM split_col1 s1
    LEFT JOIN duplicate_words dw ON s1.id = dw.id AND s1.word = dw.word
    WHERE dw.word IS NULL
    GROUP BY s1.id
)
UPDATE test_table t
JOIN filtered_words fw ON t.id = fw.id
SET t.col_name1 = fw.filtered_col1;

代码逻辑拆解 #

1. 拆分字符串：split_col1和split_col2两个递归CTE，分别把col_name1和col_name2按空格拆成单个单词，同时保留每条数据的id，确保后续匹配的准确性。
2. 识别重复词汇：duplicate_words通过关联两个拆分后的结果集，筛选出每个id下同时出现在两列中的词汇。
3. 生成目标字符串：filtered_words把col_name1中不在重复列表里的单词重新拼接成完整字符串。
4. 执行更新：最后通过JOIN操作，把处理后的字符串更新回原表的col_name1字段。

验证结果 #

执行完UPDATE后，查询表数据：

SELECT * FROM test_table;

会得到你期望的结果：

id | col_name1     | col_name2
---|---------------|------------------
1  | world         | hello test
2  | stack         | over the flow
3  | from fiddle   | hello my sql

注意事项 #

• 该方案要求MySQL版本为8.0及以上（支持递归CTE），如果是低版本MySQL，需要改用自定义字符串拆分函数来实现。
• 假设词汇之间仅用单个空格分隔，如果存在连续空格，建议先通过REPLACE(col_name1, ' ', ' ')替换为单个空格后再处理。

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