本文主要討論PostgreSQL中大小寫不敏感存在的問題。

默認(rèn)情況下，PostgreSQL會將列名和表名全部轉(zhuǎn)換為小寫狀態(tài)。

圖1 Person與person

如圖1所示，我們創(chuàng)建表person，其中包含name列。然后插入一條記錄。執(zhí)行SELECT查詢時，使用列名Name和表名Person而不是name和person，發(fā)現(xiàn)仍然可以正常獲取剛剛插入表person中的記錄。

圖2 創(chuàng)建表Person？

此時如果我們再想創(chuàng)建表Person，會得到一個錯誤，因為此時PostgreSQL實際上把表名從Person轉(zhuǎn)換成了person。由于已經(jīng)存在表person，所以會報錯。

通常情況下，這種大小寫不敏感是很方便的，但是當(dāng)我們想創(chuàng)建大小寫敏感的表名和列名（需要使用雙引號）時，會產(chǎn)生一些問題。

圖3 創(chuàng)建表Person

如圖3所示，我們成功創(chuàng)建了表Person，并插入了一條記錄，此條記錄和插入person中的不同以示區(qū)分。再次使用SELECT查詢，并且使用表名Person和列名Name，但是返回的結(jié)果卻是person中的記錄。這還是因為PostgreSQL將Person轉(zhuǎn)換成了person。所以想要正確查詢，需要使用“Person”和“Name”（如圖4所示）。

圖4 獲取表Person中的記錄

此時查看數(shù)據(jù)庫中的表（見圖5），可以發(fā)現(xiàn)Person和person這兩個表都在數(shù)據(jù)庫中。如果我們使用DROP TABLE Person，刪除的仍然是表person。

圖5 刪除操作

綜上所述，當(dāng)創(chuàng)建表或者寫SQL查詢語句時，建議避免使用雙引號。

補充：PostgreSQL大小寫不敏感排序

pg12開始支持不區(qū)分大小寫，或者區(qū)分大小寫的排序的collate。

語法：

CREATE COLLATION [ IF NOT EXISTS ] name (
  [ LOCALE = locale, ]
  [ LC_COLLATE = lc_collate, ]
  [ LC_CTYPE = lc_ctype, ]
  [ PROVIDER = provider, ]
  [ DETERMINISTIC = boolean, ]
  [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

其中兩個關(guān)鍵參數(shù)：

PROVIDER：指定用于與此排序規(guī)則相關(guān)的區(qū)域服務(wù)的提供程序。可能的值是: icu、libc。 默認(rèn) 是libc。但若要設(shè)置大小寫不敏感，目前只支持icu。

DETERMINISTIC:設(shè)置成not deterministic表示大小寫不敏感。

例子：

—正常情況的排序

我們可以看到，正常的order by會區(qū)分大小寫。

bill@bill=>create table test (c1 text); 
CREATE TABLE
bill@bill=>insert into test values ('a'),('b'),('c'),('A'),('B'),('C'); 
INSERT 0 6
bill@bill=>select * from test order by c1;
 c1 
----
 A
 B
 C
 a
 b
 c
(6 rows)

同樣，在oracle中也是一樣：

SQL> select * from test order by c1;

C1
--------------------------------------------------------------------------------
A
B
C
a
b
c

6 rows selected.

—不區(qū)分大小寫排序

可以看到我們指定collate為zh_CN時便沒有區(qū)分大小寫排序。

bill@bill=>select * from test order by c1 collate "zh_CN"; 
 c1 
----
 a
 A
 b
 B
 c
 C
(6 rows)

我們也可以自定義collation支持不區(qū)分大小寫的排序，但是需要注意在編譯數(shù)據(jù)庫的時候加上 —with-icu才可以，否則會出現(xiàn)報錯：

bill@bill=>CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false);
ERROR: ICU is not supported in this build
HINT: You need to rebuild PostgreSQL using --with-icu.

正常情況：

bill@bill=> CREATE COLLATION case_insensitive (provider = icu, locale = 'zh_Hans', deterministic = false); 
CREATE COLLATION 
bill@bill=> select * from test order by c1 collate "case_insensitive"; 
 c1  
---- 
 a 
 A 
 b 
 B 
 c 
 C 
(6 rows)

目前collate不支持=操作不區(qū)分大小寫，目前需要citext插件。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

最新評論