PostgreSQL在使用initdb初始化数据库时,提供了"本地化"的参数locale,如不指定该参数则默认为空,即使用OS的locale设定. 本地化设置对以下SQL特性有影响: 1.排序和比较操作 : Sort order in queries using ORDER BY or the standard comparison operators on textual data 2.内置函数 : The upper, lower, and initcap functions 3.模式匹配 : Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions 4.to_char相关函数 : The to_char family of functions 5.LIKE能否使用索引 : The ability to use indexes with LIKE clauses **排序** 同样的数据,使用不同的LC_COLLATE,SQL输出不同: ``` postgres=# SELECT name FROM unnest(ARRAY['MYNAME', ' my_name', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "C"; name -------------------- my_name MYNAME my-image.jpg my-third-image.jpg (4 rows) postgres=# SELECT name FROM unnest(ARRAY['MYNAME', ' my_name', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN"; name -------------------- my-image.jpg my_name MYNAME my-third-image.jpg (4 rows) ``` collate指定为"C",则使用默认的字符串的二进制ASCII码值进行对比,而指定是zh_CN则不是. 使用zh_CN其行为按不区分大小写进行处理 ``` postgres=# SELECT name FROM unnest(ARRAY['MYNAME1', ' my_name2', 'my-image.jpg', 'my-third-image.jpg']) name ORDER BY name collate "zh_CN"; name -------------------- my-image.jpg MYNAME1 my_name2 my-third-image.jpg (4 rows) postgres=# SELECT name FROM unnest(ARRAY['myname1', ' myname2', 'myimage.jpg', 'mythirdimage.jpg']) name ORDER BY name collate "zh_CN"; name ------------------ myimage.jpg myname1 myname2 mythirdimage.jpg (4 rows) ``` 邮件列表中的解释如下: >The behavior of each collation comes from the operating system's own libc, except for the C collation, which is based on the ordering implied by strcmp() comparisons. Generally, most implementations have the behavior you describe, in that they assign least weight of all to caseness and whitespace, and somewhat more weight to punctuation. I don't think that there is much that can be done about it in practice, though in principal there could be a collation that has all the properties you want. **内置函数** 如initcap,在法语和C下面会有不同 ``` postgres=# select initcap('élysée' collate "C"); initcap --------- éLyséE (1 row) postgres=# select initcap('élysée' collate "fr_FR"); initcap --------- Élysée (1 row) ``` 在中文语境下,全角字符的小写字母会转换为全角的大写字母 ``` postgres=# select initcap('ａ' collate "zh_CN"); initcap --------- Ａ (1 row) postgres=# select initcap('ａ' collate "C"); initcap --------- ａ (1 row) ``` 在LC_COLLATE下,只会对7F以下的ASCII字符生效,其他字符不生效 **模式匹配** ``` postgres=# select 'élysée' ~ '^\w+$' collate "fr_FR"; ?column? ---------- t (1 row) postgres=# select 'élysée' COLLATE "C" ~ '^\w+$'; ?column? ---------- f (1 row) ``` **LIKE能否使用索引** ``` postgres=# CREATE TABLE t_sort ( postgres(# a text COLLATE "zh_CN", postgres(# b text COLLATE "C"); CREATE TABLE postgres=# postgres=# INSERT INTO t_sort SELECT md5(n::text), md5(n::text) postgres-# FROM generate_series(1, 1000000) n; INSERT 0 1000000 postgres=# CREATE INDEX ON t_sort USING btree (a); CREATE INDEX postgres=# CREATE INDEX ON t_sort USING btree (b); CREATE INDEX postgres=# ANALYZE t_sort; ANALYZE postgres=# SELECT * FROM t_sort LIMIT 2; a | b ----------------------------------+---------------------------------- c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c (2 rows) postgres=# explain SELECT * FROM t_sort WHERE a LIKE 'c4ca4238a0%'; QUERY PLAN --------------------------------------------------------------------------- Gather (cost=1000.00..18564.33 rows=100 width=66) Workers Planned: 2 -> Parallel Seq Scan on t_sort (cost=0.00..17554.33 rows=42 width=66) Filter: (a ~~ 'c4ca4238a0%'::text) (4 rows) postgres=# explain SELECT * FROM t_sort WHERE b LIKE 'c4ca4238a0%'; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using t_sort_b_idx on t_sort (cost=0.42..8.45 rows=100 width=66) Index Cond: ((b >= 'c4ca4238a0'::text) AND (b < 'c4ca4238a1'::text)) Filter: (b ~~ 'c4ca4238a0%'::text) (3 rows) ``` 使用zh_CN不能用上索引,但使用C可以用上索引 **参考资料** [Locale Support](https://www.postgresql.org/docs/current/locale.html) [One more time about collation in PostgreSQL](https://simply.name/pg-lc-collate.html) [What is the impact of LC_CTYPE on a PostgreSQL database?](https://dba.stackexchange.com/questions/94887/what-is-the-impact-of-lc-ctype-on-a-postgresql-database) [Re: Problem with PostgreSQL string sorting](https://www.postgresql.org/message-id/CAH2-Wzk5NzT-Tsn%2BO%2BXLprGvPYD%2BUk61kWeU1yVJZb97pJNQTg%40mail.gmail.com)