SQL中如何从存储为数组的列中筛选指定值？

表结构及数据 #

pkid | columnValue
1     ('1', '11')
2     ('2', '11')

问题说明 #

需要编写SELECT语句，仅取出columnValue中包含'1'的行。当前遇到的问题：

• 使用LIKE '%1%'会返回两行（第二行的'11'包含字符'1'），不符合需求；
• 使用WHERE columnValue = '1'无结果返回（columnValue是数组类型，直接匹配字符串'1'不生效）。

解决方案 #

假设columnValue是数组类型（比如PostgreSQL的text数组），可以使用数组专用查询语法：

方法1：使用数组包含操作符@> #

SELECT * FROM tbl WHERE columnValue @> ARRAY['1']::text[];

方法2：使用ANY函数匹配数组元素 #

SELECT * FROM tbl WHERE '1' = ANY(columnValue);

原理说明 #

• 数组包含操作符@>会检查目标数组是否包含指定元素，实现精准匹配；
• ANY函数遍历数组中的每个元素，判断是否存在与指定值相等的元素，同样能达到精准筛选的效果。

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