多元索引操作

支援的操作

全文檢索索引

全文檢索索引匹配 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 不能直接添加或刪除嵌套子列，只能操作整個嵌套列，子列隨嵌套列自動添加或刪除。

• 嵌套子列不支援資料類型轉換後的計算，也不支援無法下推到多元索引的Function Compute。確保嵌套子列對應的資料類型正確。

樣本

-- 子列直接查詢：查詢嵌套列中 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。

• 虛擬列不支援資料類型轉換後的計算，也不支援無法下推到多元索引的Function Compute。目前 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 函數

Table Store SQL 的 JSON 函數遵循 MySQL 5.7 文法，可以從 JSON 格式的列中提取資料。支援以下函數。

->>（JSON 路徑提取）

從 JSON 列中提取指定路徑的值並取值 (Dereference)轉換為字串，等效於 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]]。當傳回值為非標量值時，可以繼續巢狀查詢。