This topic describes how to create a full-text index for full-text search in AnalyticDB for MySQL.
Prerequisites
An AnalyticDB for MySQL cluster of V3.1.4.9 or later is created.
We recommend that you use an AnalyticDB for MySQL cluster of V3.1.4.17 or later.
For information about how to query the minor version of an AnalyticDB for MySQL cluster, see How do I query the version of an AnalyticDB for MySQL cluster?
Limits
A full-text index can be created only on a single column each time. If you want to create a full-text index for multiple columns, you can create the full-text index on each column.
Full-text indexes can be created only on columns of the VARCHAR type.
Visibility policies
Full-text indexes of AnalyticDB for MySQL are visible to newly written data in real time.
To create a full-text index on historical data, you must execute the
BUILD TABLE `Table name` force=true;
statement.
Create a full-text index when you create a table
Syntax
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
[FULLTEXT [INDEX|KEY] [index_name] (column_name)] [index_option]} [, ... ] )
table_attribute
[partition_options]
[AS] query_expression
COMMENT 'string'
Parameters
Parameter | Description |
table_name | The name of the table. The table name must be 1 to 127 characters in length and can contain letters, digits, and underscores (_). The table name must start with a letter or underscore (_). Specify the table name in the |
column_name | The name of the column to be added to the table. The column name must be 1 to 127 characters in length and can contain letters, digits, and underscores (_). The column name must start with a letter or underscore (_). |
column_type | The data type of the column. At least one column must be of the VARCHAR type. For more information about the data types supported by AnalyticDB for MySQL, see Basic data types. |
column_attributes | The attributes of the column. For more information about the column attributes supported by AnalyticDB for MySQL, see CREATE TABLE. |
column_constraints | The constraints of the column. For more information about the column constraints supported by AnalyticDB for MySQL, see CREATE TABLE. |
FULLTEXT | Defines the full-text index. |
INDEX|KEY | The keyword identifier of the full-text index. It can be INDEX or KEY. |
index_name | The name of the full-text index. |
column_name | The name of the column for creating the full-text index. The column must be of the VARCHAR type. |
index_option | Specifies the analyzer and custom dictionary used for the full-text index.
|
Examples
Create a full-text index on the content
column when you create a table named tbl_fulltext_name
.
CREATE TABLE `tbl_fulltext_name` (
`id` int,
`content` varchar,
`keyword` varchar,
FULLTEXT INDEX fidx_c(`content`),
PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);
Query the index of the table.
SHOW index from tbl_fulltext_name;
Sample result:
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_fulltext_name | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl_fulltext_name | 1 | id_0_idx | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl_fulltext_name | 1 | keyword_2_idx | 1 | keyword | A | 0 | NULL | NULL | | BTREE | | |
| tbl_fulltext_name | 1 | fidx_c | 1 | content | A | 0 | NULL | NULL | | FULLTEXT | | |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Create a full-text index for an existing table
Syntax
ALTER TABLE `table_name` ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]
Parameters
For more information about the parameters, see the "Create a full-text index when you create a table" section of this topic.
Examples
Create a table named
tbl_fulltext_name
.CREATE TABLE `tbl_fulltext_name` ( `id` int, `content` varchar, `keyword` varchar, FULLTEXT INDEX fidx_c(`content`), PRIMARY KEY (`id`) ) DISTRIBUTED BY HASH(id) INDEX_ALL = 'N';
Create a full-text index named fidx_k on the
keyword
column and specify the standard analyzer.NoteIf no analyzer is specified, the default analyzer is used. For more information, see Analyzers for full-text indexes.
ALTER TABLE `tbl_fulltext_name` ADD FULLTEXT INDEX fidx_k(`keyword`) WITH ANALYZER standard;
To create a full-text index on historical data, execute the following statement. Indexes take effect only after BUILD tasks are completed. The amount of time it takes to create a full-text index is related to the amount of data.
BUILD TABLE `tbl_fulltext_name` force=true;
Delete a full-text index
Syntax
ALTER TABLE table_name DROP FULLTEXT INDEX index_name;
Examples
Delete the full-text index fidx_k
from the tbl_fulltext_name
table.
ALTER TABLE `tbl_fulltext_name` DROP FULLTEXT INDEX fidx_k;