多元索引操作

支持的操作

全文检索

全文检索匹配 Text 类型字段中包含指定字符串的数据，支持匹配查询（TEXT_MATCH）和短语匹配查询（TEXT_MATCH_PHRASE）两种方式。

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore') LIMIT 10;

SELECT * FROM search_exampletable WHERE TEXT_MATCH_PHRASE(content, 'sql query') LIMIT 10;

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'or', '2') LIMIT 10;

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'and') LIMIT 10;

数组类型查询

使用 ARRAY_EXTRACT 函数查询数组类型列的数据。数组列在多元索引中须配置为数组类型。可以在控制台打开对应列的数组选项开关，或使用 SDK 将对应列的 IsArray 设置为 true。写入数据时，数组值必须为 JSON 数组格式，例如 ["a","b","c"]。

数据类型映射

ARRAY_EXTRACT(col_name)

ARRAY_EXTRACT 将数组列展开后与运算符组合使用，作为 WHERE 子句的查询条件。支持等值（=）、范围（>、<）、LIKE 等运算符。

使用限制

• ARRAY_EXTRACT 只能在多元索引映射表上使用，且每次只能设置一个数组列参数。该函数只能作为 WHERE 子句的查询条件，不能作为 SELECT 列表达式，不能用于聚合和排序。

• 数组列本身（不使用 ARRAY_EXTRACT）可以作为 SELECT 的列名或列表达式，但不能用于聚合和排序。

• ARRAY_EXTRACT 函数与运算符组合作为查询条件时，不支持数据类型转换后的计算。查询值须与数组列的数据类型一致。例如长整型数组列支持 ARRAY_EXTRACT(col) = 1，不支持 ARRAY_EXTRACT(col) = '1'。

• Text 类型数组元素需结合 TEXT_MATCH 或 TEXT_MATCH_PHRASE 函数使用，例如 TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword')。

示例

-- 查询数组中包含值 'apple' 的行
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) = 'apple';

-- 查询数组中包含以 'd' 开头的元素的行
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) LIKE 'd%';

嵌套类型查询

嵌套类型列存储 JSON 数组，每个元素包含多个子列。数据表中该列的数据类型必须为字符串，创建多元索引时将其设置为嵌套类型并配置子列的数据类型。

创建映射关系时，推荐将嵌套类型列定义为 MEDIUMTEXT 类型。内部子列会自动创建，可通过 DESCRIBE 查看，如 col_nested.name、col_nested.age。查询时，子列名格式为 嵌套列名.子列名，多层嵌套用半角句号（.）连接，例如 col1.col2.col3。

数据类型映射

两种查询方式

SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

NESTED_QUERY(subcol_column_condition)

两种方式的区别

假设嵌套列 tags 有一行数据 [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]：

• tags.tagName — 匹配成功，因为第一个元素满足 tagName 条件，第二个元素满足 score 条件。

