MariaDB 10.1无JSON函数时按JSON键值范围查询TEXT字段记录方案

适用场景 #

MariaDB 10.1版本未内置JSON系列操作函数，针对TEXT类型存储的固定结构JSON数据，可以通过字符串截取/正则匹配的方式提取指定键的值做范围判断。

方案1：SUBSTRING_INDEX 分层截取（兼容性最好） #

该方案不需要依赖正则函数，所有MariaDB 10.1版本都支持，前提是你的JSON存储格式固定，"880"键和对应值之间的格式无多余空格，固定为"880": "值"的形式：

SELECT * 
FROM CONTRACTS 
WHERE CAST(
  -- 先截取"880": "后面的所有内容，再从结果里截取第一个"之前的内容，就是键880对应的值
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(data, '"880": "', -1),
    '"', 
    1
  ) AS UNSIGNED
) > 10000 
AND CAST(
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(data, '"880": "', -1),
    '"', 
    1
  ) AS UNSIGNED
) < 20000;

如果你的JSON中键值之间存在空格（比如"880" : "15255"），只需要把SUBSTRING_INDEX的第二个参数改成'"880" : "'即可。

方案2：正则匹配（适配格式不固定场景） #

如果你的JSON存储格式不固定，键和值之间可能存在任意数量的空格，可以使用MariaDB 10.1支持的REGEXP_SUBSTR函数提取值：

SELECT *
FROM CONTRACTS
WHERE CAST(
  -- 正则匹配任意空格间隔的"880"键，捕获对应的值内容
  REGEXP_SUBSTR(data, '"880"\\s*:\\s*"([^"]+)"', 1, 1, 'i', 1) AS UNSIGNED
) BETWEEN 10001 AND 19999;

注意事项 #

• 如果880对应的值在JSON中是数字类型（无引号包裹，比如"880": 15255），需要调整截取逻辑或正则规则，把匹配引号的部分去掉即可。
• 两种方案都需要全表扫描做字符串处理，数据量大的场景建议后续升级到MariaDB 10.2及以上版本，使用原生JSON_EXTRACT函数+JSON索引优化查询性能。

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