postgreSQL ARRAY 类型的使用

postgreSQL常用函数及操作

注：引敬爱的何老师的文章

案例表

如周边城市表的字段 id,city_zone,around_city;

$select * from public.around_city limit 3;
 id | city_zone |                                      around_city                                       
----+-----------+---------------------------------------------------------------------
  1 | 京津冀    | 北京|唐山|石家庄|张家口|邯郸|沧州|承德|保定|廊坊|秦皇岛|邢台|衡水|天津
  2 | 浙江      | 台州|金华|嘉兴|丽水|宁波|湖州|杭州|温州|衢州|绍兴|舟山
  3 | 沪皖      | 上海|黄山|马鞍山|池州|六安|安庆|亳州|合肥|宣城|蚌埠|淮南|滁州|阜阳|淮北|芜湖

需求

找出所有 around_city 里头包含 丽水、六安、镇江 的数据。

解法一

使用 sql 的 or：

select id from public.around_city where around_city ~ '丽水' or around_city ~ '六安' or around_city ~ '镇江';

解法优点：思考简单，起点要求底，逻辑清楚
解法缺点：程序拼接SQL痛苦，SQL 显得很长，拼接的代码也会相对复杂，速度很慢

解法二

使用postgreSQL 的正则：

select id from public.around_city where around_city ~ '丽水|六安|镇江';

正则本身是可以实现“或”的逻辑的，因此，可以利用正则实现 sql 的 OR 的语意。

解法优点：
1、需要扩展的知识并不多，增加对正则的了解即可
2、sql比较短、紧凑，拼接 sql 的代码会比较简单
3、逻辑清楚
解法缺点：
1、难度提高一倍，知识点还是比sql or 的方案多一个：必须相对充分了解正则
2、速度几乎无提升

解法三

数组的办法，PostgreSQL 有很强的数据的处理能力，尤其是 gin 类型的索引可以对数组进行：

1. @> 是否包含某个数组元素
2. <@ 某个数组元素是否包含于数组
3. = 数组是否相当
4. && 数组是否有交集
   等操作。gin 在历史上是PostgreSQL的全文索引的索引类型，实际上，稍微想像就容易理解：数组本质上和全文索引没有区别，都是全文索引是分词之后做倒排，而数组只不过是天然就分好了的，数组有个优点是数据类型比较丰富。

select id from public.around_city where string_to_array(around_city,'|') && ARRAY['六安','镇江','丽水'];

建gin索引，如下：

create index CONCURRENTLY on public.around_city using gin (string_to_array(around_city,'|'));

方法对比

假设数据量比较多的时候，方法一与方法二的查询速度是按数据量变大而变慢，而方法三使用gin索引，可以迅速匹配出，不过gin索引肯定会占取额外的空间。【不同的需求，不同的场景，解决方式多样化，能合理解决问题的方法，其解法就是最优解。】

个人总结

学习了数组的操作，收获满满，希望以后可以用更多的地方使用这种牛x的方法去解决问题，不过还得根据业务去分析，切不可强制套用，灵活运用才是王道！！！