• NESTED_QUERY( — 不匹配，因为没有单个元素同时满足两个条件。

使用限制

• NESTED_QUERY 只能在多元索引映射表上使用，只能作为 WHERE 子句，不能作为 SELECT 列表达式，不能用于聚合、分组和排序。

• 嵌套子列不能作为 SELECT 列表达式，不能用于聚合、分组和排序。

• ALTER TABLE 不能直接添加或删除嵌套子列，只能操作整个嵌套列，子列随嵌套列自动添加或删除。

• 嵌套子列不支持数据类型转换后的计算，也不支持无法下推到多元索引的函数计算。确保嵌套子列对应的数据类型正确。

示例

-- 子列直接查询：查询嵌套列中 age > 30 的行
SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

-- NESTED_QUERY：查询同一元素同时满足 name 以 'I' 开头且 age < 20 的行
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col_nested.name` LIKE 'I%' AND `col_nested.age` < 20);

-- 多层嵌套
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));

虚拟列查询

多元索引虚拟列无需修改数据表存储结构，通过修改多元索引 Schema 即可实现新字段新类型的查询。虚拟列在映射表中按实际 SQL 数据类型定义。

数据类型映射

支持的用法

• 用于 WHERE 子句的条件过滤。条件中虚拟列的数据类型与查询参数类型必须一致。

• 用于聚合和分组，但虚拟列在多元索引中的原始数据类型必须满足对应要求。例如多元索引支持求和的类型为 Long 和 Double，Keyword 类型的虚拟列不能求和；Text 类型不支持分组。

• 支持 TopN 查询和排序，但排序必须配合 LIMIT 使用，不支持无 LIMIT 的排序。

使用限制

• 虚拟列仅支持在多元索引映射表中使用。

• 虚拟列仅支持用在查询条件中，不能用于 SELECT 返回列值。如需返回值，请指定虚拟列的原始列。SELECT * 不受影响，返回结果自动忽略虚拟列。

• 虚拟列不能进行列间比较、运算和 Join。

• 虚拟列不支持数据类型转换后的计算，也不支持无法下推到多元索引的函数计算。目前 SQL 查询仅支持下推聚合函数。

示例

创建包含虚拟列的多元索引映射表：

CREATE TABLE search_exampletable(
    col_keyword MEDIUMTEXT,
    col_keyword_virtual_long BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"exampletable_index","table_name":"exampletable"}';

使用虚拟列查询：

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

向量检索

使用 VECTOR_QUERY_FLOAT32 函数进行近似最近邻查询。向量字段在数据表中为字符串类型，在多元索引中须配置为向量类型并指定维度、数据类型和距离度量算法。映射表中向量列的 SQL 数据类型为 MEDIUMTEXT。

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

SCORE() 函数

配合 VECTOR_QUERY_FLOAT32 使用，作为 SELECT 列表达式返回查询结果的相关性分数，分数越大表示越相似。

SCORE()

使用限制

• VECTOR_QUERY_FLOAT32 只能在多元索引映射表上使用，必须配合 LIMIT，不支持 HAVING 子句。

• VECTOR_QUERY_FLOAT32 只能作为 WHERE 子句，不能作为 SELECT 列表达式，不能用于聚合、分组和排序。

• SCORE() 只能配合 VECTOR_QUERY_FLOAT32 使用，只能作为 SELECT 列表达式，不能用于 WHERE、聚合和排序。

• WHERE 子句中的其他条件必须能下推到多元索引执行，否则查询失败。关于支持下推的算子，请参见SQL查询优化。

示例

查询 col_vector 列与指定向量最相似的 10 个结果：

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 10) LIMIT 10;

使用 filter 先筛选再向量检索（filter 在向量检索前缩小候选集，结果更精确）：

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 100, col_keyword='cat_a' AND year_num=2024) LIMIT 10;

在 WHERE 中用 AND 附加过滤（从 topK 结果中筛选，topK 结果可能不包含所有满足条件的行）：

SELECT *, SCORE() FROM exampletable WHERE col_keyword='cat_a' AND VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 500) LIMIT 10;

JSON 函数

表格存储 SQL 的 JSON 函数遵循 MySQL 5.7 语法，可以从 JSON 格式的列中提取数据。支持以下函数。

->>（JSON 路径提取）

从 JSON 列中提取指定路径的值并取消引用转换为字符串，等效于 JSON_UNQUOTE(JSON_EXTRACT())。

column->>'$.path'

示例

SELECT col_json->>'$.city' AS city FROM exampletable LIMIT 10;

JSON_UNQUOTE

去除 JSON 值外层引号，返回字符串。如果参数为 NULL，则返回 NULL。

JSON_UNQUOTE(json_val)

示例

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable LIMIT 10;

JSON_EXTRACT

从 JSON 列中提取指定路径的子文档，返回值保留 JSON 格式（字符串值带引号）。支持同时提取多个 path，多个 path 的结果以数组格式返回。

JSON_EXTRACT(json_doc, path[, path] ...)

示例

提取单个路径：

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable WHERE pk = 1;

同时提取多个路径（结果以数组格式返回）：

-- 假设 col_json 的值为 {"a": 1, "b": 2, "c": {"d": 4}}
SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.a', '$.b', '$.c.d')) AS subdoc FROM exampletable WHERE pk = 1;
-- 返回结果：[1, 2, 4]

JSON Path 语法

path 必须以 $ 开头，$ 表示整个 JSON 文档。其后可添加路径选择器，选择器可组合使用。

JSON Object 查询示例

假设 JSON 列的值为 {"a": 1, "f": [1, 2, 3], "c": {"d": 4}}：

JSON Array 查询示例

假设 JSON 列的值为 [3, {"a": [5, 6], "b": 10}, [99, 100]]。当返回值为非标量值时，可以继续嵌套查询。