因此,我几乎没有使用PostgreSQL的Debian服务器。从历史上看,这些服务器和PostgreSQL是使用Latin 9字符集进行本地化的,那时还不错。现在我们必须处理波兰语,希腊语或中文这样的问题,因此将其更改正成为一个日益严重的问题。

当我尝试创建UTF8数据库时,我收到消息:


错误:编码UTF8与语言环境fr_FR不匹配详细信息:所选的LC_CTYPE设置需要编码LATIN9。


我几次没有与我的老朋友Google对该主题进行过一些研究,而我所能找到的只是一些过于复杂的过程,例如更新Debian LANG,使用正确的字符集重新编译PostgreSQL,编辑所有LC_系统变量和其他晦涩的解决方案。因此,暂时,我们将这个问题搁置一旁。

最近,它又回来了,希腊人想要的东西而拉丁9人不想。当我再次研究这个问题时,一位同事走过来对我说:“对,这很简单,看。”

他什么也没编辑,没有做魔术,他只是编写了此SQL查询:

CREATE DATABASE my_utf8_db
  WITH ENCODING='UTF8'
       OWNER=admin
       TEMPLATE=template0
       LC_COLLATE='C'
       LC_CTYPE='C'
       CONNECTION LIMIT=-1
       TABLESPACE=pg_default;


它运行良好。

我实际上对LC_CTYPE='C'一无所知,我很惊讶使用它不是在Google的第一个解决方案上,甚至在Stack Overflow上。我环顾四周,只在PostgreSQL文档中找到了提及。


当LC_CTYPE为C或POSIX时,允许任何字符集,但对于LC_CTYPE的其他设置,只有一个字符设置将正常工作。由于initdb冻结了LC_CTYPE设置,因此在集群的不同数据库中使用不同编码的明显灵活性要比实际更具理论性,除非您选择了C或POSIX语言环境(从而禁用了任何实际语言环境感知)。


这让我感到奇怪,这太容易了,太完美了,不利之处是什么?而且,我很难找到答案。所以我在这里发布:

tl; dr:在特定的本地化环境中使用LC_CTYPE='C'有何弊端?这样做不好吗?我应该打破什么?

#1 楼


在特定的本地化上使用LC_CTYPE ='C'的缺点是什么?

文档中提到了语言环境与Locale Support中的SQL功能之间的关系:

区域设置会影响以下SQL功能:


使用ORDER BY或文本数据的标准比较运算符对查询的排序顺序


upper,lower和initcap函数


模式匹配运算符(LIKE,SIMILAR TO和POSIX样式的正则表达式);语言环境会影响不区分大小写的匹配和字符类正则表达式对字符的分类


to_char系列函数


使用的能力带有LIKE子句的索引



第一项(排序顺序)与LC_COLLATE有关,而其他似乎与LC_CTYPE有关。
LC_COLLATE
LC_COLLATE影响字符串之间的比较。实际上,最明显的效果是排序顺序。 LC_COLLATE='C'(或POSIX的同义词)表示驱动比较的是字节顺序,而language_REGION格式的语言环境则意味着文化规则将推动比较。
带有法文名称的示例在UTF内部执行-8数据库:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "fr_FR";

结果:
 firstname 
-----------
 béatrice
 bérénice
 bernard
 boris

béatriceboris之前,因为带重音的E与O比较就好像没有重音。这是一条文化规则。
这与C语言环境所发生的情况不同:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris')) 
 AS l(firstname)
order by firstname collate "C";

结果:
 firstname 
-----------
 bernard
 boris
 béatrice
 bérénice


