创建全文索引 - 云原生数仓 AnalyticDB MySQL

创建表时添加全文索引列

约束条件

全文索引仅支持对一列进行设置，如需对多个列创建全文索引，可通过在多个列上单独创建全文索引实现。
全文索引只支持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)

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

约束条件

全文索引仅支持对单列进行设置，如需对多个列创建全文索引，可通过在多个列上单独创建全文索引实现。
全文索引只支持varchar类型的列。
在已存在的表中添加的全文索引，仅支持查询新写入的数据，查询已经写入数据表的历史数据不能获得有效结果。如果需要对历史数据生效全文索引，需要使用BUILD TABLE `表名` force=true；强制建立索引。

语法结构

ALTER TABLE ADD FULLTEXT [INDEX/KEY] [index_name] (col_name,...)] [indexoption]

示例

创建表。

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';

在上表中通过下列语句添加全文索引。

说明添加全文索引的列不能有普通索引，如果有需要先drop普通索引。
```
ALTER TABLE fulltext_test ADD FULLTEXT INDEX b_idx(body);
```
如需对历史数据建立全文索引，执行下列语句。
```
BUILD TABLE `fulltext_test` force=true;
```

删除全文索引

语法结构

ALTER TABLE table_name DROP FULLTEXT INDEX index_name;

示例

ALTER TABLE fulltext_test DROP FULLTEXT INDEX b_idx;