All Products
Search

This Product

    AnalyticDB:Full-text search

    Document Center

    AnalyticDB:Full-text search

    Last Updated:Aug 24, 2026

    Use the full-text index functions match() against(), match() fuzzy(), and match() phrase() to run full-text searches, and highlight the matched keywords in the results.

    Prerequisites

    A full-text index is created. For instructions, see Create a full-text index.

    Sample data

    All examples in this topic use the sample table tbl_fulltext_demo. Each full-text index uses a different analyzer. Run the following statements to create the table and insert the sample data.

    /* RC_DDL_ENGINE_REWRITE_XUANWUV2=false */
CREATE TABLE `tbl_fulltext_demo` (
  `id` INT,
  `content` VARCHAR,
  `content_alinlp` VARCHAR,
  `content_ik` VARCHAR,
  `content_standard` VARCHAR,
  `content_ngram` VARCHAR,
  FULLTEXT INDEX fidx_c(`content`),  -- Default tokenizer
  FULLTEXT INDEX fidx_alinlp(`content_alinlp`) WITH ANALYZER alinlp,-- AliNLP tokenizer
  FULLTEXT INDEX fidx_ik(`content_ik`) WITH ANALYZER ik,-- IK tokenizer
  FULLTEXT INDEX fidx_standard(`content_standard`) WITH ANALYZER standard,-- Standard tokenizer
  FULLTEXT INDEX fidx_ngram(`content_ngram`) WITH ANALYZER ngram,-- Ngram tokenizer
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);

