MySQL 5.7下SELECT语句搜索JSON数组匹配跨表字段值的方法

核心报错原因 #

JSON_CONTAINS 第二个参数仅支持传入单个JSON类型值，无法直接接收返回多行结果的子查询，因此直接传入t2全表ID的子查询会触发数据类型错误。

推荐实现方案 #

方案1：逐行匹配（性能最优，适配存储过程批量处理场景） #

不需要将JSON数组转换为行集，反向遍历待匹配的t2表逐行判断即可，完全规避多行子查询问题，只要t2表ID字段存在索引，即使数据量较大也能保持高性能。
示例代码：

-- 存储过程中可将该变量替换为入参
SET @input_jdoc = '{"a": 17, "b": "red", "x": [3, 5, 7]}';

-- 查询t2中所有不存在于x数组内的ID
SELECT ID
FROM t2
WHERE JSON_CONTAINS(
  JSON_EXTRACT(@input_jdoc, '$.x'),
  CAST(ID AS JSON),
  '$'
) = 0;

如果需要关联t1表，判断每条JSON文档对应的x数组与t2ID的匹配关系，直接用交叉关联逐行计算即可：

SELECT 
  t1.*,
  JSON_EXTRACT(t1.jdoc, '$.x') AS extract_x,
  t2.ID AS t2_id,
  JSON_CONTAINS(JSON_EXTRACT(t1.jdoc, '$.x'), CAST(t2.ID AS JSON), '$') AS id_exists_in_x
FROM t1, t2;

注意：匹配时必须将表中ID字段通过CAST(xxx AS JSON)转为JSON标量值，避免隐式类型转换导致的匹配错误（比如字符串格式数字和数值型数字匹配失败的问题）。

方案2：辅助序列表拆分JSON数组（适配复杂集合运算场景） #

如果业务逻辑必须将JSON数组转换为行值列表处理，可以通过预建整数序列表的方式模拟MySQL 8.0的JSON_TABLE功能，一次建表可永久复用。

1. 先创建辅助整数序列表，按需填充足够的连续整数，最大数值需大于业务中JSON数组的最大长度：

CREATE TABLE IF NOT EXISTS num_seq (
  n INT UNSIGNED NOT NULL PRIMARY KEY
);
-- 示例填充0-999，支持最大长度1000的JSON数组，可按需扩展位数
INSERT IGNORE INTO num_seq(n)
SELECT ones.n + 10*tens.n + 100*hundreds.n
FROM
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) ones,
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens,
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds;

2. 通过序列下标拆分JSON数组为行集，再关联t2表做匹配：

SET @input_jdoc = '{"a": 17, "b": "red", "x": [3, 5, 7]}';

SELECT t2.ID
FROM t2
LEFT JOIN (
  SELECT
    CAST(JSON_UNQUOTE(JSON_EXTRACT(@input_jdoc, CONCAT('$.x[', n, ']'))) AS UNSIGNED) AS x_id
  FROM num_seq
  WHERE JSON_EXTRACT(@input_jdoc, CONCAT('$.x[', n, ']')) IS NOT NULL
) x_arr ON t2.ID = x_arr.x_id
WHERE x_arr.x_id IS NULL;

选型建议 #

• 仅做存在性判断、批量数据校验的存储过程场景优先选方案1，无额外建表成本，性能最高
• 需要对JSON数组内的元素做分组、聚合等复杂运算时再选方案2

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