MATCH AGAINST 在 MariaDB 中的工作原理

在MariaDB中，是一种特殊的结构，用于对全文索引执行全文搜索。MATCH AGAINST

语法

语法是这样的：

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

示例

假设我们有一个名为 Products 的表，其中包含以下数据：

此表在其列上具有全文索引。这意味着我们可以用来对该列进行全文搜索。ProductDescription``MATCH AGAINST

例：

SELECT 
    ProductId AS "Id",
    ProductName,
    ProductDescription
FROM Products 
WHERE MATCH(ProductDescription) AGAINST('includes')
ORDER BY ProductId ASC;

输出：

+----+--------------------------+-----------------------------------------+
| Id | ProductName              | ProductDescription                      |
+----+--------------------------+-----------------------------------------+
|  1 | Left handed screwdriver  | Purple. Includes left handed carry box. |
|  2 | Right handed screwdriver | Blue. Includes right handed carry box.  |
|  6 | Chainsaw                 | Orange. Includes spare fingers.         |
+----+--------------------------+-----------------------------------------+

获取分数

我们可以包含在列表中，以便在搜索的列中返回关键字的相关性分数：MATCH AGAINST``SELECT

SELECT 
    ProductDescription,
    MATCH(ProductDescription) AGAINST ('includes') AS Score
FROM Products 
WHERE MATCH(ProductDescription) AGAINST ('includes')
ORDER BY Score DESC;

输出：

+-----------------------------------------+---------------------+
| ProductDescription                      | Score               |
+-----------------------------------------+---------------------+
| Orange. Includes spare fingers.         |  0.4883610010147095 |
| Blue. Includes right handed carry box.  |  0.4883610010147095 |
| Purple. Includes left handed carry box. | 0.48305025696754456 |
+-----------------------------------------+---------------------+

在这个示例中，我们还使用 anclause 按分数降序排序（即最相关的在前）。ORDER BY

没有全文索引的列

前面的示例起作用的原因是因为我之前在列上创建了一个全文索引。如果我没有这样做，我会收到一个错误。ProductDescription

以下是当我们尝试对没有全文索引的列使用 USE 时发生的情况：MATCH AGAINST

SELECT 
    ProductId,
    ProductName,
    ProductPrice
FROM Products 
WHERE MATCH(ProductName) AGAINST('screwdriver')
ORDER BY ProductId ASC;

Result:

ERROR 1191 (HY000): Can’t find FULLTEXT index matching the column list

Let’s add a full-text index on the column:ProductName

ALTER TABLE Products
ADD FULLTEXT(ProductName);

Now run the query again:

SELECT 
    ProductId,
    ProductName,
    ProductPrice
FROM Products 
WHERE MATCH(ProductName) AGAINST('screwdriver')
ORDER BY ProductId ASC;

输出：

+-----------+--------------------------+--------------+
| ProductId | ProductName              | ProductPrice |
+-----------+--------------------------+--------------+
|         1 | Left handed screwdriver  |        25.99 |
|         2 | Right handed screwdriver |        25.99 |
+-----------+--------------------------+--------------+

这次成功了。

多列上的全文索引

我们可以在多个列上添加全文索引。

例：

ALTER TABLE Products
ADD FULLTEXT(ProductName, ProductDescription);

现在我们可以针对该全文索引运行。MATCH AGAINST

SELECT 
    ProductId AS Id, 
    ProductName, 
    ProductDescription
FROM Products 
WHERE MATCH(ProductName, ProductDescription) AGAINST ('weight')
OR MATCH(ProductName, ProductDescription) AGAINST ('ceramic')
ORDER BY Id ASC;

输出：

+----+---------------------------------+---------------------------------------+
| Id | ProductName                     | ProductDescription                    |
+----+---------------------------------+---------------------------------------+
|  3 | Long Weight (blue)              | Approximate 45 minute waiting period. |
|  4 | Long Weight (green)             | Approximate 30 minute waiting period. |
|  8 | Bottomless Coffee Mugs (4 Pack) | Brown ceramic with solid handle.      |
+----+---------------------------------+---------------------------------------+