PostgreSQL DBA(124) - Develop(Putting multiple LIKE patterns into an array)

husthxd發表於2019-11-07

本節簡單介紹了PostgreSQL中的Putting multiple LIKE patterns into an array特性。

LIKE用於模糊查詢，如select * from tablename where column like '%value%'；如需要多個模糊匹配則需要使用多個like子句，如select * from tablename where column like '%value1%' or column like '%value2%'  or column like '%value3%'或者select * from tablename where column like '%value1%' and column like '%value2%'  and column like '%value3%'。PG提供了ANY/ALL結合ARRAY來簡化SQL：

select * from tablename where column like '%value1%' or column like '%value2%'  or column like '%value3%'
->
select * from tablename where column like ANY(ARRAY['%value1%','%value2%','%value3%'])

select * from tablename where column like '%value1%' and column like '%value2%'  and column like '%value3%'
->
select * from tablename where column like ALL(ARRAY['%value1%','%value2%','%value3%'])

測試指令碼
```
[local]:5432 pg12@testdb=# create table t_like (id int,c1 varchar(20));
CREATE TABLE
Time: 160.410 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_like(id,c1) values(1,'asdfafetestsdfasdf');
INSERT 0 1
Time: 14.815 ms
[local]:5432 pg12@testdb=# insert into t_like(id,c1) values(2,'asdfafe測試sdfasdf');
INSERT 0 1
Time: 1.423 ms
[local]:5432 pg12@testdb=# insert into t_like(id,c1) values(3,'astestfe測試sdfasdf');
INSERT 0 1
Time: 1.486 ms
[local]:5432 pg12@testdb=# select * from t_like where c1 like ANY(ARRAY['%test%','%測試%']);
 id |         c1          
----+---------------------
  1 | asdfafetestsdfasdf
  2 | asdfafe測試sdfasdf
  3 | astestfe測試sdfasdf
(3 rows)

Time: 48.319 ms
[local]:5432 pg12@testdb=# select * from t_like where c1 like ALL(ARRAY['%test%','%測試%']);
 id |         c1          
----+---------------------
  3 | astestfe測試sdfasdf
(1 row)

Time: 2.463 ms
[local]:5432 pg12@testdb=#

```

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2663070/，如需轉載，請註明出處，否則將追究法律責任。

Oracle vs PostgreSQL Develop（17） - ARRAY
2019-08-23
OracleSQLdev
PostgreSQL DBA(169) - Develop(Distinct vs Group by)
2020-03-13
SQLdev
PostgreSQL DBA(136) - Develop(Common Mistakes)
2019-12-03
SQLdev
PostgreSQL DBA(99) - Develop(generate random string)
2019-09-26
SQLdevrandom
PostgreSQL DBA(122) - Develop(EDB package)
2019-11-06
SQLdevPackage
PostgreSQL DBA(155) - Develop（“大表”連線）
2020-01-07
SQLdev
PostgreSQL DBA(135) - Develop(Avoiding “OR” for better query)
2019-11-21
SQLdev
PostgreSQL DBA(118) - Develop(Seamless Application Failover)
2019-10-31
SQLdevAPPAI
PostgreSQL DBA(131) - Develop(numeric vs float vs int)
2019-11-18
SQLdev
PostgreSQL DBA(127) - Develop(JDBC failover&load balance)
2019-11-13
SQLdevJDBCAI
Would you like to develop a story for your character?
2020-02-11
dev
Oracle vs PostgreSQL Develop（18） - Boolean
2019-09-23
OracleSQLdevBoolean
Oracle vs PostgreSQL Develop（15） - DISTINCT ON
2019-08-22
OracleSQLdev
Array-like類陣列物件
2017-02-21
陣列物件
Oracle vs PostgreSQL Develop（26） - ASCII 0
2019-12-25
OracleSQLdevASCII
Oracle vs PostgreSQL Develop（20） - Materialized View
2019-11-01
OracleSQLdevZedView
Oracle vs PostgreSQL Develop（19） - PIPE ROW
2019-10-31
OracleSQLdev
Oracle vs PostgreSQL Develop（16） - Prepared Statement
2019-08-22
OracleSQLdev
Oracle vs PostgreSQL Develop（28） - function overload
2020-02-28
OracleSQLdevFunction
Oracle vs PostgreSQL Develop（31） - Index Only Scan
2022-04-20
OracleSQLdevIndex
Oracle vs PostgreSQL Develop（29） - COMMIT in PLSQL
2021-09-18
OracleSQLdevMIT
JavaScript 特殊物件 Array-Like Objects 詳解
2016-06-26
JavaScript物件Object
PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQL
2019-06-27
SQLIndex
Oracle vs PostgreSQL Develop（24） - subquery with agg function
2019-12-23
OracleSQLdevFunction
Oracle vs PostgreSQL Develop（30） - Index&Case when
2022-04-11
OracleSQLdevIndex
PostgreSQL DBA(35) - CTE
2019-06-03
SQL
PostgreSQL DBA(42) - locale
2019-06-25
SQL
PostgreSQL DBA(133) - Extension(postgresql_anonymizer)
2019-11-19
SQL
PostgreSQL DBA(59) - Index(Bloom)
2019-07-18
SQLIndexOOM
PostgreSQL DBA(53) - Index(BRIN)
2019-07-11
SQLIndex
PostgreSQL DBA(58) - DBLink
2019-07-18
SQL
PostgreSQL DBA(48) - Index(GiST)
2019-07-04
SQLIndex
PostgreSQL DBA(51) - Index(GIN)
2019-07-09
SQLIndex
PostgreSQL DBA(52) - Index(RUM)
2019-07-10
SQLIndex
PostgreSQL DBA(47) - Index(Btree)
2019-07-02
SQLIndex
PostgreSQL DBA(43) - Index(Hash)
2019-06-26
SQLIndex
PostgreSQL DBA(191) - Collation
2022-04-30
SQL
PostgreSQL DBA(182) - HOT
2021-05-24
SQL

PostgreSQL DBA(124) - Develop(Putting multiple LIKE patterns into an array)

相關文章