INSERT INTO tbl_fulltext_demo(id, content, content_alinlp, content_ik, content_standard, content_ngram) 
VALUES(1, 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services', 'Customers Need Better Products and Services')
,(2, 'Wuhan Changjiang Bridge','Wuhan Changjiang Bridge','Wuhan Changjiang Bridge','Wuhan Changjiang Bridge', 'Wuhan Changjiang Bridge')
,(3, 'Hangzhou, Zhejiang Province','Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province')
,(4, 'User Values and Commercial Values of Products','User Values and Commercial Values of Products', 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products', 'User Values and Commercial Values of Products');

    Usage notes

    • Full-text index functions support the special characters +-&|!(){}[]^"~*?:\/, but you must escape them with \\.

      For example, to search for data that contains Spring/Scenery, the incorrect statement is match(content) against('Spring / Scenery' ) and the correct statement is match(content) against('Spring \\/ Scenery').

    • Full-text index functions do not support the =, !=, between, is null, is not null, or like operators.

    • For historical data or data migrated from other databases, you must execute BUILD TABLE tablename force=true after creating a full-text index to make the index take effect. Otherwise, MATCH AGAINST queries will return errors. You can check the BUILD task status in INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK. A status of FINISH indicates that the task is complete.

    Choose a search function

    Full-text search provides three search functions. Choose one based on your scenario:

    Scenario

    Function

    Match keywords or exact phrases, with AND, OR, and NOT logic.

    MATCH() AGAINST()

    Tolerate typos or spelling errors in the input and still return content close to the keyword.

    MATCH() FUZZY()

    Match multiple keywords in a specified order, with a controlled number of moves between the keywords.

    MATCH() PHRASE()

    MATCH() AGAINST()

    MATCH() AGAINST() supports word match and exact match. It runs a keyword match against the specified columns and returns the content that matches.

    MATCH() AGAINST() returns a relevance score for each matched row. You can filter the result set by specifying a threshold, such as > 0.9. The threshold filters out the bottom percentile of results by relevance. For example, > 0.9 filters out the bottom 90% of results by relevance and returns only the top 10%. By default, the returned result set is not sorted by relevance score. To sort results by relevance score, use the ORDER BY clause.

    Syntax

    SELECT * FROM `table_name` WHERE match (column_name[ , ... ]) against('term')

    Parameters

    • table_name: the table to search.

    • column_name: the column to search. To search multiple columns, separate the column names with commas (,).

    • term: the keyword to search for. Keyword queries support the following logical operators:

      • AND: returns content that matches all keywords.

      • OR: returns content that matches any one of the keywords.

      • NOT: returns content that matches the keyword on the left of the operator but does not match the keyword on the right.

    Note

    Logical operators are case-insensitive.

    Example 1: Single-column query

    SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH (`content`) AGAINST ('Products Services');

    The following result is returned:

    +------+-----------------------------------------------+
| id   | content                                       |
+------+-----------------------------------------------+
| 4    | User Values and Commercial Values of Products |
+------+-----------------------------------------------+
| 1    | Customers Need Better Products and Services   |
+------+-----------------------------------------------+

    Example 2: Multi-column query

    To match multiple columns, you do not need to create a multi-column composite index. As long as each column in the conditions has a full-text index, you can run a multi-column full-text index query.

    SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH (content, content_alinlp) AGAINST ('Services');

    The following query is equivalent. It combines the per-column conditions with OR:

    SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('Services')
OR MATCH (content_alinlp) AGAINST ('Services');

    The following result is returned:

    +------+---------------------------------------------+---------------------------------------------+
| id   | content                                     | content_alinlp                              |
+------+---------------------------------------------+---------------------------------------------+
| 1    | Customers Need Better Products and Services | Customers Need Better Products and Services |
+------+---------------------------------------------+---------------------------------------------+

    Example 3: Boolean query

    • Use the AND operator to return content that matches all keywords.

    SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('Products AND Services');

    The following result is returned:

    +------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+
| id   | content                                     | content_alinlp                              | content_ik                                  | content_standard                            | content_ngram                               |
+------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+
|    1 | Customers Need Better Products and Services | Customers Need Better Products and Services | Customers Need Better Products and Services | Customers Need Better Products and Services | Customers Need Better Products and Services |
+------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+

    • Use the OR operator to return content that matches any keyword.

    SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('Products OR Services');

    The following result is returned:

    +------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
| id   | content                                       | content_alinlp                                | content_ik                                    | content_standard                              | content_ngram                                 |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    4 | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    1 | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+

    • Use the NOT operator to return only content that matches the keyword on the left of the operator.

    SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('Products NOT Services');

    The following result is returned:

    +------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
| id   | content                                       | content_alinlp                                | content_ik                                    | content_standard                              | content_ngram                                 |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    4 | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+

    • Use the NOT operator together with *:* to exclude rows that match the keyword on the right of the operator. In the following query, *:* matches all rows in the table, and the query returns the rows that do not match Services.

    SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('*:* NOT Services');

    Use *:* only on the left of a logical operator, and only at the beginning of the expression.

    The following result is returned:

    +------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
| id   |                    content                    |                content_alinlp                 |                  content_ik                   |               content_standard                |                 content_ngram                 |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    2 |            Wuhan Changjiang Bridge            |            Wuhan Changjiang Bridge            |            Wuhan Changjiang Bridge            |            Wuhan Changjiang Bridge            |            Wuhan Changjiang Bridge            |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    4 | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+
|    3 |          Hangzhou, Zhejiang Province          |          Hangzhou, Zhejiang Province          |          Hangzhou, Zhejiang Province          |          Hangzhou, Zhejiang Province          |          Hangzhou, Zhejiang Province          |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+

    • Use parentheses to build a complex Boolean query:

    SELECT id, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH (content_alinlp) AGAINST ('(Wuhan OR Hangzhou) AND (Bridge OR Xihu)');

    The following result is returned:

    +------+-------------------------+
| id   | content_alinlp          |
+------+-------------------------+
|    2 | Wuhan Changjiang Bridge |
+------+-------------------------+

    Example 4: Result set filtering

    A full-text search returns all results that are similar to the keyword. When the data volume is large, the result set of a keyword query is also large, but you often need only the results with high relevance. Therefore, AnalyticDB for MySQL provides result set filtering.

    The following example filters out the bottom 90% of results by relevance and returns only the top 10%.

    SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('Products Services') > 0.9;

    The following result is returned:

    +------+-----------------------------------------------+
| id   | content                                       |
+------+-----------------------------------------------+
|    4 | User Values and Commercial Values of Products |
+------+-----------------------------------------------+
|    1 | Customers Need Better Products and Services   |
+------+-----------------------------------------------+

    Example 5: Relevance scores and sorting

    AnalyticDB for MySQL can return the relevance scores of a result set and sort the results by relevance score.

    • Query relevance scores.

    SELECT id, content, MATCH (content) AGAINST ('Products Services') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('Products Services') > 0.9;
    Important

    WHERE MATCH(content) AGAINST('Products Services') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    By default, the returned result set is not sorted by relevance score:

    +------+-----------------------------------------------+----------------------+
| id   | content                                       | score                |
+------+-----------------------------------------------+----------------------+
|    4 | User Values and Commercial Values of Products | 0.13076457381248474  |
+------+-----------------------------------------------+----------------------+
|    1 | Customers Need Better Products and Services   | 1.1090354919433594   |
+------+-----------------------------------------------+----------------------+

    The MATCH(content) AGAINST ('Wuhan') expression in the SELECT projection does not need to match the MATCH(content) AGAINST ('Products Services') expression in the WHERE clause. The following query statement returns the relevance scores for MATCH(content) AGAINST ('Wuhan').

    SELECT *, MATCH (content) AGAINST ('Wuhan') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('Products Services') > 0.9
ORDER BY score DESC;

    The following result is returned:

    +------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-------+
| id   | content                                       | content_alinlp                                | content_ik                                    | content_standard                              | content_ngram                                 | score |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-------+
|    4 | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | User Values and Commercial Values of Products | 0.0   |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-------+
|    1 | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   | Customers Need Better Products and Services   | 0.0   |
+------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-------+

    • Sort by relevance.

      Use the ORDER BY clause to sort the results by relevance score in descending order. 

    SELECT id, content, MATCH (content) AGAINST ('Products Services') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('Products Services') > 0.9
ORDER BY score DESC;
    Important

    WHERE MATCH(content) AGAINST('Products Services') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +------+-----------------------------------------------+---------------------+
| id   | content                                       | score               |
+------+-----------------------------------------------+---------------------+
|    1 | Customers Need Better Products and Services   | 0.2615291476249695  |
+------+-----------------------------------------------+---------------------+
|    4 | User Values and Commercial Values of Products | 0.13076457381248474 |
+------+-----------------------------------------------+---------------------+

    Example 6: Word match

    By default, a full-text search first tokenizes the keyword and then searches. If you do not enclose the keyword in double quotation marks, a row is returned when it matches any one of the tokenized keywords.

    The AliNLP analyzer splits Products and Services into Products, and, and Services, and splits Products Services into Products and Services.

    SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('Products Services') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content_alinlp) AGAINST ('Products Services') > 0.9
ORDER BY score DESC;
    Important

    WHERE MATCH(content) AGAINST('Products Services') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    A row is returned when it matches any one of the keywords in Products Services:

    +------+-----------------------------------------------+---------------------+
| id   | content_alinlp                                | score               |
+------+-----------------------------------------------+---------------------+
|    1 | Customers Need Better Products and Services   | 0.2615291476249695  |
+------+-----------------------------------------------+---------------------+
|    4 | User Values and Commercial Values of Products | 0.13076457381248474 |
+------+-----------------------------------------------+---------------------+

    Example 7: Exact match

    If you enclose the keyword in double quotation marks, the analyzer does not split the keyword. A row is returned only when the data in the specified column exactly matches the quoted keyword.

    • Search for content that exactly matches the keyword Products Services.

    SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('"Products Services"') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content_alinlp) AGAINST ('"Products Services"') > 0.9
