Full-text query refers to the operation of performing a full-text search or retrieval in text data. It is used to find text content that contains specific keywords, phrases, or text expressions. Full-text queries can search the entire text more comprehensively and return results that match the search criteria.
Syntax
You can use the following syntax to perform a Full-text search by specifying columns and keywords or phrases. You can also use specific search modifiers to adjust the search pattern (i.e., search conditions or rules).
MATCH (column_name [, column_name ...]) AGAINST (expr [search_modifier])
search_modifier:
IN NATURAL LANGUAGE MODE
| IN BOOLEAN MODE
| IN MATCH PHRASE MODE
The related parameters are described as follows:
column_name: The column for which to perform the full-text search. If you need to list multiple columns, separate them with commas.expr: the keyword or phrase to search for.search_modifier: Optional. Specifies the search pattern. For details about the valid values and applicable scenarios, see the following table:Search ModeParameter DescriptionApplication ScenariosIN NATURAL LANGUAGE MODE The default value. Specifies to use the natural language search mode for the search. Natural language, top-k scenarios, performs fuzzy matching, and outputs relevance scores. Words are automatically segmented by default using the tokenizer configured for the table. IN BOOLEAN MODE Specifies to perform a search in boolean mode. The current version supports the following boolean operators and nested operations: +: representsAND, the intersection.-: represents negation, the difference set.- No operator: when used alone, it represents
OR, the union. For example,A BmeansA OR B. Mixing the operators with the+operator increases the relevance of existing sentences but loses the semantics ofOR. For example,+A Bmeans A must be present, and then calculates the relevance of A and B in the sentence. (): represents nested operations. When no outer operator is present, it has the semantics ofOR. For example,+A (nested clause)means A must be present or the nested clause must be true.
Filters semantics for exact match, filtering based on user output syntax. You can choose to output relevance, where each word is segmented only by spaces and +,-,().IN MATCH PHRASE MODE Specifies to use phrase query. Exact phrase match without stemming. The query result must exactly match the phrase. Example:
The output sentence must contain the word "computer".
obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);The output sentence must contain the word "computer" and must not contain the word "weather".
obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);The output sentence must contain the word "computer", and the sentence is more likely to match if it also contains "oceanbase".
obclient> SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);Perform queries using phrase matching patterns.
MATCH AGAINSTis used as theSELECTfield for querying.obclient> SELECT id, MATCH (title, body) AGAINST ('some words' IN MATCH PHRASE MODE) AS score FROM test;MATCH AGAINSTis used as theWHEREfield for querying.obclient> SELECT * FROM test WHERE MATCH (title, body) AGAINST ('some words' IN MATCH PHRASE MODE);
For more information about the MATCH AGAINST expression, see MATCH AGAINST.
Vectorized queries
For queries containing full-text indexes, you can choose between vectorized and non-vectorized execution. You can use the /*+ opt_param('rowsets_enabled', '[true | false]')*/ hint to enable or disable vectorization.
Notice
If no hint is specified, whether vectorization is enabled is determined by the configuration of the vectorization engine system parameter. By default, it is enabled.
Example:
Enable vectorized queries.
obclient> SELECT /*+ opt_param('rowsets_enabled', 'true') */ title, body FROM articles WHERE MATCH(title, body) AGAINST('tutorial');Disable vectorized queries.
obclient> SELECT /*+ opt_param('rowsets_enabled', 'false') */ title, body FROM articles WHERE MATCH(title, body) AGAINST('tutorial');
Examples
Create table
tbl1and a full-text indexfull_idx1_tbl1on it.obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(100), col3 TEXT, FULLTEXT INDEX full_idx1_tbl1(col2, col3));Add test data to the
tbl1table.obclient> 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: 0In the
MATCHclause, specify to search for the keyword 'OceanBase' in columnscol2andcol3of tabletbl1. Also, use the search modifierIN NATURAL LANGUAGE MODEto indicate searching in natural language mode.obclient> 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
ES Full-text Query
OceanBase AI Database supports the SQL syntax for ES full-text queries, including the MATCH() and SCORE() statements:
The
MATCH()statement is used as a field afterWHEREto indicate filtering semantics and must be used together with theSCORE()statement.Notice
Currently, there can be at most one
MATCH()statement in theWHEREclause, and it cannot be used together withMATCH() AGAINST().The
SCORE()statement indicates the correlation of the correspondingMATCH()statement. TheSCORE()statement is used in conjunction with theMATCH()syntax and has no meaning when used alone.
The SQL syntax for full-text queries in ES is as follows:
SELECT [select_expr_list ,] SCORE()
FROM table_name
WHERE
MATCH(
A_COLUNM^x, B_COLUNM^y ...,
'expr',
'[parameters]'
)
... ;
parameters:
operator=or;
minimum_should_match=int_value;
boost=number_value;
score_norm=min-max;
type={most_fields | best_fields}
The related parameters are described as follows:
A_COLUNM^x, B_COLUNM^y ...: Indicates column names and their corresponding weights, where the weights are positive numbers.Notice
For each column in the
MATCH()expression, you must create a full-text index, not a composite index on the columns. For information about creating full-text indexes, see Create an index.expr: The query statement, which is divided into tokens. Each token can contain a weight. The weight must be a positive number, with a default value of 1.parameters: Optional. Specifies a key-value pair list. If no additional parameters are specified, include an empty pair of quotation marks''. The following parameters are currently supported:operator: the aggregation algorithm for theexprparameter. Valid values:or: Indicates that the output is generated as long as one token exists. If theminimum_should_matchparameter is specified, refer to its description below for the return result information.
minimum_should_match: A positive integer. When theoperatorparameter isor, this parameter specifies the minimum number of keywords in a token group that must match.boost: A positive value indicating theMATCHlevel weight, which is multiplied by the relevance. The default value is 1.score_norm: Specifies the normalization algorithm for aMATCHscore during scoring. Valid value:min-max. By default, normalization is not performed.type: the correlation calculation method when multiple columns are involved in aMATCHoperation. Valid values:most_fields: Indicates to sum the correlations of all columns.best_fields: Indicates to take the maximum correlation value for each column.
Example:
Create a full-text index on column
col2of tabletbl1.obclient> CREATE FULLTEXT INDEX ft_idx_tbl1_col2 ON tbl1(col2);Create a full-text index on column
col3of tabletbl1.obclient> CREATE FULLTEXT INDEX ft_idx_tbl1_col3 ON tbl1(col3);Filter rows from table
tbl1that contain both "OceanBase" and "Database" (at least 2 keywords,minimum_should_match=2), perform a full-text search on fieldscol2(weight 2) andcol3(weight 1), and return the matching score (normalized to the range 0-1).obclient> SELECT *, SCORE() AS relevance_score FROM tbl1 WHERE MATCH(col2^2, col3^1, 'OceanBase Database', 'operator=or; minimum_should_match=2; boost=1; score_norm=min-max; type=most_fields' );The return result is as follows:
+------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+ | col1 | col2 | col3 | relevance_score | +------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+ | 2 | OceanBase | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team | 0.6079027355623101 | +------+-----------+---------------------------------------------------------------------------------------------------------------------+--------------------+ 1 row in setFilter rows containing "OceanBase" or "Database" from table
tbl1(match at least one keyword,minimum_should_match=1), perform a full-text search on fieldscol2(weight 2) andcol3(weight 1), and return the matching score (normalized to the range 0-1).obclient> SELECT *, SCORE() AS relevance_score FROM tbl1 WHERE MATCH(col2^2, col3^1, 'OceanBase Database', 'operator=or; minimum_should_match=1; boost=1; score_norm=min-max; type=most_fields' );The return result is as follows:
+------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+ | col1 | col2 | col3 | relevance_score | +------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+ | 2 | OceanBase | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team | 0.4208104883442219 | | 3 | Database Management | Learn about SQL and database administration | 0.2876526910145043 | +------+---------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+ 2 rows in set
References
- Create an index
- Full-text index