postgreSQL ARRAY 型別的使用

NO IMAGE

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的方法去解決問題,不過還得根據業務去分析,切不可強制套用,靈活運用才是王道!!!