ORDER BY score DESC;

    The exact match for the keyword Products Services returns an empty result:

    Empty set

    • Search for content that exactly matches the keyword Products and Services.

    SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('"Products and Services"') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content_alinlp) AGAINST ('"Products and Services"') > 0.9
ORDER BY score DESC;
    Important

    WHERE MATCH(content) AGAINST('Products Services') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +------+---------------------------------------------+--------------------+
| id   | content_alinlp                              | score              |
+------+---------------------------------------------+--------------------+
|    1 | Customers Need Better Products and Services | 0.3922937512397766 |
+------+---------------------------------------------+--------------------+

    MATCH() FUZZY()

    MATCH() FUZZY() supports fuzzy match queries. It matches the searched text based on the Levenshtein Edit Distance. If the input contains errors, a fuzzy match query can still return content that is close to the keyword.

    Syntax

    SELECT * FROM `table_name` WHERE match (`column_name`) fuzzy('term') [max_edits(n)];

    Parameters

    • table_name: the table to search.

    • column_name: the column to search.

    • term: the keyword to search for.

    • max_edits(n): the maximum edit distance (optional), which is the minimum number of changes (insertions, deletions, or substitutions) required to turn string A into string B. Default value: 2. Valid values: integers from 0 to 2. For example, one change turns windos into windows by inserting the character w, so the maximum edit distance between windos and windows is 1.

    Example 1: Approximate query

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou');

    If you do not specify max_edits, the maximum edit distance defaults to 2. The misspelled keyword hangzou still matches Hangzhou. The following result is returned:

    +------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
