注:引敬爱的何老师的文章
案例表
如周边城市表的字段 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 类型的索引可以对数组进行:
- @> 是否包含某个数组元素
- <@ 某个数组元素是否包含于数组
- = 数组是否相当
- && 数组是否有交集
等操作。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的方法去解决问题,不过还得根据业务去分析,切不可强制套用,灵活运用才是王道!!!