本文介绍了云原生数据仓库AnalyticDB MySQL版在创建表时或在已存在的表中添加全文索引的方法。
版本限制
推荐使用内核版本为3.1.4.17及以上的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;