MySQL查询：筛选喜爱颜色未全部属于排除列表的记录

核心逻辑 #

过滤规则等价于：只要记录中存在任意一个喜爱颜色不在排除列表内，就保留该记录；仅当所有喜爱颜色都在排除列表中时，才丢弃记录。
利用字符串精确替换的思路实现，无需拆分字段、无需依赖高级SQL特性，全MySQL版本兼容：

1. 给每行的颜色字符串前后拼接逗号，把所有颜色包裹成,color1,color2,color3,的格式，避免子串误匹配（比如reen不会被错误匹配到green）
2. 把字符串中所有属于排除列表的颜色（带前后逗号的完整匹配段）替换成单个逗号
3. 把替换后字符串里的所有逗号删除，如果剩余内容长度大于0，说明存在未被排除的颜色，符合保留条件

代码示例 #

场景1：排除单个颜色purple #

SELECT * FROM favorites
WHERE LENGTH(
  REPLACE(
    REPLACE(CONCAT(',', colors, ','), ',purple,', ','),
    ',', ''
  )
) > 0;

执行后仅过滤Ryan的记录，其余全部返回，符合规则要求。

场景2：排除多个颜色yellow、green #

多排除一个颜色就多嵌套一层REPLACE即可：

SELECT * FROM favorites
WHERE LENGTH(
  REPLACE(
    REPLACE(
      REPLACE(CONCAT(',', colors, ','), ',yellow,', ','),
      ',green,', ','
    ),
    ',', ''
  )
) > 0;

执行后仅过滤James的记录，其余全部返回，符合规则要求。

场景3：排除多个颜色orange、black #

SELECT * FROM favorites
WHERE LENGTH(
  REPLACE(
    REPLACE(
      REPLACE(CONCAT(',', colors, ','), ',orange,', ','),
      ',black,', ','
    ),
    ',', ''
  )
) > 0;

执行时John的颜色串替换后会剩余green，长度大于0被保留，最终所有记录均返回，符合规则要求。

注意事项 #

• 新增排除颜色时，只需在现有REPLACE嵌套层中新增一层REPLACE(..., ',<待排除颜色名>,', ',')即可
• 禁止省略前后拼接逗号的步骤，否则会出现短字符串误匹配长颜色名的问题，比如颜色red会错误匹配到darkred导致判断失效
• 该写法仅依赖MySQL内置的字符串函数，无版本兼容问题，执行效率高于递归拆分、数字辅助表拆分等方案

附测试表结构与初始化数据：

CREATE TABLE IF NOT EXISTS `favorites` (
  `name` varchar(20) NOT NULL,
  `colors` varchar(20) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `favorites` (`name`, `colors`) VALUES
  ('Timmy', 'blue,yellow,green'),
  ('John', 'green,orange,black'),
  ('Alysha', 'red,purple,orange'),
  ('James', 'yellow,green'),
  ('Janet', 'reen'),
  ('Peter', 'purple,orange,blue'),
  ('Ryan', 'purple'),
  ('Tony', 'blue,red');

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