MySQL JSON字段加索引后Django查询返回空结果的原因与解决

问题背景 #

在MySQL 8.0.29（Windows环境）中，给JSON字段添加索引后，Django生成的部分查询返回空结果，删除索引后查询恢复正常。可通过以下SQL复现问题：

CREATE TABLE `products` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `status` varchar(32) DEFAULT NULL,
  `info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

ALTER TABLE products ADD INDEX tag_group_idx ((
    CAST(info->>'$.\"Group\"' as CHAR(32)) COLLATE utf8mb4_bin
)) USING BTREE;

INSERT INTO products (status, info) VALUES('active', '{\"Group\": \"G0001\"}');

三种查询测试结果 #

1. Django生成的查询（返回空）

SELECT * FROM products
WHERE status = 'active' AND
JSON_EXTRACT(info,'$.\"Group\"') = JSON_EXTRACT('\"G0001\"', '$');

结果：无数据返回

2. 普通字符串匹配查询（返回正常）

SELECT * FROM products
WHERE status = 'active' AND
JSON_EXTRACT(info,'$.\"Group\"') = 'G0001';

结果：返回id=1的记录

3. 移除status条件的查询（返回正常）

SELECT * FROM products
WHERE JSON_EXTRACT(info,'$.\"Group\"') = JSON_EXTRACT('\"G0001\"', '$');

结果：返回id=1的记录

删除索引后，第一种查询恢复正常：

ALTER TABLE products DROP INDEX tag_group_idx;
SELECT * FROM products
WHERE status = 'active' AND
JSON_EXTRACT(info,'$.\"Group\"') = JSON_EXTRACT('\"G0001\"', '$');

真实原因 #

核心问题是索引生成列的类型与查询中JSON_EXTRACT返回值的类型不匹配，导致MySQL使用索引时发生类型转换错误：

• 你的索引基于CAST(info->>'$.\"Group\"' as CHAR(32))创建，->>运算符直接返回不带引号的字符串（即G0001），再转成CHAR类型存入索引。
• Django生成的查询里，JSON_EXTRACT(info,'$.\"Group\"')返回的是带引号的JSON字符串类型（即"G0001"），右侧的JSON_EXTRACT('\"G0001\"', '$')同样返回JSON字符串类型。
• 当MySQL尝试使用tag_group_idx索引时，会把查询条件中的JSON字符串强制转为CHAR类型，此时带引号的"G0001"转成CHAR后是"G0001"，和索引里存储的G0001不匹配，因此查不到数据。
• 移除status条件或删除索引时，MySQL走全表扫描，此时会进行JSON类型原生比较：两个JSON字符串类型的"G0001"是相等的，所以能查到数据。

解决办法 #

方法1：修改索引定义，匹配JSON类型 #

把索引改成基于JSON类型的表达式，避免类型转换：

ALTER TABLE products ADD INDEX tag_group_idx ((info->'$.\"Group\"')) USING BTREE;

这里用->运算符（返回JSON类型值），和查询中JSON_EXTRACT的返回类型一致，MySQL使用索引时能正确匹配。

方法2：修改Django查询逻辑，使用字符串匹配 #

在Django中直接用字符串值匹配JSON字段的对应键，避免生成带JSON_EXTRACT的查询：

# 推荐写法：直接通过键名匹配字符串
Product.objects.filter(status='active', info__Group='G0001')

这种写法会生成类似第二种测试的SQL（直接和字符串'G0001'比较），能正确使用原有的CHAR类型索引。

方法3：强制查询不使用索引（不推荐） #

如果暂时无法修改索引或查询逻辑，可通过FORCE INDEX强制MySQL不使用该索引，但会影响查询性能：

SELECT * FROM products FORCE INDEX (PRIMARY)
WHERE status = 'active' AND
JSON_EXTRACT(info,'$.\"Group\"') = JSON_EXTRACT('\"G0001\"', '$');

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