本文介绍了在创建表时或在已存在的表中添加全文索引的方法。

创建表时添加全文索引列

约束条件
  1. 全文索引仅支持对一列进行设置,如需对多个列创建全文索引,可通过在多个列上单独创建全文索引实现。
  2. 全文索引只支持varchar类型的列。

语法结构

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]   --定义全文索引

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

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

示例

示例中title列是全文索引,其他列是普通索引。

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)

为已存在的表添加全文索引列

约束条件
  1. 全文索引仅支持对单列进行设置,如需对多个列创建全文索引,可通过在多个列上单独创建全文索引实现。
  2. 全文索引只支持varchar类型的列。
  3. 在已存在的表中添加的全文索引,仅支持查询新写入的数据,查询已经写入数据表的历史数据不能获得有效结果。如果需要对历史数据生效全文索引,需要使用BUILD TABLE `表名` force=true;强制建立索引。
语法结构
ALTER TABLE ADD FULLTEXT [INDEX/KEY] [index_name] (col_name,...)] [indexoption]

示例

  1. 创建表。
    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. 在上表中通过下列语句添加全文索引。
    说明 添加全文索引的列不能有普通索引,如果有需要先drop普通索引。
    ALTER TABLE fulltext_test ADD FULLTEXT INDEX b_idx(body);
  3. 如需对历史数据建立全文索引,执行下列语句。
    BUILD TABLE `fulltext_test` force=true;

删除全文索引

语法结构
ALTER TABLE table_name DROP FULLTEXT INDEX index_name;
示例
ALTER TABLE fulltext_test DROP FULLTEXT INDEX b_idx;