H2数据库中如何高效查找包含特定值集合的无序数组？

在H2数据库里，确实有几种高效且易维护的方法来查找包含特定值集合的数组，无需考虑元素顺序，下面是具体方案：

方案1：排序后比较数组 #

H2提供了ARRAY_SORT()函数可以对数组元素排序，你可以将存储的数组和目标数组分别排序后再比较，这样就能忽略元素顺序的差异。

示例代码：

-- 查找包含1和2的数组（元素数量一致，无多余元素）
SELECT id, arr FROM test 
WHERE ARRAY_SORT(arr) = ARRAY_SORT(ARRAY[1, 2]);

执行后会返回id=1的行，因为排序后的[2,1]和[1,2]完全一致。

这个方法的优势是代码简洁、逻辑直观；缺点是如果数组元素数量很大，排序操作会带来一定性能开销，适合中小规模的数组场景。

方案2：利用集合包含关系（精确匹配） #

如果需要精确匹配数组的元素集合（即数组刚好包含目标值，没有多余元素），可以结合MEMBER OF运算符和数组长度检查：

示例代码：

SELECT id, arr FROM test 
WHERE 
  1 MEMBER OF arr 
  AND 2 MEMBER OF arr
  AND CARDINALITY(arr) = 2;

MEMBER OF用于检查目标值是否存在于数组中，CARDINALITY()获取数组长度，两者结合确保数组刚好包含目标值集合。

方案3：数组交集匹配（支持包含目标元素的场景） #

如果允许数组包含额外元素，只需要确保目标值都存在于数组中，可以使用ARRAY_INTERSECT()函数计算交集，再判断交集的长度是否等于目标数组的长度：

示例代码：

-- 查找所有包含1和2的数组（允许有其他元素）
SELECT id, arr FROM test 
WHERE CARDINALITY(ARRAY_INTERSECT(arr, ARRAY[1, 2])) = 2;

ARRAY_INTERSECT()返回两个数组的交集元素，当交集长度等于目标数组长度时，说明目标所有元素都存在于存储的数组中。

性能说明 #

• 如果你的数组字段创建了索引，MEMBER OF和ARRAY_INTERSECT的方法可能更易利用索引优化（H2对数组的索引支持有限，建议实际测试）；
• 排序方法的性能依赖数组大小，小规模数组几乎无影响，大规模数组建议优先考虑其他方案。

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