MySQL 8.0.32中如何为JSON列创建索引实现高效数据筛选？

错误原因 #

你使用的JSON_ARRAYAGG是聚合函数，作用是对多行数据做汇总计算，但生成列只能基于当前行的字段值计算，不能跨行列聚合，因此触发错误代码1111（Invalid use of group function）。

正确解决方案（基于MySQL 8.0.32） #

针对筛选info数组中存在skill="python"且exp="1"的候选人需求，推荐三种高效实现方式：

方式1：多值索引（推荐，灵活高效）

MySQL 8.0.17及以上支持多值索引，可直接对JSON数组元素创建索引，无需修改原有数据结构：

1. 创建多值索引：

CREATE INDEX idx_info_skill_exp ON candidate ((info->>'$[*]'));

info->>'$[*]'会提取info数组中的所有对象，多值索引会为每个对象建立索引条目。

2. 查询语句：

SELECT * FROM candidate
WHERE JSON_CONTAINS(info, '{"skill": "python", "exp": "1"}');

该查询会利用多值索引快速定位符合条件的行。

方式2：固定条件虚拟生成列+索引

如果仅需针对skill="python"且exp="1"这个固定条件查询，可创建布尔型虚拟生成列并加索引：

1. 添加生成列并创建索引：

ALTER TABLE candidate
ADD COLUMN has_python_exp1 BOOLEAN GENERATED ALWAYS AS (
  JSON_CONTAINS(info, '{"skill": "python", "exp": "1"}')
) VIRTUAL,
ADD INDEX idx_has_python_exp1 (has_python_exp1);

2. 查询语句：

SELECT * FROM candidate WHERE has_python_exp1 = 1;

这种方式索引体积小、查询速度快，但仅适用于固定条件，无法灵活匹配其他skill/exp组合。

方式3：拆分JSON为关联表（长期最优方案）

若需频繁进行复杂的技能/经验筛选，建议将JSON数组拆分为关联表：

1. 创建关联表：

CREATE TABLE candidate_skills (
  id INT AUTO_INCREMENT PRIMARY KEY,
  candidate_id INT NOT NULL,
  skill VARCHAR(50) NOT NULL,
  exp VARCHAR(10) NOT NULL,
  FOREIGN KEY (candidate_id) REFERENCES candidate(id)
);

2. 将原info中的数据导入该表，然后创建复合索引：

CREATE INDEX idx_candidate_skill_exp ON candidate_skills (skill, exp);

这种结构支持所有复杂筛选，查询效率达到最优。

内容的提问来源于stack exchange，提问作者anil