一、长字符串行转列方案

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, ‘,’, ‘“,”’), ‘"]’),
‘$[\ast {]}^{\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 索引 方案，结合虚拟列和合适的索引类型可显著提升性能。注意版本兼容性，避免低效查询模式。