现在将带有重音E的名称推到最后
uTF-8中é的字节表示形式是十六​​进制C3 A9,而对于o则是6fc3大于6f,因此在C语言环境下为'béatrice' > 'boris'
不只是口音。带有连字符,标点和诸如œ之类的怪异字符的规则更为复杂。在每个语言环境中都应该使用怪异的文化规则。
现在,如果要比较的字符串碰巧混合了不同的语言,例如为来自世界各地的人们提供firstname列时,则可能是任何特定的语言环境都不应无论如何,之所以要占主导地位,是因为没有将针对不同语言的不同字母设计为不能相互排序。
在这种情况下,C是一个合理的选择,它的优点是速度更快,因为没有人能胜过纯字节比较。
LC_CTYPE
LC_CTYPE设置为'C'意味着像isupper(c)tolower(c)这样的C函数仅对US-ASCII范围内的字符给出预期的结果
(即,直到代码点0x7F Unicode)。
由于在这些libc函数之上在Postgres中实现了诸如upper()lower()initcap
之类的SQL函数,一旦存在非US-ASCII,它们就会受到影响。字符串中的字符。
示例:
test=> show lc_ctype;
  lc_ctype   
-------------
 fr_FR.UTF-8
(1 row)

-- Good result
test=> select initcap('élysée');
 initcap 
---------
 Élysée
(1 row)

-- Wrong result
-- collate "C" is the same as if the db has been created with lc_ctype='C'
test=> select initcap('élysée' collate "C");
 initcap 
---------
 éLyséE
(1 row)

C语言环境é被视为无法分类的字符。
使用正则表达式也会得到类似的错误结果:
test=> select 'élysée' ~ '^\w+$';
 ?column? 
----------
 t
(1 row)

test=> select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column? 
----------
 f
(1 row)


评论


因此,如果我做对了,即使您制造的是UTF-8服务器,也会出现订单问题?我猜想在UTF-8上设置系统LC_CTYPE或在UTF-8中编译PostgreSQL会导致与您指出的相同的比较问题。

– Gregoire D.
2015年3月11日14:30在

为了对此进行扩展,是否可以对查询强制进行整理,以便比较在本地正确?

– Gregoire D.
2015年3月11日14:31

是的,单个字符串比较可以嵌入他们自己的整理规则,就像我在此答案中使用排序规则后的“ C”整理一样。由您确定应用程序是否需要它,以及在何处需要它。那里的大多数应用程序都不在乎。

–丹尼尔·韦里特(DanielVérité)
2015年3月11日14:42



还要注意,各个列的COLLATE指定符可能与数据库的不同。

–丹尼尔·韦里特(DanielVérité)
2015年3月11日14:44

这个答案确实适用于LC_COLLATE,而不是LC_CTYPE。 LC_CTYPE用于确定字符是否为数字,字母,空格,标点符号等。

– jjanes
16 Mar 9 '16 at 14:50

#2 楼

关于Daniel接受的有关使用归类进行排序的答案,请注意,如果您在Mac上运行PostgreSQL,则由于操作系统级别上某些归类的设置不足,您首选的归类可能无法正常运行。您可以在此处阅读有关此问题的更多信息:

http://www.postgresql.org/message-id/4B4E845F.80906@postnewspapers.com.au

这是并不是特定于PostgreSQL的问题,而是Mac的排序规则设置的默认配置问题。我当前的系统在OS X El Capitan版本10.11上运行PostgreSQL 9.3,并且遇到此问题。无论我使用“ fr_FR”还是“ en_US”排序规则,我的系统都会返回相同的查询结果。例如:

使用“ fr_FR”归类:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
AS l(firstname)
order by firstname collate "fr_FR";

results:
==============
bernard
boris
béatrice
bérénice


使用“ en_US”归类:

select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
AS l(firstname)
order by firstname collate "en_US";

results:
==============
bernard
boris
béatrice
bérénice


在我的系统上,“ fr_FR”和“ en_US”的排序规则设置(在操作系统级别上)与在外壳程序中通过运行diff所演示的相同:

cd /usr/share/locale
diff fr_FR.UTF-8/LC_COLLATE en_US.UTF-8/LC_COLLATE


希望对阅读此文章并在Mac上使用PostgreSQL且遇到此问题的任何人有所帮助。

评论


如何使它在现代Mac中工作。您是否经历了使它在Mac中运行的任何事情?

–Dinesh Kumar
18年9月10日下午1:00