|    3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+

    To limit the maximum edit distance to 1, add the max_edits(1) clause:

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou') max_edits(1);

    The following result is returned:

    +------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
|    3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+

    Example 2: Approximate query with an edit distance of 2

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('Wuhan Chang') max_edits(2);

    The following result is returned:

    +------+-------------------------+
| id   | content                 |
+------+-------------------------+
|    2 | Wuhan Changjiang Bridge |
+------+-------------------------+

    MATCH() PHRASE()

    MATCH() PHRASE() supports phrase queries. It searches the specified column for content that matches multiple keywords. When all keywords match, you can use the slop parameter as an additional condition that determines whether the phrase query is satisfied.

    Syntax

    SELECT * FROM `table_name` WHERE match (`column_name`) phrase('term1 term2') [slop(n)]

    Parameters

    • table_name: the table to search.

    • column_name: the column to search.

    • term1 term2: the list of keywords to search for. Separate the keywords with spaces. The keyword order affects the match results.

    • slop(n): the maximum number of moves (optional). After the analyzer splits the text into a list of terms, each term is numbered in order starting from 0. Terms are moved left or right to match the keyword list specified by the phrase parameter. Default value: 0. Valid values: integers from 0 to 6. For example, the Standard analyzer splits Need Better Products and Services into Need, Better, Products, and, and Services.

    Text

    Need

    Better

    Products

    and

    Services

    Position

    0

    1

    2

    3

    4

    • phrase('Need Products'): the slop distance is 1, which means that Need moves one position to the right or Products moves one position to the left.

    • phrase('Need and'): the slop distance is 2, which means that Need moves two positions to the right or and moves two positions to the left.

    Example 1: English phrase query

    AnalyticDB for MySQL uses the built-in Standard analyzer, which splits Hangzhou, Zhejiang Province into Hangzhou, Zhejiang, and Province under the default configurations.

    • Search for content that matches the keywords zhejiang hangzhou.

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou');

    The slop parameter is 0 by default, so the search for content that matches the keywords zhejiang hangzhou returns an empty result:

    Empty set

    • Search for content that matches the keywords hangzhou Province after one move.

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('hangzhou Province') slop(1);

    The following result is returned:

    +------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
|    3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+

    • Search for content that matches the keywords zhejiang hangzhou after two moves.

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou') slop(2);

    The following result is returned:

    +------+-----------------------------+
| id   | content                     |
+------+-----------------------------+
|    3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+

    Example 2: Phrase query with the IK and Standard analyzers

    In Chinese phrase match scenarios that use MATCH() PHRASE(), the built-in AliNLP analyzer and Ngram analyzer may fail to match the queried data. Use the Standard analyzer or the IK analyzer instead.

    • Search for content that matches the keywords Products Services after one move.

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_ik) PHRASE('Products Services') slop(1);

    The following result is returned:

    +------+---------------------------------------------+
| id   | content                                     |
+------+---------------------------------------------+
|    1 | Customers Need Better Products and Services |
+------+---------------------------------------------+

    • Search for content that matches the keywords Products Services after two moves.

    SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) PHRASE('Products Services') slop(2);

    The following result is returned:

    +------+---------------------------------------------+
| id   | content                                     |
+------+---------------------------------------------+
|    1 | Customers Need Better Products and Services |
+------+---------------------------------------------+

    Highlight support

    Highlight by using a function

    AnalyticDB for MySQL supports the fulltext_highlight(column_name) function, which highlights the matched keywords in a full-text index column.

    Example 1: Highlight MATCH() AGAINST() keywords

    • Highlight keywords in a single-column query.

    SELECT MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') > 0.9
ORDER BY score DESC LIMIT 3;
    Important

    WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +--------------------+-------------------------------------------+
