MySQL 中的全文搜索（附示例）

虽然 MySQL 不是一个功能齐全的文本搜索引擎，但它有足够的技巧可用于在您的应用程序中实现基本搜索功能。让我们快速浏览一下。

首先，让我们打开 mysql 提示并创建一个新的数据库并调用它restaurant。

现在，我们可以继续创建一个TABLE来存储我们的记录。我们必须在这里小心，因为我们需要明确指定需要“全文索引”的字段。全文搜索查询仅对全文索引的字段有效。只能为 、 或 列创建VARCHAR全文CHAR索引TEXT。

CREATE TABLE food
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID
  dish            VARCHAR(120) NOT NULL,                # Name of the dish
  chef            VARCHAR(120) NOT NULL,                # Chef's name
  flavor          VARCHAR(100) NOT NULL,                # Flavor of the dish
  PRIMARY KEY     (id)                                  # Making the id the primary key

  FULLTEXT        (dish)                                # Full-text indexes dish
  FULLTEXT        (chef)                                # Full-text indexes chef
  FULLTEXT        (flavor)                              # Full-text indexes flavor
);

如果FULLTEXT索引未与表一起初始化，则可以稍后使用该ALTER命令启用它。

ALTER TABLE food ADD FULLTEXT (dish,chef);

请注意，(dish,chef)它将作为一对。这意味着您只能同时在两个字段上使用全文查询，而不能在任何一个字段上使用。您需要单独指定它们以便独立索引它们。同样，如果您单独索引它们，则不能与多个列进行全文匹配。

将值输入表

我们可以用普通的 SQL 来实现它，INSERT INTO

INSERT INTO food (dish, chef, flavor) VALUES
	('Ramen Noodles', 'Gordon Ramensay', 'Chinesey with a hint of sweetness and with a hint of bitterness'),
	('Szechuan Noodles', 'Jackie Chan', 'Extra Spicy with a tiny hint of tanginess'),
	('Soupy Noodles', 'Julie Chan', 'Cheesy and extremely wet with hints of pepper');

全文字符串匹配

全文搜索查询由两个主要部分组成：

1. 查询一组列MATCH (col1, col2, ..)
2. 对这些列使用的查询和搜索修饰符AGAINST (query [search_modifier])

MySQL 支持 3 种类型的搜索。我们将逐一介绍这三种模式。

• 自然语言全文搜索
• 布尔全文搜索
• 查询扩展搜索

自然语言搜索

自然语言全文搜索将搜索字符串解释为自由文本（人类语言），不需要特殊运算符。Chan一个例子是在chef列中查找。

SELECT * FROM food WHERE MATCH(chef) AGAINST ('Chan' IN NATURAL LANGUAGE MODE);

# Output
+----+------------------+-------------+-------------+
| id | dish             | chef        | flavor      |
+----+------------------+-------------+-------------+
|  2 | Szechuan Noodles | Jackie Chan | Extra Spicy |
|  3 | Soupy Noodles    | Julie Chan  | Cheesy      |
+----+------------------+-------------+-------------+
2 rows in set (0.003 sec)

必须注意，搜索不区分大小写。

我们还可以搜索多个单词。例如，让我们with hint针对该flavor列进行搜索，以识别包含子风味提示的菜肴。

SELECT * FROM food WHERE MATCH(flavor) AGAINST ('with hint' IN NATURAL LANGUAGE MODE);

# Output
+----+------------------+-----------------+-----------------------------------------------------------------+
| id | dish             | chef            | flavor                                                          |
+----+------------------+-----------------+-----------------------------------------------------------------+
|  1 | Ramen Noodles    | Gordon Ramensay | Chinesey with a hint of sweetness and with a hint of bitterness |
|  2 | Szechuan Noodles | Jackie Chan     | Extra Spicy with a tiny hint of tanginess                       |
+----+------------------+-----------------+-----------------------------------------------------------------+
2 rows in set (0.006 sec)

由于是全文匹配，所以单词（hints）的复数形式的记录没有匹配到。

搜索结果按相关性值排序。相关性的计算基于：

• 行中的单词数
• 该行中唯一单词的数量
• 集合中的总单词数
• 包含特定单词的文档（行）数。

零相关性意味着没有相似性。我们可以获得要显示的相关分数：

SELECT id, MATCH(flavor) AGAINST ('with hint' IN NATURAL LANGUAGE MODE) AS score FROM food;

# Output
+----+----------------------------+
| id | score                      |
+----+----------------------------+
|  1 | 0.000000003771856604828372 |
|  2 | 0.000000001885928302414186 |
|  3 |                          0 |
+----+----------------------------+
3 rows in set (0.004 sec)

