MySQL中如何批量删除逗号分隔字符串内的所有Pear数据？

针对你目前的需求——不用重复写20次REPLACE语句来删除所有颜色的Pear条目，我推荐两种更高效的方案，分别适用于不同的数据库版本，能彻底解决逗号处理和多颜色匹配的问题：

方案1：字符串拆分-过滤-重组（推荐，可靠且兼容性强） #

这个思路是把逗号分隔的字符串拆成独立元素，过滤掉所有包含Pear的元素，再重新拼接成新的字符串，从根源上避免逗号残留和漏处理的问题。

MySQL 8.0+ 版本代码 #

-- 更新数据：拆分后过滤掉含Pear的元素，再重组
UPDATE fruits f
INNER JOIN (
    SELECT 
        id,
        GROUP_CONCAT(item SEPARATOR ',') AS cleaned_fruit_data
    FROM fruits,
         JSON_TABLE(
             -- 把逗号分隔字符串转成JSON数组，方便拆分
             CONCAT('["', REPLACE(fruit_data, ',', '","'), '"]'),
             '$[*]' COLUMNS(item VARCHAR(255) PATH '$')
         ) AS split_items
    -- 过滤掉所有包含Pear的条目
    WHERE item NOT LIKE '%Pear'
    GROUP BY id
) AS filtered ON f.id = filtered.id
SET f.fruit_data = filtered.cleaned_fruit_data;

-- 删除处理后为空的行
DELETE FROM fruits WHERE fruit_data = '';

PostgreSQL 版本代码 #

-- 更新数据：拆分数组、过滤、重新拼接
UPDATE fruits
SET fruit_data = (
    SELECT string_agg(item, ',')
    FROM unnest(string_to_array(fruit_data, ',')) AS item
    WHERE item NOT LIKE '%Pear'
);

-- 删除空值或空字符串的行
DELETE FROM fruits WHERE fruit_data IS NULL OR fruit_data = '';

方案2：正则表达式替换（适合支持正则替换的数据库） #

如果你的数据库支持正则表达式替换（比如MySQL 8.0+、PostgreSQL、Oracle等），可以用正则一次性匹配所有颜色的Pear条目，同时处理前后的逗号：

MySQL 8.0+ 版本代码 #

-- 第一步：替换所有含Pear的条目，同时清理多余逗号
UPDATE fruits
SET fruit_data = TRIM(BOTH ',' FROM 
    REGEXP_REPLACE(fruit_data, '(^|,)[^,]*Pear(,|$)', '', 1, 0)
)
WHERE fruit_data REGEXP '[^,]*Pear';

-- 第二步：删除空行
DELETE FROM fruits WHERE fruit_data = '';

这里的正则(^|,)[^,]*Pear(,|$)会匹配三种情况：

• 开头的Pear条目（比如redPear,xxx）
• 中间的Pear条目（比如xxx,greenPear,xxx）
• 结尾的Pear条目（比如xxx,yellowPear）
  TRIM(BOTH ',' FROM ...)用来清理替换后可能出现的首尾逗号。

为什么不推荐原来的REPLACE方案？ #

原来的方法需要针对每种颜色写三次REPLACE，不仅重复劳动，还容易遗漏颜色；而且处理逗号时很容易出现残留（比如替换后出现apple,,banana这种连续逗号的情况），后续还要额外清理，效率很低。

内容的提问来源于stack exchange，提问作者Roberto Sepúlveda Bravo