虽然 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');
全文字符串匹配
全文搜索查询由两个主要部分组成:
- 查询一组列
MATCH (col1, col2, ..)
- 对这些列使用的查询和搜索修饰符
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
列与查询匹配。所以它背后的过程是:
Jackie
Jackie Chan
查询产生一个结果:以菜命名的厨师Szechuan Noodles
- 第二次搜索从第一次搜索的结果中获取其他词,例如
Chan
、Szechuan
和 ,并在和列Noodles
上运行另一次搜索dish
chef
- 第二次搜索产生另外两个结果:与只有在行中的
Julie Chan (Soupy Noodles)
结果相比,同时具有Chan
和Noodles
在行中的结果具有更高的相关性。Ramen Noodles
Noodles
- 所有三个结果均按显示的相关性排序
InnoDB 和 MyISAM 数据库引擎都提供全文搜索。但是应该注意的是,全文搜索的最小字符数在 InnoDB 中为 3,在 MyISAM 中为 4。某些停用词,如on
,the
或it
在搜索时被忽略。您可以在此处找到停用词的总列表以及有关相同内容的更多信息。您也可以按照此过程在查询时忽略它们。
在搜索数据库中的大量数据时,全文搜索确实非常强大。搜索愉快!