淺談PostgreSQL中大小寫不敏感問題

本文主要討論P(yáng)ostgreSQL中大小寫不敏感存在的問題。

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

圖1 Person與person

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

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

此時(shí)如果我們?cè)傧雱?chuàng)建表Person，會(huì)得到一個(gè)錯(cuò)誤，因?yàn)榇藭r(shí)PostgreSQL實(shí)際上把表名從Person轉(zhuǎn)換成了person。由于已經(jīng)存在表person，所以會(huì)報(bào)錯(cuò)。

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

圖3 創(chuàng)建表Person

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

圖4 獲取表Person中的記錄

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

圖5 刪除操作

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

補(bǔ)充：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

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

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

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

例子：

—正常情況的排序

我們可以看到，正常的order by會(huì)區(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時(shí)便沒有區(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ù)庫的時(shí)候加上 —with-icu才可以，否則會(huì)出現(xiàn)報(bào)錯(cuò)：

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插件。

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