MySQL中如何过滤字符串子串？保留指定分类值的语法求助

你需要将data表的Genres列中每行的多值字符串，仅保留Classics、Fiction、Literature、Young Adult、Fantasy这几个值，以下是两种可行的SQL方案：

方法一：字符串替换法（适合不需要的值明确且数量少的场景） #

通过嵌套REPLACE函数逐个移除不需要的值，最后清理多余的逗号和空格：

UPDATE data
SET Genres = REGEXP_REPLACE(TRIM(BOTH ', ' FROM 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                Genres,
                'Historical Fiction', ''), 'School', ''), 'Historical', ''), 'Magic', ''), 'Childrens', ''),
            'Middle Grade', ''), 'Romance', ''), 'Audiobook', ''), 'Nonfiction', ''), 'History', ''),
        'Biography', ''), 'Memoir', ''), 'Holocaust', ''), 'Dystopia', ''), 'Politics', ''
), ',{2,}', ',');

注意：这种方法需要把所有不需要的值都列出来替换，适合简单场景。

方法二：拆分-筛选-合并法（通用场景，推荐） #

利用递归CTE拆分字符串为单个值，筛选出需要保留的类别后重新合并，适合复杂场景：

-- 假设表有主键id用于标识每行，若无主键可参考下文调整
WITH RECURSIVE genre_split AS (
    -- 初始拆分第一组值
    SELECT 
        id,
        TRIM(SUBSTRING_INDEX(Genres, ',', 1)) AS genre,
        TRIM(SUBSTRING(Genres, LENGTH(SUBSTRING_INDEX(Genres, ',', 1)) + 2)) AS remaining
    FROM data
    WHERE Genres IS NOT NULL AND Genres != ''
    
    UNION ALL
    
    -- 递归拆分剩余字符串
    SELECT 
        id,
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)) AS genre,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)) AS remaining
    FROM genre_split
    WHERE remaining IS NOT NULL AND remaining != ''
),
filtered_genres AS (
    -- 筛选需要保留的类别
    SELECT id, genre
    FROM genre_split
    WHERE genre IN ('Classics', 'Fiction', 'Literature', 'Young Adult', 'Fantasy')
)
-- 合并筛选后的值并更新原表
UPDATE data d
JOIN (
    SELECT id, GROUP_CONCAT(genre SEPARATOR ', ') AS new_genres
    FROM filtered_genres
    GROUP BY id
) f ON d.id = f.id
SET d.Genres = f.new_genres;

无主键时的调整方案 #

如果表没有主键，用ROW_NUMBER()生成临时行标识：

WITH RECURSIVE genre_split AS (
    SELECT 
        ROW_NUMBER() OVER () AS row_id,
        TRIM(SUBSTRING_INDEX(Genres, ',', 1)) AS genre,
        TRIM(SUBSTRING(Genres, LENGTH(SUBSTRING_INDEX(Genres, ',', 1)) + 2)) AS remaining,
        Genres AS original
    FROM data
    WHERE Genres IS NOT NULL AND Genres != ''
    
    UNION ALL
    
    SELECT 
        row_id,
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)) AS genre,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)) AS remaining,
        original
    FROM genre_split
    WHERE remaining IS NOT NULL AND remaining != ''
),
filtered_genres AS (
    SELECT row_id, genre
    FROM genre_split
    WHERE genre IN ('Classics', 'Fiction', 'Literature', 'Young Adult', 'Fantasy')
),
merged_genres AS (
    SELECT row_id, GROUP_CONCAT(genre SEPARATOR ', ') AS new_genres
    FROM filtered_genres
    GROUP BY row_id
)
UPDATE data d
JOIN (
    SELECT m.new_genres, g.original
    FROM merged_genres m
    JOIN genre_split g ON m.row_id = g.row_id
    GROUP BY m.row_id, g.original
) f ON d.Genres = f.original
SET d.Genres = f.new_genres;

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