第一行具有更好的相关性得分，因为with hint该行出现了两次，而且在第二行中不止一次。

布尔搜索

布尔搜索使用特殊查询语言的规则解释搜索字符串。查询可以包含要搜索的词，以及修改搜索结果必须如何显示的特殊运算符；检查匹配行中是否必须存在或不存在单词的运算符，或确定给定单词的权重。

我们BOOLEAN MODE在 MySQL 中使用来启用布尔全文搜索。特殊运算符位于单词的开头或结尾。前导+表示单词必须出现在行中，而前导-表示它不应该出现在行中。您可以在此处查看完整的运营商列表。

SELECT * FROM food WHERE  MATCH(chef) AGAINST ('+Chan -Julie' IN BOOLEAN MODE);

# Output
+----+------------------+-------------+-------------------------------------------+
| id | dish             | chef        | flavor                                    |
+----+------------------+-------------+-------------------------------------------+
|  2 | Szechuan Noodles | Jackie Chan | Extra Spicy with a tiny hint of tanginess |
+----+------------------+-------------+-------------------------------------------+
1 row in set (0.127 sec)

上面给出的查询要求 MySQL 搜索包含该词chan但不包含该词的行Julie，结果，我们只得到Jackie Chan输出。

查询扩展搜索

查询扩展搜索为您提供了一个强大的选项，可以更灵活地进行更深入的搜索。在自然语言模式下，仅列出匹配的匹配项。如果搜索查询是，Jackie那么只有Jackie Chan作为厨师的行被登记。但是，如果我们要对 query 进行查询扩展搜索chef，Jackie它会发现那Jackie Chan是结果匹配，然后再进行一次搜索Chan（以及出现在 chef 列中的任何其他单词，如果有的话）并添加Julie Chan到列表中。

搜索发生两次。在第一阶段，获取与用户提供的查询匹配的记录。在第二阶段，将第一组记录中的相关词用于另一轮搜索。

SELECT * FROM food WHERE  MATCH(chef) AGAINST ('Jackie' WITH QUERY EXPANSION);

# Output
+----+------------------+-------------+-----------------------------------------------+
| id | dish             | chef        | flavor                                        |
+----+------------------+-------------+-----------------------------------------------+
|  2 | Szechuan Noodles | Jackie Chan | Extra Spicy with a tiny hint of tanginess     |
|  3 | Soupy Noodles    | Julie Chan  | Cheesy and extremely wet with hints of pepper |
+----+------------------+-------------+-----------------------------------------------+
2 rows in set (0.015 sec)

由于查询扩展，您可以看到 Jackie 和 Julie Chans 都已列出。更有趣的是，如果我们这样做：

SELECT * FROM food WHERE  MATCH(dish,chef) AGAINST ('Jackie' WITH QUERY EXPANSION);

# Output
+----+------------------+-----------------+-----------------------------------------------------------------+
| id | dish             | chef            | flavor                                                          |
+----+------------------+-----------------+-----------------------------------------------------------------+
|  2 | Szechuan Noodles | Jackie Chan     | Extra Spicy with a tiny hint of tanginess                       |
|  3 | Soupy Noodles    | Julie Chan      | Cheesy and extremely wet with hints of pepper                   |
|  1 | Ramen Noodles    | Gordon Ramensay | Chinesey with a hint of sweetness and with a hint of bitterness |
+----+------------------+-----------------+-----------------------------------------------------------------+
3 rows in set (0.013 sec)

我们得到所有三行！这是因为我们将dish和chef列与查询匹配。所以它背后的过程是：

1. JackieJackie Chan查询产生一个结果：以菜命名的厨师Szechuan Noodles
2. 第二次搜索从第一次搜索的结果中获取其他词，例如Chan、Szechuan和 ，并在和列Noodles上运行另一次搜索dishchef
3. 第二次搜索产生另外两个结果：与只有在行中的Julie Chan (Soupy Noodles)结果相比，同时具有Chan和Noodles在行中的结果具有更高的相关性。Ramen NoodlesNoodles
4. 所有三个结果均按显示的相关性排序

InnoDB 和 MyISAM 数据库引擎都提供全文搜索。但是应该注意的是，全文搜索的最小字符数在 InnoDB 中为 3，在 MyISAM 中为 4。某些停用词，如on,the或it在搜索时被忽略。您可以在此处找到停用词的总列表以及有关相同内容的更多信息。您也可以按照此过程在查询时忽略它们。

在搜索数据库中的大量数据时，全文搜索确实非常强大。搜索愉快！