MySQL正则表达式查询 - 忽略音调的搜索

Question

我希望查询一个葡萄酒名称的数据库，其中许多葡萄酒名称包含重音符号（但不是统一的方式，因此可能会输入相似的葡萄酒名称，有些带有重音符号，有些则没有）

基本查询如下：

SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugères[[:>:]]'

这将返回标题中包含“Faugères”的条目，但不包括“Faugeres”。

SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugeres[[:>:]]'

做相反的事情。

我之前想过类似这样的东西：

SELECT * 
FROM `table` 
WHERE `wine_name` REGEXP '[[:<:]]Faug[eèêéë]r[eèêéë]s[[:>:]]'

也许可以解决问题，但这样只能返回没有重音符号的结果。

该字段的排序规则为utf8_unicode_ci，根据我所读到的，应该是这样的。

有什么建议吗？！

- freestate

---

我也曾经有同样的问题。请看我的这个帖子：https://dev59.com/BZHea4cB1Zd3GeqPkxwk#34047990 - Dan

Answer 1

你运气不好：

警告

REGEXP和RLIKE操作符按字节方式工作，因此它们不支持多字节且可能在使用多字节字符集时产生意外结果。另外，这些操作符根据其字节值比较字符，即使给定排序将其视为相等，带重音的字符也可能不相等。

[[:<:]]和[[:>:]] regexp操作符是单词边界的标记。您可以通过LIKE操作符实现类似的功能，如下所示:

SELECT *
FROM `table`
WHERE wine_name = 'Faugères'
   OR wine_name LIKE 'Faugères %'
   OR wine_name LIKE '% Faugères'

正如您所看到的，它并不完全等同，因为我将单词边界的概念限制在空格上。添加更多用于其他边界的子句将会很混乱。

您也可以使用全文搜索（虽然它不是完全相同的），但您不能在InnoDB表中定义全文索引（尚未）。

你肯定很倒霉 :)

附录：截至MySQL 8.0，这已经发生了改变：

MySQL使用国际Unicode组件（ICU）实现正则表达式支持，提供完整的Unicode支持，并且是多字节安全的。（在MySQL 8.0.4之前，MySQL使用Henry Spencer的正则表达式实现，该实现以字节方式操作，不是多字节安全的。

- Álvaro González

---

哎呀...- 好的，那么如果我改成：WHERE wine_name LIKE '%Faugeres%'有什么缺点吗？我记不清我们一开始为什么要使用REGEXP了，但我想这可能与搜索整个单词而不是单词内的字符串有关，而上述的LIKE语句会做到这一点... - freestate

这个解决方案可能并不是很好，因为它无法处理单词前后有其他字符的情况，例如：´Faugères.´ ´Faugères!´ ´Faugères?´ ´(Faugères´以及许多其他变化形式。我正在寻找一个REGEXP，它使用单词边界但对重音不敏感。 - steps

仍然无法在mysql 8中成功。REGEXP '\bFaugeres\b'没有起作用。我的意思是不区分重音符号也不起作用。 - Linga

@Linga MySQL 8修复了多字节支持。正则表达式不应该以这种方式处理排序规则：è和e本质上是不同的字符。您可以尝试使用'\\bFaug[eèêéë]r[eèêéë]s\\b'。 - Álvaro González

Answer 2

因为 REGEXP 和 RLIKE 是面向字节的，所以您是否尝试过以下操作：

SELECT 'Faugères' REGEXP 'Faug(e|è|ê|é|ë)r(e|è|ê|é|ë)s';

这句话的意思是表达式中必须包含其中一个。请注意，我没有使用加号（+），因为它表示“一个或多个”。由于您只想要一个，因此不应使用加号。

- Mark Manning

---

这应该是答案。 - Linga

Answer 3

utf8_general_ci在排序时不区分重音/无重音。也许对于搜索也是如此。 此外，将REGEXP更改为LIKE。 REGEXP进行二进制比较。

- Alexander Taver

Answer 4

为了解决这个问题，我尝试了不同的方法，包括使用二进制关键字或latin1字符集，但都没有成功。
最终，考虑到这是一个MySql的bug，我最终替换了é和è字符，

像这样：

SELECT * 
FROM `table` 
WHERE replace(replace(wine_name, 'é', 'e'), 'è', 'e') REGEXP '[[:<:]]Faugeres[[:>:]]'

- user217447

---

这可能是低效的，因为它会为表中的每一行计算表达式。只有在读取整个表后才能应用条件。 - Lorenz Meyer

Answer 5

我有这个问题，并采纳了Álvaro上面的建议。但在我的情况下，它会错过那些搜索词是字符串中中间单词的实例。我选择了相当于：

SELECT *
FROM `table`
WHERE wine_name = 'Faugères'
   OR wine_name LIKE 'Faugères %'
   OR wine_name LIKE '% Faugères'
   OR wine_name LIKE '% Faugères %'

- Stephen S

Answer 6

我曾经遇到过同样的问题，尝试查找与以下模式之一匹配的每个记录：'copropriété'，'copropriete'，'COPROPRIÉTÉ'，'Copropri?t?'

REGEXP 'copropri.{1,2}t.{1,2} 对我有用。 基本上，.{1,2} 在每种情况下都应该有效，无论字符是1个还是2个字节编码。

解释：https://dev.mysql.com/doc/refman/5.7/en/regexp.html

警告
REGEXP和RLIKE运算符以字节方式工作，因此它们不支持多字节，并且可能会在使用多字节字符集时产生意外结果。此外，这些运算符按其字节值比较字符，即使给定排序将重音字符视为相等，它们也可能不相等。

- BigBud52

Answer 7

好的，我在寻找其他东西时偶然发现了这个问题。

这将返回true。

SELECT 'Faugères' REGEXP 'Faug[eèêéë]+r[eèêéë]+s';

希望能有所帮助。

在正则表达式中添加“+”表示查找一个或多个字符的出现次数。

- Seeknowevil