| score              | fulltext_highlight(content_alinlp)        |
+--------------------+-------------------------------------------+
| 0.2615291476249695 | <em>Wuhan</em> <em>Changjiang</em> Bridge |
+--------------------+-------------------------------------------+

    • Highlight keywords in a multi-column query.

    SELECT MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') > 0.9
AND MATCH (content_alinlp) AGAINST ('Bridge') > 0.9
ORDER BY score DESC LIMIT 3;
    Important

    WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +--------------------+----------------------------------------------------+
| score              | fulltext_highlight(content_alinlp)                 |
+--------------------+----------------------------------------------------+
| 0.2615291476249695 | <em>Wuhan</em> <em>Changjiang</em> <em>Bridge</em> |
+--------------------+----------------------------------------------------+

    Example 2: Highlight MATCH() FUZZY() keywords

    SELECT id, MATCH(content_standard) FUZZY('Wuhan Chang') as score, fulltext_highlight(content_standard)
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('Wuhan Chang');

    The following result is returned:

    +------+-------+-------------------------------------------+
| id   | score | fulltext_highlight(content_standard)      |
+------+-------+-------------------------------------------+
|    2 |   0.0 | <em>Wuhan</em> Chang<em>jiang</em> Bridge |
+------+-------+-------------------------------------------+

    Example 3: Highlight MATCH() PHRASE() keywords

    SELECT id, MATCH(content_ik) PHRASE('Products Services') slop(1) as score, fulltext_highlight(content_ik)
FROM tbl_fulltext_demo
WHERE MATCH(content_ik) PHRASE('Products Services') slop(1);

    The following result is returned:

    +------+---------------------+---------------------------------------------------------------+
| id   | score               | fulltext_highlight(content_ik)                                |
+------+---------------------+---------------------------------------------------------------+
|    1 | 0.16922473907470703 | Customers Need Better <em>Products</em> and <em>Services</em> |
+------+---------------------+---------------------------------------------------------------+

    Customize highlighting by using a hint

    AnalyticDB for MySQL highlights full-text search results with the <em> and </em> tags by default. You can also use a hint to set fulltext_highlight_pre_tag and fulltext_highlight_post_tag to define the left and right highlight tags.

    The following examples use <span> and </span> as the highlight tags.

    Example 4: Custom highlighting for MATCH() AGAINST()

    /*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) AGAINST ('Wuhan Changjiang') > 0.9
ORDER BY score DESC LIMIT 3;
    Important

    WHERE MATCH(content_alinlp) AGAINST('Wuhan Changjiang') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +--------------------+---------------------------------------------------+
| score              | fulltext_highlight(content_alinlp)                |
+--------------------+---------------------------------------------------+
| 0.2615291476249695 | <span>Wuhan</span> <span>Changjiang</span> Bridge |
+--------------------+---------------------------------------------------+

    Example 5: Custom highlighting for MATCH() FUZZY()

    /*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) FUZZY ('Wuhan Changjiang') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) FUZZY ('Wuhan Changjiang') > 0.9
ORDER BY score DESC LIMIT 3;
    Important

    WHERE MATCH(content_alinlp) FUZZY('Wuhan Changjiang') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +--------------------+---------------------------------------------------+
| score              | fulltext_highlight(content_alinlp)                |
+--------------------+---------------------------------------------------+
| 0.0                | <span>Wuhan</span> <span>Changjiang</span> Bridge |
+--------------------+---------------------------------------------------+

    Example 6: Custom highlighting for MATCH() PHRASE()

    /*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) PHRASE ('Wuhan') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) PHRASE ('Wuhan') > 0.9
ORDER BY score DESC LIMIT 3;
    Important

    WHERE MATCH(content_alinlp) PHRASE('Wuhan') > 0.9 filters out 90% of low-relevance results and returns only the top 10% by relevance score.

    The following result is returned:

    +---------------------+-----------------------------------------------------------------------------------+
| score               | fulltext_highlight(content_alinlp)                                                |
+---------------------+-----------------------------------------------------------------------------------+
| 0.13076457381248474 | Customers Need Better <span>Products</span> <span>and</span> <span>Services</span> |
+---------------------+-----------------------------------------------------------------------------------+