PostgreSQL本地化设置对SQL特性的影响有哪些

108次阅读

共计 4030 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要介绍“PostgreSQL 本地化设置对 SQL 特性的影响有哪些”，在日常操作中，相信很多人在 PostgreSQL 本地化设置对 SQL 特性的影响有哪些问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL 本地化设置对 SQL 特性的影响有哪些”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

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( a  collate  zh_CN 
 initcap 
---------
 A
(1 row)
postgres=# select initcap( a  collate  C 
 initcap 
---------
 a
(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 可以用上索引

到此，关于“PostgreSQL 本地化设置对 SQL 特性的影响有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完