MySQL REGEXP 查询 - 不区分重音的搜索

Question

我想查询一个酒名数据库，其中很多都包含重音符号(但不是以统一的方式，所以类似的 Wine 可能带有或不带有重音符号)

基本查询如下所示:

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

这将返回标题中包含“Faugeres”但不包含“Faugeres”的条目

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

相反。

我的想法是这样的:

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

可能会成功，但这只会返回没有重音符号的结果。

该字段整理为 utf8_unicode_ci，根据我的阅读，它应该是这样的。

有什么建议吗？!

Answer 1

You're out of luck :

Warning

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

[[:<:]]和 [[:>:]]正则表达式运算符是单词边界的标记。使用 LIKE 可以达到的最接近的值运营商是这一行的东西:

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

正如您所见，它并不完全等价，因为我将单词边界的概念限制为空格。为其他边界添加更多子句将是一团糟。

您也可以使用全文搜索(虽然不一样)，但您不能在 InnoDB 表中定义全文索引(目前)。

你肯定不走运:)

---

附录:此has changed从 MySQL 8.0 开始:

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe.