MySQL问题：无法正确筛选含特殊字符的shop_name列数据

我有一张名为shops的表，包含shop_name列，数据如下：

我需要返回其中包含非空格类特殊字符的行，预期结果为：

Matt & Josh Sub
Lilly's Sub

我尝试了两条查询语句，但结果都不符合预期：

• 查询1：执行后未返回任何结果

select shop_name 
from shops 
where shop_name like '%[^0-9a-zA-Z]%';

• 查询2：执行后返回全部4行数据

select shop_name 
from shops 
where shop_name REGEXP '[^a-zA-Z0-9]';

问题分析 #

1. 查询1失效原因：
   LIKE操作符在MySQL等多数数据库中不支持[^...]这种正则风格的否定字符集，它会把[^0-9a-zA-Z]当作普通字符串进行匹配——也就是查找字面包含[^0-9a-zA-Z]的行，自然不会有结果。即便在支持LIKE通配符的SQL Server中，该语句也会把空格判定为"特殊字符"，不符合需求。

2. 查询2返回全部行的原因：
   REGEXP '[^a-zA-Z0-9]'会匹配任何包含非字母数字的字符，而所有行都包含空格（空格属于非字母数字），因此所有行都被选中了。

解决方案 #

调整正则表达式，将空格排除在"特殊字符"的判定之外，只匹配字母、数字、空格之外的字符：

SELECT shop_name 
FROM shops 
WHERE shop_name REGEXP '[^a-zA-Z0-9 ]';

如果是在SQL Server环境下，需要使用PATINDEX函数实现类似逻辑：

SELECT shop_name 
FROM shops 
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', shop_name) > 0;

以上查询会精准匹配包含&、'这类特殊字符的行，得到预期结果。

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