mysql - 如何在 MariaDB/MySQL 中转义字符串以进行全文搜索

Question

某些字符(运算符)会影响 MariaDB 中的全文搜索行为。他们是 +-<>()~*" documentation 中描述了它们的功能.

我希望能够搜索包含这些运算符之一的单词，并且我希望 MariaDB 将其作为普通字符而不是运算符来处理。我该怎么做？

示例:

让我们创建带有全文索引的表:

CREATE TABLE users (username TEXT, FULLTEXT(username)) ENGINE=InnoDB;

INSERT INTO users(username) VALUES ('joseph'), ('jose'), ('jose*');

现在我想搜索恰好包含 jose* 的行:

SELECT * FROM users WHERE MATCH(username) AGAINST('jose*' IN BOOLEAN MODE);
+----------+
| username |
+----------+
| joseph   |
| jose     |
| jose*    |
+----------+

但我只想要 jose* 的行.当我尝试以我期望的方式转义该字符串时，结果相同。

SELECT * FROM users WHERE MATCH(username) AGAINST('jose\*' IN BOOLEAN MODE);
+----------+
| username |
+----------+
| joseph   |
| jose     |
| jose*    |
+----------+

SELECT * FROM users WHERE MATCH(username) AGAINST('jose\\*' IN BOOLEAN MODE);
+----------+
| username |
+----------+
| joseph   |
| jose     |
| jose*    |
+----------+

在 MariaDB/MySQL 中对全文搜索字符串进行转义的正确方法是什么？

Answer 1

全文搜索是一种有效搜索出现在(全文)文本中任何位置的单词(或单词开头)的工具。如果您的数据不包含分隔的“单词”(以任何您想要定义的方式)，则全文索引不是您完成任务的正确工具(因为它将完全无用)。默认情况下，* 是一个单词分隔符，就像空格一样(例如 'abc*def'，以及 'abc def'，都是两个单词，在全文索引中有两个单独的条目，其中都不包含 *)。您可以指定您想要的分隔符，但 MySQL 不支持通过在搜索表达式中转义它们来动态指定它；您需要在创建索引时执行此操作，因此索引实际上将包含 jose*，而不仅仅是 jose。

如果您没有单词(或一组非常有限的分隔符)，您可以使用例如username = 'jose*, username like 'jose*' 或类似的；或者，您可以使用 regular expressions ，这很慢，但是对于复杂的要求(例如，如果全文不适合您的情况)的后备工具，其中全文索引不是 usabel(和/或您不能更改配置以适应您的要求)。

要更改 MySQL 将哪些字符视为定界符，您可以更改字符映射，参见 Adding a Collation for Full-Text Indexing :

• 将新排序规则添加到 index.xml
• 将该排序规则添加到字 rune 件(例如 latin1.xml)，并编辑 ctype将特定字符定义为(非)定界符；仅对于 *，将其更改为“48 10 10 10 10 10 10 10 10 10 01 10 10 10 10 10”)；对您希望可搜索的所有字符执行此操作(但请再次记住，如果您没有至少一个剩余的分隔符，则全文搜索将毫无用处)。
• 重启后，对您的列使用此排序规则(例如 ... (username TEXT collat​​e 'latin1_fulltext_ci', ...)并重新创建全文索引，MySQL 会将这些字符包含到索引。
• 请记住，您需要在每台要使用此行为的服务器上执行此操作

现在以下三个搜索应该返回预期的结果:

... MATCH(username) AGAINST('"jose*"' IN BOOLEAN MODE);

... MATCH(username) AGAINST('jose*');

... MATCH(username) AGAINST('"jose*"');

"..." 将查找完全匹配(例如单词组合)；它的工作方式类似于转义，但不完全相同，因为它只适用于非定界符。

... MATCH(username) AGAINST('jose*' IN BOOLEAN MODE);

将不用于 InnoDB(它将被视为通配符)，但将用于 MyISAM(它们之间的一些细微差别之一)。

如果你真的想使用 bool 模式，但需要一个不同于*的通配符，你可以使用ft_boolean_syntax定义一个不同的通配符。 , 尽管由于 bug in InnoDB这也只适用于 MyISAM。它也是一个全局设置，因此会改变其他表(和数据库)中所有其他全文搜索的行为。您可能必须指定要使用此模式实现的目标，以查看是否有办法使全文搜索满足这些要求，但最终，您可能不得不退回到使用 like。