MATCH AGAINST|V4.6.0|OceanBase Database| docs|Distributed Database

MATCH AGAINST

Last Updated：2026-05-07 11:26:25 Updated

Purpose

This expression is used for text retrieval on a full-text index. The AGAINST clause accepts a search string and searches for it in the index based on the character set comparison. For each row in the table, the return value of MATCH represents the relevance between the search string and the data in the row, which is the similarity between the text in the search string and the text in the data table.

Syntax

MATCH (column_set) AGAINST (query_expr [mode_flag])

column_set:
    column_name [, column_name ...]

mode_flag:
    IN NATURAL LANGUAGE MODE
    | IN BOOLEAN MODE
    | IN MATCH PHRASE MODE

Parameters

Field	Description
column_set	Specifies the column to be searched. If multiple columns are specified, they must be separated by commas. The query is independent of the order of the columns. To execute the `MATCH AGAINST` expression, there must be a full-text index that exactly matches the `column_set`.
query_expr	Specifies the keyword or phrase to be searched for, which is the parameter to be matched. OceanBase Database will tokenize `query_expr` based on the tokenizer used by the full-text index and then perform a search on the full-text index.
mode_flag	Optional. Specifies the search mode. The default value is `IN NATURAL LANGUAGE MODE`. For more information, see mode_flag.

mode_flag

IN NATURAL LANGUAGE MODE: The default mode, used to specify the use of the natural language search mode. In this mode, the full-text search is performed by tokenizing the query expression (query_expr) to obtain a set of tokens (token), and then matching these tokens with those in the index. A match is considered successful if at least one token matches. Additionally, the relevance of the matched rows is ranked using the Okapi BM25 method.

By default, or when IN NATURAL LANGUAGE MODE is specified, MATCH AGAINST uses the NATURAL LANGUAGE mode for full-text search. In this mode, AGAINST accepts a search string and searches for it in the index based on the character set comparison. For each row in the table, the return value of MATCH represents the relevance between the search string and the data in the row, which is the similarity between the text in the search string and the text in the data table.
IN BOOLEAN MODE: Used to specify the use of the Boolean mode for search. The current version supports three most commonly used Boolean operators and nested operations, as follows:
- +: Represents AND, which is the intersection.
- -: Represents negation, which is the difference.
- No operator: When used alone, it represents OR, which is the union. For example, A B means A OR B. Using operators with OR can increase the relevance of the sentences but will lose the OR meaning. For example, +A B means A must be present, and the relevance of A and B in the sentence is calculated.
- (): Represents nested operations. When there is no operator outside, it has the OR meaning. For example, +A (nested clause) means A or the nested clause must be present.
  
  Note
  
  For OceanBase Database V4.3.5, support for IN BOOLEAN MODE was introduced starting from V4.3.5 BP1.
  
  Examples:
  1. Output sentences must contain the word "computer".
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
```
  2. Output sentences must contain the word "computer" and must not contain the word "weather".
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);
```
  3. Output sentences must contain the word "computer", and those containing "oceanbase" are more relevant.
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
```
IN MATCH PHRASE MODE: Used to support exact phrase matching.

Note

Support for IN MATCH PHRASE MODE was introduced in OceanBase Database V4.4.0.

Examples:
1. Create a table named test and add a full-text index to the specified columns.
```
CREATE TABLE test(c1 int, c2 VARCHAR(200), c3 TEXT, FULLTEXT INDEX fts_idx (c2, c3));
```
2. Query records where the c2 and c3 columns exactly match some words.
```
SELECT c2 FROM test WHERE MATCH(c2,c3) AGAINST('some words' IN MATCH PHRASE MODE);
```

Considerations

The MATCH AGAINST expression, in addition to indicating text matching, has other semantic meanings in different clauses of SQL:

Projection of relevance:
- Full-text search supports ranking based on word frequency. The MATCH AGAINST expression can be used to project the relevance.
- Relevance indicates the degree of match between the matched data rows and the query (Query) in the MATCH AGAINST expression.
- Relevance is a DOUBLE type data greater than or equal to 0. A value of 0 indicates that the data row is unrelated to the query (Query). The higher the value, the stronger the relevance.
Filtering semantics:
- If the MATCH AGAINST expression appears in the WHERE clause and is connected to all other filtering conditions using AND, it has filtering semantics. This means that data rows that do not match the query will be filtered out.
  
  Here is an example:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase');
```
  This is equivalent to:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase') > 0;
```
Sorting semantics:
- If the MATCH AGAINST expression appears in the WHERE clause and is connected to other filtering conditions using AND, it has sorting semantics.
- This means that the results of the full-text search will be sorted in descending order based on the relevance ranking of the MATCH AGAINST expression.
- If there are multiple MATCH AGAINST expressions, the results will be sorted based on the relevance of the first MATCH AGAINST expression.
Here is an example:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) against ('oceanbase');
```
This is equivalent to:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase')
ORDER BY relevance DESC;
```

Execution of text retrieval

If the MATCH AGAINST expression in the SQL statement has filtering semantics, OceanBase Database can execute the query by scanning the full-text index to calculate the MATCH AGAINST expression and then accessing the table.
If the MATCH AGAINST expression in the SQL statement does not have filtering semantics, OceanBase Database can execute the query by scanning and filtering using other secondary indexes and then accessing the full-text index based on the primary key.
If the WHERE clause of the SQL statement contains the MATCH AGAINST expression and multiple filtering conditions connected by AND, and other secondary indexes can be hit, OceanBase Database will attempt to select the most cost-effective index for scanning.
When calculating the MATCH AGAINST expression by scanning the full-text index, if the query contains a LIMIT clause, OceanBase Database will attempt to push the top-k calculation down to the full-text index scan to improve performance.
When the query contains MATCH AGAINST predicates connected by OR, an index merge plan will be automatically generated. Whether the index merge plan is selected depends on the cost competition.
Note
- Starting from V4.4.1, the index merge plan supports multiple predicates connected by AND. When the query contains multiple predicates, including MATCH AGAINST, an index merge plan will be automatically generated.
- Before V4.4.1, the index merge plan only supported multiple predicates connected by OR.

Examples

Create the sample table test_tbl1, and create the full-text indexes ft_idx1_test_tbl1 and ft_idx1_test_tbl1, and the index idx_test_tbl1.

CREATE TABLE test_tbl1(id INT, ref_no INT, digest VARCHAR(512), detail VARCHAR(4096),
    FULLTEXT INDEX ft_idx1_test_tbl1(detail),
    FULLTEXT INDEX ft_idx2_test_tbl1(digest, detail),
    INDEX idx_test_tbl1 (id));

Insert test data into the test_tbl1 table.

INSERT INTO test_tbl1 VALUES
    (1, 1234, 'fulltext', 'Try text retrieval with OceanBase fulltext index'),
    (2, 2345, 'log', 'OceanBase can halp with log analysis'),
    (3, 3456, 'order', 'Simple text retrieval scan will return result set in order of descending ranking in OceanBase'),
    (4, 4567, 'ranking', 'OceanBase will ranking relevance to query for matched result set'),
    (5, 5678, 'filter', 'Using text retrieval as a filter condition would be probably more efficient');

Use the MATCH AGAINST expression to project relevance.

SELECT id, digest, detail, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM test_tbl1;