MariaDB中EXISTS+BETWEEN筛选JSON距离范围异常问题排查

核心问题定位 #

这种异常的本质是JSON_TABLE解析出的距离值默认是字符串类型，而非数值类型，导致BETWEEN按字符串规则执行比较，而非预期的数值规则。

比如你的event_distances数组若为["15", "22", "8"]，字符串比较逻辑下：

• 范围0-22能匹配所有值（字符串"8"、"15"都小于"22"）
• 范围20-22时，"15"和"8"作为字符串会被判定为小于"20"（字符串逐字符对比，'1'<'2'、'8'<'2'），仅"22"符合，但如果你的数据里没有恰好等于"22"的项，就会返回空结果。

修复方案 #

在JSON_TABLE中显式将解析出的距离值转为数值类型（如DECIMAL或INT），再执行范围比较。

正确查询示例（直接指定数值类型） #

SELECT *
FROM events
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        events.event_distances,
        '$[*]' COLUMNS (distance DECIMAL(10,2) PATH '$')
    ) AS jt
    WHERE jt.distance BETWEEN 20 AND 22
);

备选方案（通过CAST转换类型） #

如果需要保留原解析逻辑，也可以在比较时强制转换类型：

SELECT *
FROM events
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        events.event_distances,
        '$[*]' COLUMNS (distance VARCHAR(10) PATH '$')
    ) AS jt
    WHERE CAST(jt.distance AS DECIMAL(10,2)) BETWEEN 20 AND 22
);

验证方法 #

可以先单独查询解析后的数据类型，确认问题根源：

SELECT distance, typeof(distance)
FROM events, JSON_TABLE(
    events.event_distances,
    '$[*]' COLUMNS (distance PATH '$')
) AS jt;

若typeof(distance)返回string，则坐实是字符串类型导致的比较异常。

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