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

MATCH AGAINST

Last Updated：2026-05-08 09:08:55 Updated

Purpose

This expression is used for text retrieval on a full-text index. The AGAINST clause accepts a search string and searches the index based on the collation. 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 indicates 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

Parameters

Field	Description
column_set	Specifies the column to be searched. If multiple columns are to be listed, 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, which is the parameter to be matched. OceanBase Database will tokenize `query_expr` using the tokenizer of the full-text index and then search the full-text index.
mode_flag	Optional. Specifies the search mode for full-text search. The default value is `IN NATURAL LANGUAGE MODE`. For more information, see mode_flag.

mode_flag

IN NATURAL LANGUAGE MODE: The default value. It specifies to use 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 the 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.

If the IN NATURAL LANGUAGE MODE keyword is not explicitly specified, MATCH AGAINST defaults to using the NATURAL LANGUAGE mode for full-text search. In this mode, the AGAINST clause accepts a search string as its parameter and compares it with the index according to the specified character set. For each row in the table, the MATCH function returns a relevance score that indicates the similarity between the search string and the text in the row.
IN BOOLEAN MODE: Specifies to use the boolean mode for search. The current version supports three commonly used boolean operators and nested operations, as follows:
- +: indicates AND, representing the intersection of sets.
- -: indicates NOT, representing the difference between sets.
- No operator: When no operator is specified, it implies OR, representing the union of sets. For example, A B is equivalent to A OR B. Using the + operator can increase the relevance of queries containing both terms, but the OR semantics are lost. For example, in the query +A B, the result must include A, and the relevance between A and B is calculated.
- (): represents nested Boolean expressions. If no operator precedes the parentheses, the OR operator is implied. For example, +A (nested clause) means that the sentence must contain A or the nested clause.
  
  Note
  
  For OceanBase Database V4.3.5, support for IN BOOLEAN MODE was added starting from V4.3.5 BP1.
  
  Examples:
  1. The output sentence must contain the word "computer".
```
 SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
```
  2. The output sentence 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. The output sentence must contain the word "computer", and "oceanbase" is more relevant.
```
 SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
```

Considerations

The MATCH AGAINST expression not only represents text matching but also has other semantics in different clauses of SQL:

Projection of relevance:
- Full-text search supports ranking based on term frequency, which can be represented by the MATCH AGAINST expression.
- Relevance indicates the degree of match between the matched data rows and the query (Query) specified in MATCH AGAINST.
- Relevance is a DOUBLE value greater than or equal to 0. A value of 0 indicates no relevance, while a higher value indicates a stronger match.
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.
  
  Example:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase');
```
  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.
- When multiple MATCH AGAINST expressions are present, the sorting is based on the relevance of the first MATCH AGAINST expression.
Example:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) against ('oceanbase');
```
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

When the MATCH AGAINST expression in an SQL statement has filtering semantics, OceanBase Database can execute the query by scanning the full-text index, computing the MATCH AGAINST expression, and then accessing the table.
When the MATCH AGAINST expression in an 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.
When the WHERE clause of an SQL statement contains MATCH AGAINST and multiple filtering conditions connected by AND, and other secondary indexes can be hit, OceanBase Database will try to select the most cost-effective index for scanning.
When computing the MATCH AGAINST expression by scanning the full-text index, if the query contains LIMIT, OceanBase Database will try to push the top-k calculation down to the full-text index scan to improve performance.
When a query contains MATCH AGAINST predicates connected by OR, an index merge plan will be automatically generated. The final decision to use the index merge plan depends on the cost competition.

Note

For OceanBase Database V4.3.5, support for generating an index merge plan was added starting from V4.3.5 BP1.

Examples

Create a sample table test_tbl1, and create two full-text indexes ft_idx1_test_tbl1 and ft_idx2_test_tbl1, and an 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;