一、長字符串行轉列方案

1. JSON_TABLE 方案（TiDB 5.0+ 推薦）
   通過將逗號分隔字符串轉為 JSON 數組后展開為行：

sql
SET @str = ‘a,b,c,d’;
SELECT jt.val, jt.pos
FROM JSON_TABLE(
CONCAT(‘[’, REPLACE(@str, ‘,’, ‘“,”’), ‘"]’),
‘$[?{]}^{\prime }COLUMNS(valVARCHAR(255)PAT{H}^{\prime }$’,
pos INT PATH ‘$.ordinality’
)
) AS jt;

核心原理：利用 JSON_TABLE 將數組元素映射為行，支持位置信息提取，性能接近 O (log n)。
2. 遞歸 CTE 方案（通用場景）
適用于無內置函數的數據庫，通過遞歸拆分字符串：

sql
SET @str = ‘a,b,c’;
WITH RECURSIVE split_cte AS (
SELECT 1 AS pos, SUBSTRING_INDEX(@str, ‘,’, 1) AS val
UNION ALL
SELECT pos+1, SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ‘,’, pos+1), ‘,’, -1)
FROM split_cte WHERE pos < LENGTH(@str) - LENGTH(REPLACE(@str, ‘,’, ‘’))
)
SELECT * FROM split_cte;
二、JSON 數據查詢優化

1. JSON 索引創建與使用
   sql
   – 為 JSON 數組元素創建索引
   CREATE INDEX idx_json ON table_name((CAST(json_col->‘$.array[*]’ AS CHAR)));

– 查詢優化：直接匹配 JSON 路徑
SELECT * FROM table_name WHERE json_col->“$.array[*]” = ‘target_value’;

索引優勢：

時間復雜度從全表掃描的 O (n) 降至索引掃描的 O (log n)。
示例：100 萬行數據查詢耗時從 7.2s 優化至 6ms。
2. JSON_SEARCH 與索引對比
方法 匹配邏輯 索引支持 性能
JSON_SEARCH 搜索值并返回路徑 不支持索引 O (n)（全表掃描）
->“…[*]” = value 數組元素精確匹配 支持 JSON 索引 O(log n)
三、全文索引（Full-Text Index）注意事項

1. 版本兼容性
   TiDB 5.1+ 支持全文索引，低版本（如 v8.5.1）不支持，會報錯 UnknownType: *ast.MatchAgainst。
   替代方案：使用 JSON 索引或拆分存儲為關聯表。
2. 正確用法（TiDB 5.1+）
   sql
   – 創建虛擬列與全文索引
   ALTER TABLE table_name
   ADD COLUMN text_col TEXT GENERATED ALWAYS AS (REPLACE(json_col, ‘,’, ’ ')) VIRTUAL;
   ALTER TABLE table_name ADD FULLTEXT INDEX idx_text(text_col);

– 查詢示例
SELECT * FROM table_name WHERE MATCH(text_col) AGAINST(‘keyword’ IN BOOLEAN MODE);
四、虛擬列（Generated Column）索引優化

1. 創建虛擬列并加索引
   sql
   – 基于 JSON 路徑創建虛擬列
   ALTER TABLE table_name
   ADD COLUMN virtual_col TEXT
   GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(json_col, ‘$.path’))) VIRTUAL;

– 添加索引
CREATE INDEX idx_virtual ON table_name(virtual_col);
2. 查詢優化示例
sql
WITH split_data AS (-- 字符串拆分邏輯…)
SELECT s.id, MAX(e.paas_id)
FROM split_data s
LEFT JOIN table_name e ON e.virtual_col = s.target_value
GROUP BY s.id;

性能對比：虛擬列 + 索引查詢耗時較無索引方案提升 100+ 倍。
五、常見錯誤與解決方案

1. 錯誤 1105: UnknownType: ast.MatchAgainst
   原因：TiDB 版本 < 5.1 不支持全文索引。
   解決方案：
   升級至 TiDB 5.1+。
   改用 JSON 索引：CREATE INDEX … ON ((CAST(json_col->'$.path[]’ AS CHAR)))。
2. 索引不生效問題
   檢查點：
   索引表達式與查詢條件是否一致（如是否遺漏 CAST 或 JSON_UNQUOTE）。
   執行計劃是否顯示 IndexRangeScan（使用 EXPLAIN SELECT … 驗證）。
   六、性能優化最佳實踐
   數據模型優化：
   頻繁查詢的字符串建議存儲為 JSON 數組，而非純字符串。
   拆分存儲：將逗號分隔字符串拆分為關聯表（如 id-split_id 表），支持高效索引。
   索引維護：
   sql
   ANALYZE TABLE table_name; – 更新統計信息

避免反模式：
禁止 LIKE ‘%keyword%’（全表掃描），改用前綴匹配或全文索引。
減少 JSON_SEARCH 嵌套調用，直接使用 JSON 路徑匹配。

總結：TiDB 中處理字符串行轉列與 JSON 數據時，優先選擇 JSON_TABLE + JSON 索引 方案，結合虛擬列和合適的索引類型可顯著提升性能。注意版本兼容性，避免低效查詢模式。