MySQL中LIKE与REGEXP查询返回超出指定条件结果如何解决

异常原因 #

这个异常和OR条件的逻辑本身没有关系，核心问题有两个：

1. 你没有限制and、with为独立单词，只要采集人姓名本身包含这两个字符串的片段就会被误匹配，比如姓名Anderson包含and、Witherspoon包含with，都会命中筛选规则，导致仅含单个采集人的记录被返回。
2. 第二条LIKE写法存在语法错误：&和;不是MySQL LIKE的特殊通配符，不需要转义。你添加#作为转义前缀之后，%#&%实际匹配的是包含#&组合的内容，单独的&、;根本无法命中，这两个筛选条件相当于完全失效。

优化方案 #

更推荐使用REGEXP搭配单词边界匹配的写法，既能精确匹配独立的and、with，也能正确识别&、;符号：

SELECT sp.barcode, sp.gard_collector
FROM specimens sp
WHERE barcode IS NOT NULL 
AND (
    sp.gard_collector REGEXP '&'
    OR sp.gard_collector REGEXP ';'
    OR sp.gard_collector REGEXP '[[:<:]]and[[:>:]]'
    OR sp.gard_collector REGEXP '[[:<:]]with[[:>:]]'
);

说明：[[:<:]]和[[:>:]]是MySQL正则支持的单词边界标识符，可以保证只匹配作为独立单词出现的and、with，不会命中姓名内部的字符串片段。如果你使用的是MySQL 8.0及以上版本，也可以用转义后的\\b作为单词边界：REGEXP '\\band\\b'。

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