This topic describes how to create a full-text index when you create a table or use an existing table in AnalyticDB for MySQL.

Prerequisites

A cluster that runs the minor engine version of 3.1.4.9 or later is created.
Note

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

ParameterDescription
table_nameThe 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 db_name.table_name format to distinguish tables that share the same name across different databases.

column_nameThe 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 table name must start with a letter or underscore (_).

column_typeThe 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_attributesThe attributes of the column. For more information about the column attributes supported by AnalyticDB for MySQL, see CREATE TABLE.
column_constraintsThe constraints of the column. For more information about the column constraints supported by AnalyticDB for MySQL, see CREATE TABLE.
FULLTEXTDefines the full-text index.
INDEX|KEYThe keyword identifier of the full-text index. It can be INDEX or KEY.
index_nameThe name of the full-text index.
column_nameThe name of the column for creating the full-text index. The column must be of the VARCHAR type.
index_optionSpecifies the analyzer and custom dictionary used for the full-text index.
  • WITH ANALYZER analyzer_name: specifies the analyzer used for the full-index index. For more information about the analyzers supported by AnalyticDB for MySQL, see Analyzers for full-text indexes.
  • WITH DICT tbl_dict_name: specifies the custom dictionary used for the full-index index. For more information about the custom dictionaries supported by AnalyticDB for MySQL, see Custom dictionaries for full-text indexes.

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`)
) DISTRIBUTE BY HASH(id);
Query the index of the table. 
SHOW index from tbl_fulltext_name;
The following information is returned:
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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

  1. 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`)
) DISTRIBUTE BY HASH(id) INDEX_ALL = 'N';
  2. Create a full-text index named fidx_k on the keyword column and specify the standard analyzer.
    Note If 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;
  3. 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;