Sign inTry Free

HIGHLIGHT

On this page

Returns the fragment of text near the matching words from a full-text query.

See Full Text Search for more conceptual information on this feature.

Syntax

HIGHLIGHT (expression) AGAINST (query_expression, [max_number_fragments])

Arguments

  • expression: Any set of expressions. This may be a column name, the result of another function, or a math operation.

  • query_expression: The supported possible expression values are the same as the MATCH syntax.

    Note: Fuzzy and wildcard search are not supported in HIGHLIGHT.

  • max_number_fragments: The maximum number of elements to return in the fragments JSON array.

    Note: highlight_fragment_size is a global variable that sets the size of the fragment for all highlight queries.

Output

Each string value (the raw string stored in the column that is indexed) conceptually can be thought of as fragments of approximately the same size (default is 100 characters and fragments are aligned to a token boundary). There is no physical change to how the raw string is stored. HIGHLIGHT returns the fragments that contain the match. Each fragment is accompanied by the offset into the document at the location of the fragment, and the number of matches within the fragment.

The result is a JSON blob. It contains one array named fragments. The element represents a fragment that has at least one match. Each element of fragments is an object with three members as described below:

Name

Type

Description

offset

number

The number of characters from the beginning of the text to the beginning of the fragment.

unique_term_count

number

The number of unique matches found within the fragment.

text

string

A snippet of text surrounding the matched text. The matched text is bolded using HTML tags.

The result will be NULL if no term is found.

Remarks

Unlike MATCH, HIGHLIGHT does not require a FULLTEXT index. This allows HIGHLIGHT to run on an arbitrary string expression, but at the expense of a longer query time compared to MATCH.

Example

The following extracts the relevant part of a field.

SELECT HIGHLIGHT (body) AGAINST ('mother') FROM books;
{"fragments":[{"offset":408,"unique_term_count":1.000000,"text":"
oblique line in her magnolia-white skin--that skin so prized by
Southern women and <B>mother</B> and so"},{"offset":0,
"unique_term_count":1.000000,"text":"In her face were too sharply
blended the delicate features of her <B>mother</B>, a Coast aristocrat of French"}]}

Use HIGHLIGHT on string literals to extract specific terms.

SELECT HIGHLIGHT(
    "Each string value (the raw string stored in the column that is indexed)
    conceptually can be thought of as fragments of approximately the same size
    (default is 100 characters and fragments are aligned to a token boundary).
    There is no physical change to how the raw string is stored.
    `HIGHLIGHT` returns the fragments that contain the match. Each fragment is
    accompanied by the offset into the document at the location of the fragment,
    and the number of matches within the fragment.") against("fragment");
{"fragments":[{"offset":311,"text":" the match. Each <B>fragment</B>
is accompanied by the offset into the document to the location of the
 <B>fragment</B>","unique_terms":1},{"offset":415,"text":", and the
 number of matches within the <B>fragment</B>","unique_terms":1}]}

It can also be used in combination with MATCH to retrieve relevant parts from relevant rows. This is the recommended way of using HIGHLIGHT.

SELECT name, HIGHLIGHT(doc) AGAINST ("fragment") FROM articles WHERE MATCH(doc) AGAINST ("+memsql + highlight");
{"fragments":[{"offset":202,"text":
" to a token boundary). There is no physical change to how it is stored.
 `HIGHLIGHT` returns the fragments that contain the match.
 Each <B>fragment</B> is accompanied by the offset into the document to
the location","unique_terms":1},{"offset":408,"text":" you yi zhi tu tu shi
 xiao ben ben. the <B>fragment</B>, and the number of matches within the
 <B>fragment</B>. you yi zhi tu tu shi xiao ben ben. wo xi huan xiao ben
 ben tu tu","unique_terms":1}]}

Related Topics

Last modified: May 31, 2023

Was this article helpful?

On this page

Was this article helpful?