Full-text queries

A full-text query is an operation of searching or retrieving within text data. It is used to search for text that contains specified keywords, phrases, or text expressions. A full-text query can comprehensively search through the entire text and return results that match the search criteria.

Syntax

The syntax is as follows, where you can specify the target columns and keywords or phrases for a full-text query, and use a specific search modifier to adjust the search mode (which means search conditions or rules):

MATCH (column_name [, column_name ...]) AGAINST (expr [search_modifier])

search_modifier:
    IN NATURAL LANGUAGE MODE

Related parameters are described as follows:

• column_name: the columns on which a full-text query is to be performed. Separate multiple columns by commas (,).

• expr: the keyword or phrase to search.

• search_modifier: the search modifier that determines the search mode. This parameter is optional. The valid values are as follows:

  • IN NATURAL LANGUAGE MODE: specifies to use the natural language search mode. This is the default value.

  Note

  Currently, OceanBase Database supports only IN NATURAL LANGUAGE MODE.

Examples

1. Create a table named tbl1 and create a full-text index named full_idx1_tbl1.

   CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(100), col3 TEXT, FULLTEXT INDEX full_idx1_tbl1(col2, col3));
   

2. Insert test data into the tbl1 table.

   INSERT INTO tbl1 (col1, col2, col3) VALUES (1, 'Hello World', 'This is a test'),
       (2, 'OceanBase', 'OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team'),
       (3, 'Database Management', 'Learn about SQL and database administration'),
       (4, 'Full Text Searching', 'Master the art of full text searching');
   

   The return result is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

3. In the MATCH clause, specify to search the col2 and col3 columns in the tbl1 table for the keyword ‘OceanBase’. Specify the search modifier IN NATURAL LANGUAGE MODE to use the natural language search mode.

   SELECT * FROM tbl1 
   WHERE MATCH (col2, col3) AGAINST ('OceanBase' IN NATURAL LANGUAGE MODE);
   

   The return result is as follows:

   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   | col1 | col2      | col3                                                                                                                |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   |    2 | OceanBase | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |
   +------+-----------+---------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

References

• Create an index
• Full-text indexes