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

Create a full-text index on a column when you create a table

Limits
  1. A full-text index can be created only on a single column. If you want to create full-text indexes for multiple columns, you can create a full-text index on each column.
  2. Full-text indexes can be created only on columns of the VARCHAR type.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [AS] query_expression
   COMMENT 'string'

column_attributes:
   [DEFAULT default_expr]
   [AUTO_INCREMENT]

column_constraints:
   [{NOT NULL|NULL} ]
   [PRIMARY KEY]

table_constraints:
   [{INDEX|KEY} [index_name] (column_name,...)]
   [PRIMARY KEY [index_name] (column_name,...)]
   [CLUSTERED KEY [index_name] (column_name,...)]
   [ANN index [index_name] (col_name,...)] [indexoption]
   [FULLTEXT [INDEX/KEY] [index_name] (col_name,...)] [indexoption]   --Define a full-text index.

table_attribute:
   DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, ?))}
  LIFECYCLE N

Example

In the following sample code, the index on the title column is a full-text index, and the indexes on other columns are regular indexes.

CREATE TABLE fulltext_test (
    id int,
    title varchar,
    body varchar,
    FULLTEXT INDEX title_idx(title),
    PRIMARY KEY (id)
)
DISTRIBUTE BY HASH(id);

mysql> show index from fulltext_test;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fulltext_test |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| fulltext_test |          1 | body_2_idx |            1 | body        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| fulltext_test |          1 | id_0_idx   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| fulltext_test |          1 | title_idx  |            1 | title       | A         |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.04 sec)

Create a full-text index on a column of an existing table

Limits
  1. A full-text index can be created only on a single column. If you want to create full-text indexes for multiple columns, you can create a full-text index on each column.
  2. Full-text indexes can be created only on columns of the VARCHAR type.
  3. Only data written after a full-text index is created can be queried by using the index. If you try to use the index to query historical data written before the index is created, no valid results are returned. If you want to use the index to query historical data, you must execute the BUILD TABLE `Table name` force=true; statement to forcibly create a full-text index.
Syntax
ALTER TABLE ADD FULLTEXT [INDEX/KEY] [index_name] (col_name,...)] [indexoption]

Example

  1. Create a table.
    CREATE TABLE fulltext_test (
        id int,
        title varchar,
        body varchar,
        FULLTEXT INDEX title_idx(title),
        PRIMARY KEY (id)
    )
    DISTRIBUTE BY HASH(id)
    INDEX_ALL='N';
  2. Execute the following statement to create a full-text index on the preceding table.
    Note Columns on which full-text indexes are created cannot have regular indexes. If you want to create a full-text index on a column that has a regular index, you must first remove the regular index.
    ALTER TABLE fulltext_test ADD FULLTEXT INDEX b_idx(body);
  3. If you want to create a full-text index on historical data, execute the following statement:
    BUILD TABLE `fulltext_test` force=true;

Delete a full-text index

Syntax
ALTER TABLE table_name DROP FULLTEXT INDEX index_name;
Example
ALTER TABLE fulltext_test DROP FULLTEXT INDEX b_idx;