大连网站建设方法,烟台哪儿有可以做淘宝网站的,网页设计与制作教程免费,wordpress无法访问图片本地化的概念
本地化的目的是支持不同国家、地区的语言特性、规则。比如拥有本地化支持后#xff0c;可以使用支持汉语、法语、日语等等的字符集。除了字符集以外#xff0c;还有字符排序规则和其他语言相关规则的支持#xff0c;例如我们知道(‘a’,‘b’)该如何排序…本地化的概念
本地化的目的是支持不同国家、地区的语言特性、规则。比如拥有本地化支持后可以使用支持汉语、法语、日语等等的字符集。除了字符集以外还有字符排序规则和其他语言相关规则的支持例如我们知道(‘a’,‘b’)该如何排序那么(‘a’,‘A’)和(‘啊’,‘阿’)又该如何排序 如果通过google去搜本地化、字符集、collation相关信息可能会得到一些复杂又遥远的知识。最好的老师还是
本地化知识点总共分为3个部分locale本地化支持、collation校验、字符集。
locale
pg的本地化由由操作系统提供需要检查操作系统是否支持locale -a。在初始化数据库时可指定locale
initdb --localeen_US也可以单独设置本地化子类字符串排序、字符归类方法、数值格式、日期格式、时间格式、货币格式等
initdb --localezh_CN --lc-monetaryen_US所有本地化子类
本地化子类规则LC_COLLATEString sort orderLC_CTYPECharacter classification (What is a letter? Its upper-case equivalent?)LC_MESSAGESLanguage of messagesLC_MONETARYFormatting of currency amountsLC_NUMERICFormatting of numbersLC_TIMEFormatting of dates and times
这些子类又可以分为两部分其中lc_messageslc_monetarylc_numericlc_time在初始化后可以通过参数进行调整。LC_COLLATELC_CTYPE属于collation详见collation的调整。 locale设置会影响如下行为
Sort order in queries using ORDER BY or the standard comparison operators on textual dataThe upper, lower, and initcap functionsPattern 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 expressionsThe to_char family of functionsThe ability to use indexes with LIKE clauses
COLLATION
collation是字符排序的顺序和字符分类行为。一些数据库操作符依赖collation比如order by、lower, upper、initcap 、to_char等等。 使用如下SQL查询系统表pg_collation来获取字符集支持的LC_COLLATE和LC_CTYPE信息 select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation where collname in (default,C,POSIX,en_US.utf8,zh_CN.utf8,zh_CN.gb2312,zh_SG.gb2312) ;encoding | collname | collcollate | collctype
----------------------------------------------------| default | | | C | C | C| POSIX | POSIX | POSIXUTF8 | en_US.utf8 | en_US.utf8 | en_US.utf8EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312encoding是字符集collname为collation的名字
encoding 为空时表示这个 collation 支持所有的字符集default, C, POSIX是所有平台都支持的collation由libc提供其他collation取决于操作系统是否支持(locale -a)default表示使用建库时的collation可通过\l查看C语义上等价于POSIX但是PG仍然认为他们是不同的collation。他们的字符都以ASCII码对比严格按照字节序比对大小。 SELECT a COLLATE C b COLLATE POSIX ;
ERROR: 42P21: collation mismatch between explicit collations C and POSIX
LINE 1: SELECT a COLLATE C b COLLATE POSIX ;
LOCATION: merge_collation_state, parse_collate.c:834UTF8是最常见的字符集我们最常见的语言环境是en_US和zh_CN可以通过CREATE COLLATION ...创建自定义的collation。不过LC_COLLATE和LC_CTYPE不同的情况非常少见
LC_COLLATE
LC_COLLATE影响字符比对排序、字符操作等等 collate子句可以转化表达式的collation
expr COLLATE collation注意这里指定的是collation不是lc_collate。如果没有显示指定collation数据库默认使用字段的collation如果字段没有指定collation使用database的默认collation。
不同的collation排序测试 select col1 from (values (a), (A), (啊), (阿))
- AS l(col1)
- order by col1 collate C;col1
------Aa啊阿select col1 from (values (a), (A), (啊), (阿))
- AS l(col1)
- order by col1 collate en_US.utf8;col1
------aA啊阿select col1 from (values (a), (A), (啊), (阿))
- AS l(col1)
- order by col1 collate zh_CN.utf8;col1
------aA阿啊这3个不同的collation有不同的lc_collate排序方法应该是不一样的从结果来看确实是不一样的出现了3种排序结果。 collation C为什么Aa? collation C使用的ASCII的编码顺序ASCII码中大写在小写前面。而en_US.utf8和zh_CN.utf8的英文字母明显不是这个顺序 中文的顺序 同样是utf8字符集中文环境和英文环境的中文顺序不一样。不同的lc_collate对于不同本地化语言应该都可以对应到不同的alphabets。其中lc_collateC的排序一定是按字节序排的虽然ASCII没有中文但是C也可以排序中文基本每个中文都可以对应UTF8的一个编码而C以其字节序排序。
LC_CTYPE
LC_CTYPE影响字符操作如upper、initcap等 如果字符串都是英文比如是’abcD’initcap在3种collation下都会转换为’Abcd’这里不多展示了。 但是加入中文结果就不一样了
select initcap(啊aAAa阿bBBb collate C);initcap
--------------啊Aaaa阿Bbbbselect initcap(啊aAAa阿aAAa collate en_US.utf8);initcap
--------------啊aaaa阿aaaaselect initcap(啊aAAa阿aAAa collate zh_CN.utf8);initcap
--------------啊aaaa阿aaaaLC_CTYPEC时initcap把每个非连续英文字符串的首字母大写而en_US.utf8和zh_CN.utf8只会将首个字符大写中文就不会变其他英文字符小写。 initcap中文也许处于需求不明的状况但是我们可以得出结论不同的LC_CTYPE会导致initcap等字符敏感函数结果不一样。 另外中文对于大小写不敏感一些其他本地化语言同样有大小写不同的LC_CTYPE导致的结果会更复杂。
字符集
字符集基础
PostgreSQL支持不同的字符集character sets也叫encodings。字符集于collation是两个概念但是字符集必须跟LC_CTYPELC_COLLATE兼容。就像在pg_collation中看到的那样C/POSIX支持所有字符集而其他collation只支持一种字符集linux系统中。
PostgreSQL中文相关可用的字符集 (*collation C由libc库提供部分collation可以由ICU库提供需提前编译)
NameDescriptionLanguageServer端是否支持?ICU是否支持?Bytes/CharAliasesBIG5Big Five繁体中文NoNo1–2WIN950, Windows950EUC_CNExtended UNIX Code-CN简体中文YesYes1–3GB2312GB18030National Standard中文NoNo1–4GBKExtended National Standard简体中文NoNo1–2WIN936, Windows936UTF8Unicode, 8-bitallYesYes1–4Unicode
繁体中文 BIG5是最常见的繁体中文字符集标准。之前是业界标准后来被录入为国家标准。 简体中文 GB是国标的意思GB2312、GB18030、GBK都是我国的国家字符集标准。由于生僻字等问题并经过多年发展产生了一些历史版本所以标准看上去有多个。 其中EUC_CN全称为 Extended UNIX Code-CN 其实就是GB2312但它也不能处理所有罕见字。类似命名的还有EUC_KR,EUC_JP,EUC_TW等等。 国际标准 上面的字符集都是国家标准他们除了支持英、中外不支持其他语言。而国际标准支持世界上所有语言这就是unicode国际编码标准(甚至emoji也包含其中 )。还有个著名的国际标准组织ISO也在维护字符集他俩有交集这里先忽略ISO。 由于Unicode编码方案的不同又有UTF-8、UTF-16、UTF-32三种编码方式。
UTF-8编码格式:
字节格式实际编码位码点范围1字节0xxxxxxx70 ~ 1272字节110xxxxx 10xxxxxx11128 ~ 20473字节1110xxxx 10xxxxxx 10xxxxxx162048 ~ 655354字节11110xxx 10xxxxxx 10xxxxxx 10xxxxxx2165536 ~ 2097151
UTF8编码是变长的。 0x00-0x7F之间的字符(1字节UTF-8编码与ASCIIAmerican Standard Code for Information Interchange 美国标准信息交换代码编码完全相同所以UTF-8是完全兼容ASCII的。 由于同源、同义、相似性在unicode中中日韩越使用了同一编码称为中日韩统一表意文字或称中日韩越统一表意文字。 中日韩统一表意文字编码范围为3400-4DBF/4E00-9FFF/20000-3FFFF
字符集转换
server_encoding与client_encoding不一致可发生自动转换Server查出来的字符集。设置服务端字和客户端字符集参考设置字符集一节。 中文相关字符集Server/Client可转化表
Server Character SetAvailable Client Character SetsBIG5not supported as a server encodingEUC_CNGB2312EUC_CNGB2312, MULE_INTERNAL, UTF8GB18030not supported as a server encodingGBKnot supported as a server encodingUTF8all supported encodingsGB18030GBK服务端都不支持所以其实只有EUC_CNGB2312、UTF8能在Server/Client转换。
以上是可以转换的字符集仍需要CONVERSATION的支持。PG内置了一些转换函数可通过pg_conversion查看
Conversion NameSource EncodingDestination Encodingbig5_to_utf8BIG5UTF8euc_cn_to_utf8EUC_CNUTF8gb18030_to_utf8GB18030UTF8gbk_to_utf8GBKUTF8utf8_to_big5UTF8BIG5utf8_to_euc_cnUTF8EUC_CNutf8_to_gb18030UTF8GB18030utf8_to_gbkUTF8GBK
可通过create conversation语句创建自定义的转换需指定转换的function。 有些字符集间看上去可以转换但是server端根本不支持存储这些字符集如big5、gb18030、gbk所以也没啥用。我们这里仅需要知道euc_cn和utf8能相互转换就可以了。 没有CONVERSATION是不能发生转换的
--EUC_CN的database\encoding EUC_KR
EUC_KR: invalid encoding name or conversion procedure not found字符集转换测试 需要注意客户端的字符集设置如CRT的 “session”-“Appearance”-“Character encoding” 至少有3个端有字符集的概念数据库server、数据库client、UI客户端。CONVERSATION也只能控制数据库server - 数据库client 1.server为UTF8的转换测试
create table zh(col1 varchar(20));
insert into zh values(),(阿),(〇); --〇 ling是一个中文
--CRT不设置为UTF8中文全是乱码只有CRT设置UTF8来插入 show server_encoding;server_encoding
-----------------UTF8show client_encoding;client_encoding
-----------------UTF8
--完全没有转换的情况下UTF8正常展示。此时3端字符集为UTF8 - UTF8 - UTF8select * from zh;col1
------阿〇--切换数据库client字符集此时3端字符集为UTF8 - EUC_CN - UTF8\encoding EUC_CN; --设置客户端字符集 select * from zh where col1 in (阿);
ERROR: 22021: invalid byte sequence for encoding EUC_CN: 0xe9 0x98
LOCATION: report_invalid_encoding, mbutils.c:1597
Time: 0.112 msselect * from zh where col1 in (〇);
ERROR: 22021: invalid byte sequence for encoding EUC_CN: 0xe3 0x80
ERROR: 22021: invalid byte sequence for encoding EUC_CN: 0xe3 0x80
--“阿”和“〇”看上去不能转换为EUC_CN但不是这样的 select * from zh limit 2;col1
------B0A2
(2 rows)
--第二行即是阿数据库server/client看上去转换了字符集从UTF8转换为了EUC_CN
--但是可能是因为UI客户端问题没有正确显示此时UI客户端CRT为UTF8--然而把CRT改成GB2312还是不会正确展示
select * from zh limit 2;col1
------B0A2
(2 rows)--当查询〇时数据库直接抛出报错说明〇不能从UTF8转换为EUC_CN
select * from zh ;
ERROR: 22P05: character with byte sequence 0xe3 0x80 0x87 in encoding UTF8 has no equivalent in encoding EUC_CN
LOCATION: report_untranslatable_char, mbutils.c:16312.server为EUC_CN的转换测试 show server_encoding; --database为EUC_CN字符集
server_encoding
-----------------
EUC_CN--在EUC_CN库下同样创建一个zh表此时尝试插入就有问题了 insert into zh values(〇);
ERROR: 22P05: character with byte sequence 0xe3 0x80 0x87 in encoding UTF8 has no equivalent in encoding EUC_CN
LOCATION: report_untranslatable_char, mbutils.c:1631同样报错〇不能从UTF8转换为EUC_CN。EUC_CNGB2312中文编码不完全与UTF8相同EUC_CNGB2312不是所有中文都包含的特别是罕见字。
设置locale、collation和字符集
上面已经了解过本地化和字符集设置了这里做一个汇总
database cluster的locale、collation、字符集
初始化时可设置database cluster的locale和字符集参考
initdb -D $DATADIR -E UTF8 --localeen_US.UTF8
initdb -D $DATADIR -E UTF8 --localeen_US.UTF8 --lc_collateC --lc_ctypeC
initdb -D $DATADIR -E UTF8 --localeen_US.UTF8 --lc_collateC --lc_ctypeC --lc-messagesen_US.UTF8 --lc-monetaryen_US.UTF8 --lc-numericen_US.UTF8 --lc-timeen_US.UTF8initdb会创建postgrestemplate1和template0三个库。create database语句时默认使用template1创建库。 encoding设置字符集locale设置LC_COLLATELC_CTYPELC_MESSAGESLC_MONETARYLC_NUMERICLC_TIME除非特别指定如–lc_collate LC_COLLATELC_CTYPE称为collation还可在database、列、索引上设置。LC_MESSAGESLC_MONETARYLC_NUMERICLC_TIME为实例参数可随时修改。 encoding只能在初始化、创建database时设置一旦设置不可修改。
database的collation、字符集
创建database时可设置database的字符集、lc_collate、lc_ctype。
create database ,createdb都可以在创建database时指定字符集一旦创建就不能修改database的字符集。两个命令都是使用template库来创建database
template又有template0和template1官方文档有这样一句话 Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to. template1是可写数据的模板库可能包含本地化过的数据而template0不能写数据所以要创建不同的本地化库应使用template0。
而且要显示使用template0因为不指定的话默认是template1。所以在创建database时没有指定template1且指定其他字符集会报错 create database db_GB2312 ENCODING EUC_CN LC_COLLATE zh_CN.gb2312 LC_CTYPE zh_CN.gb2312;
ERROR: 22023: new encoding (EUC_CN) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.另外不能在创建database时通过指定locale来设置字符集 create database db_GB2312 locale zh_CN.gb2312 template template0;
ERROR: 22023: encoding UTF8 does not match locale zh_CN.gb2312
DETAIL: The chosen LC_CTYPE setting requires encoding EUC_CN.
LOCATION: check_encoding_locale_matches, dbcommands.c:773报错表示需要指定LC_CYTPE子选项把collation相关子选项全部加上仍然报错 create database db_GB2312 LOCALE EUC_CN LC_COLLATE zh_CN.gb2312 LC_CTYPE zh_CN.gb2312;
ERROR: 42601: conflicting or redundant options
DETAIL: LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.LOCALE又不能跟LC_CTYPE等子选项一起使用 然后把locale去掉通过设置字符集、LC_COLLATE、LC_CTYPE 来设置可以成功。
创建指定字符集的database的正确姿势
create database
create database db_GB2312 ENCODING EUC_CN LC_COLLATE zh_CN.gb2312 LC_CTYPE zh_CN.gb2312 template template0;createdb 通过cli命令createdb来创建createdb封装了create database他俩是等价的 createdb -E EUC_CN -T template0 --lc-collatezh_CN.gb2312 --lc-ctypezh_CN.gb2312 db_GB2312查看database字符集 \l pg_database
select datname,pg_encoding_to_char(encoding),datcollate,datctype,datlocprovider,daticulocale from pg_database;show参数
SERVER_ENCODING,LC_COLLATE,LC_CTYPE三个参数都是不可更改的分别展示当前database的server端字符集、LC_COLLATE、LC_CTYPE
列的collation
collation只跟字符排序、字符函数相关跟编码不相关。在没有索引的情况下修改列的collation相当于只是在调整这个列的default排序输出有索引的情况下会重建索引。不指定列的collation默认与database一致。
建表时指定collation注意有些字段类型是un-collatable的比如int
create table t1(col1 varchar(10) collate en_US.utf8);
alter table t1 alter column col1 type varchar(10) collate C;注意alter table不修改长度是不会重建表的但是一定会重建索引
查看列的默认collation
1. \d t12. information_schema.columns
select table_catalog,table_schema,table_name,column_name,collation_name from information_schema.columns where table_namet1;3. pg_attribute
select a.attrelid::regclass,a.attname,a.attcollation,c.collname,c.collcollate,c.collctype from pg_attribute a left join pg_collation c on a.attcollationc.oid where a.attrelid::regclasstlzl::regclass and a.attcollation0;推荐方式3查看。\d ,information_schema.columns虽然能看到collname但是collname不是唯一的。只有方式3可以看到collate,ctype
指定collate和查看pg_attribute的测试
create table tlzl(col1 varchar(10) ,col2 varchar(10) collate C,col3 varchar(10) collate zh_CN,col4 varchar(10) collate en_US.utf8
);--列的collation相当于给列打上默认排序的标记也看不到具体是哪个collate和ctype
db_utf8_c Table public.tlzlColumn | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------------------------------------------------------------------------------------------------col1 | character varying(10) | | | | extended | | | col2 | character varying(10) | C | | | extended | | | col3 | character varying(10) | zh_CN | | | extended | | | col4 | character varying(10) | en_US.utf8 | | | extended | | |
--collname和collatectype不是一一对应的上面的col3 zh_CN看不出来collate是哪个
db_utf8_c select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation where collname like zh_CN%;encoding | collname | collcollate | collctype
----------------------------------------------------EUC_CN | zh_CN | zh_CN | zh_CNEUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8UTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8--pg_attribute展示比\d准确
db_utf8_c select a.attrelid::regclass,a.attname,a.attcollation,c.collname,c.collcollate,c.collctype from pg_attribute a left join pg_collation c on a.attcollationc.oid where a.attrelid::regclasstlzl::regclass and a.attcollation0;attrelid | attname | attcollation | collname | collcollate | collctype
----------------------------------------------------------------------tlzl | col1 | 100 | default | | tlzl | col2 | 950 | C | C | Ctlzl | col4 | 12562 | en_US.utf8 | en_US.utf8 | en_US.utf8tlzl | col3 | 13200 | zh_CN | zh_CN.utf8 | zh_CN.utf8
--此时才知道col3 zh_CN的collate是zh_CN.utf8 修改列的collate重写测试
--给字段加索引看看重写情况
db_utf8_c create index idxcol4 on tlzl(col4);
CREATE INDEXdb_utf8_c select pg_relation_filepath(tlzl) TableRelid, pg_relation_filepath(idxcol4) IndexRelid; tablerelid | indexrelid
------------------------------------base/40996/41006 | base/40996/41015db_utf8_c alter table tlzl alter column col4 type varchar(10) collate C;
ALTER TABLE
db_utf8_c select pg_relation_filepath(tlzl) TableRelid, pg_relation_filepath(idxcol4) IndexRelid; tablerelid | indexrelid
------------------------------------base/40996/41006 | base/40996/41016
--表没有重写索引重写了列的collation只是标记修改列的collation不会重写表但是如果其上有索引那么会重写这个索引有时候不会见下面一节。
索引的collation
在创建索引时如不显示指定索引的collation那么索引会使用列上声明的collation。
创建索引时显示使用collation
create index idx_C on tlzl(col3 collate C); 另外索引还可以以text_pattern_opsvarchar_pattern_opsbpchar_pattern_ops来创建此时的索引不依赖collation的规则而是一个字符一个字符的对比 The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. CREATE INDEX test_index ON test_table (col varchar_pattern_ops);其实这种索引跟collation不是完全无关索引一定有一个排序规则这种索引的排序规则看上去跟C一致。参考like不走索引一节
查看索引的collation:
\d --\d会展示指定过collate的索引如果没有的话使用的是列默认索引
db_utf8_c \d tlzlTable public.tlzlColumn | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------------------------------------------------------------------------------------------------col1 | character varying(10) | | | | extended | | | col2 | character varying(10) | C | | | extended | | | col3 | character varying(10) | zh_CN | | | extended | | | col4 | character varying(10) | en_US.utf8 | | | extended | | |
Indexes:idx_c btree (col3 COLLATE C)idxcol4 btree (col4)
Access method: heap
通过pg_index查看更为清晰:pg_index的indcollation类型是oidvector的不能直接转化为oid查起来麻烦点
db_utf8_c select indcollation,indexrelid::regclass from pg_index where indexrelid::regclass idx_C::regclass;indcollation | indexrelid
--------------------------950 | idx_cdb_utf8_c select oid,pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation where oid950;oid | encoding | collname | collcollate | collctype
-------------------------------------------------950 | | C | C | C另外不能通过alter index改变索引的collation只能删除重建
测试指定过索引collate后修改列的collate是否会重写索引
db_utf8_c select pg_relation_filepath(tlzl) TableRelid, pg_relation_filepath(idxcol4) IndexRelid4,pg_relation_filepath(idx_c) IndexRelidC; tablerelid | indexrelid4 | indexrelidc
------------------------------------------------------base/40996/41020 | base/40996/41023 | base/40996/41024
(1 row)db_utf8_c alter table tlzl alter column col3 type varchar(10) collate en_US.utf8;
ALTER TABLE
db_utf8_c select pg_relation_filepath(tlzl) TableRelid, pg_relation_filepath(idxcol4) IndexRelid4,pg_relation_filepath(idx_c) IndexRelidC; tablerelid | indexrelid4 | indexrelidc
------------------------------------------------------base/40996/41020 | base/40996/41023 | base/40996/41024 --idx_c的relfileid没有变如果指定过索引的collate修改其字段默认collate不会重新索引。
客户端的字符集
客户端设置与database不同的字符集会发生字符集转换也可能转换不成功具体参考字符集转换一节。
服务端的字符集在创建database后无法改变client的字符集可随时调整。
Client字符集设置方法很多
直接在客户端设置
\encoding UTF8 --仅psql支持
SET CLIENT_ENCODING TO UTF8; --session级修改参数
SET NAMES UTF8; --sql标准设置环境变量PGCLIENTENCODING设置client_encoding服务端配置参数
优先级客户端设置环境变量PGCLIENTENCODINGclient_encoding服务端配置参数
查看client字符集
\encoding --仅psql支持
SHOW client_encoding;表达式collate
表达式加collate会覆盖表达式原本的collation相当于指定了排序collation。
需在表达式的最后加collate关键字
expr COLLATE collation--例如
select * from tab1 order by name COLLATE C;排序和collate索引选择详见排序结果问题一节。
MORE
概念整理
PostgreSQL本地化有三个重要概念字符集、locale、collation需要弄清他们的关系。
字符集在服务端的设置非常重要只能在初始化和建db时指定建库后不可修改。字符集选择直接影响编码方式collation并不是但是他俩之间有依赖关系。locale同样可以在初始化时指定其中collation可在建库时指定也可以单独指定列的collation注意他们只是默认值。只有在建索引时指定collation会影响其真正的存储顺序。不同的collation是无法使用索引的即使他们同源。
client字符集和LC_MASSAGES等4个参数都比较简单可直接修改参数与数据存储无关。 排序结果问题
因为utf8是最常见的字符集我们测试utf相关的collation排序
create database db_UTF8 ENCODING UTF8 template template0; --建一个UTF8的库collation无所谓
use db_UTF8;
create table tzlz(name varchar(10));
insert into tzlz values(a),(aa),(A),(AA),(啊),(阿),(〇);不同collation的order by结果
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name;
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name collate C;
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name collate en_US;
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name collate en_US.utf8;
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name collate zh_CN;
select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name collate zh_CN.utf8;顺序defaultCen_USen_US.utf8zh_CNzh_CN.utf81〇A〇〇aa2aAAaaAA3AaAAaaaa4aaaaaaaaAAAA5AA〇AAAA阿阿6啊啊啊啊啊啊7阿阿阿阿〇〇
这里的default是en_US.utf8字段collation(default)-database collation(en_US.utf8) C、en_US.uft8、zh_CN.uft8排序结果都不同
collate和索引扫描测试
insert into tzlz values(generate_series(1,10000));
create index idxzlz_default on tzlz(name);
create index idxzlz_C on tzlz(name collate C);
create index idxzlz_enUS_utf8 on tzlz(name collate en_US.utf8);使用collate在索引上的优化
--不加任何collate关键字简单的索引扫描不会额外排序
db_utf8_c explain select name from tzlz where name in (a,aa,A,AA,啊,阿,〇) order by name;QUERY PLAN
---------------------------------------------------------------------------------Index Only Scan using idxzlz_default on tzlz (cost0.29..30.13 rows8 width4)Index Cond: (name ANY ({a,aa,A,AA,啊,阿,〇}::text[]))--谓词加collate转换可以走到正确的索引
db_utf8 explain select name from tzlz where name collate C in (a,aa,A,AA,啊,阿,〇);QUERY PLAN
---------------------------------------------------------------------------Index Only Scan using idxzlz_c on tzlz (cost0.29..30.12 rows7 width4)Index Cond: (name ANY ({a,aa,A,AA,啊,阿,〇}::text[]))db_utf8 explain select name from tzlz where name collate en_US.utf8 in (a,aa,A,AA,啊,阿,〇);QUERY PLAN
-----------------------------------------------------------------------------------Index Only Scan using idxzlz_enus_utf8 on tzlz (cost0.29..30.12 rows7 width4)Index Cond: (name ANY ({a,aa,A,AA,啊,阿,〇}::text[]))--但是collation的名字必须一致
db_utf8 explain select name from tzlz where name collate en_US in (a,aa,A,AA,啊,阿,〇);QUERY PLAN
-----------------------------------------------------------------Seq Scan on tzlz (cost0.00..232.63 rows7 width4)Filter: ((name)::text ANY ({a,aa,A,AA,啊,阿,〇}::text[]))--同时order by也需要加collate转换表达式
--此时使用了正确的索引但是order by的时候判断为不同的collation哪怕他们是一样的
db_utf8 explain select name from tzlz where name collate en_US.utf8 in (a,aa,A,AA,啊,阿,〇) order by name;QUERY PLAN
-----------------------------------------------------------------------------------------Sort (cost30.22..30.23 rows7 width4)Sort Key: name- Index Only Scan using idxzlz_enus_utf8 on tzlz (cost0.29..30.12 rows7 width4)Index Cond: (name ANY ({a,aa,A,AA,啊,阿,〇}::text[]))--where和order by都加上collate转换可以小选择正确的索引且不会在发生排序
db_utf8 explain select name from tzlz where name collate en_US.utf8 in (a,aa,A,AA,啊,阿,〇) order by name collate en_US.utf8;QUERY PLAN
------------------------------------------------------------------------------------Index Only Scan using idxzlz_enus_utf8 on tzlz (cost0.29..30.12 rows7 width42)Index Cond: (name ANY ({a,aa,A,AA,啊,阿,〇}::text[]))索引在指定collation后sql需要显示使用collate关键字转换表达式即便default与当前collation一致pg也不会使用到索引。
like不走索引 The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE PostgreSQL原话使用非C or POSIX会阻止使用普通索引
db_utf8 explain select name from tzlz where name like a%;QUERY PLAN
--------------------------------------------------------------------------Index Only Scan using idxzlz_c on tzlz (cost0.29..4.31 rows1 width4)Index Cond: ((name a::text) AND (name b::text))Filter: ((name)::text ~~ a%::text)
(3 rows)db_utf8 explain select name from tzlz where name collate en_US.utf8 like a%;QUERY PLAN
--------------------------------------------------------------------------Index Only Scan using idxzlz_c on tzlz (cost0.29..4.31 rows1 width4)Index Cond: ((name a::text) AND (name b::text))Filter: ((name)::text ~~ a%::text)PostgreSQL在索引扫描时把like转化为了和还加了一个比输入的值大一号的值这里就有问题了collation跟排序强相关ASCII码中a1是b但是汉字又如何
db_utf8 explain select name from tzlz where name collate en_US.utf8 like 阿%;QUERY PLAN
--------------------------------------------------------------------------Index Only Scan using idxzlz_c on tzlz (cost0.29..6.49 rows1 width4)Index Cond: ((name 阿::text) AND (name 陿::text))Filter: ((name)::text ~~ 阿%::text)果然出现了另一个汉字
如果是全表扫描不会出现 的情况
db_utf8 drop index idxzlz_c;
DROP INDEX
db_utf8 explain select name from tzlz where name collate en_US.utf8 like 阿%;QUERY PLAN
------------------------------------------------------Seq Scan on tzlz (cost0.00..170.09 rows1 width4)Filter: ((name)::text ~~ 阿%::text)可以创建一个与collation规则无关的索引pg官方声称无关
CREATE INDEX idx_pattern ON tzlz (name varchar_pattern_ops);来看看他的执行计划
db_utf8 explain select name from tzlz where name like 阿%;QUERY PLAN
-----------------------------------------------------------------------------Index Only Scan using idx_pattern on tzlz (cost0.29..6.49 rows1 width4)Index Cond: ((name ~~ 阿::text) AND (name ~~ 陿::text))Filter: ((name)::text ~~ 阿%::text)他还是把大1号的字符串自己生成了这跟collation一定是有关系的···看上去就是C
所以可以得出结论
pg在like使用普通索引时需要把其转换为 此时必须出现一个比当前字符串大1的值。而collation又与大小强相关此时只能使用同一collation索引才能保证数据正确。pg选择了非本地化的collation C。
临时解决这个问题最快的办法是新建一个collation C或pattern索引:
create index idxzlz_C on tzlz(name collate C);
CREATE INDEX idx_pattern ON tzlz (name varchar_pattern_ops);其他调整各级别的默认collation参考上面的章节。
开发习惯在建索引时不会指定collation如果不是C或pattern都走不了like在加上要选择国际字符集utf8这样在数据库运维时选择的本地化方式就非常少了。字符集为utf8collation为C
参考
https://dbafix.com/what-is-the-impact-of-lc_ctype-on-a-postgresql-database/#:~:textHaving%20LC_CTYPE%20set%20to%20%E2%80%98C%E2%80%99%20implies%20that%20C,Postgres%20on%20top%20of%20these%20libc%20functions%2C%20they%E2%80%99re https://www.postgresql.org/docs/current/charset.html https://www.bookstack.cn/read/rds-best-pratice/bfc0037fe00d87dc.md https://help.aliyun.com/zh/rds/apsaradb-rds-for-postgresql/configure-the-collation-of-a-database-on-an-apsaradb-rds-for-postgresql-instance https://baike.baidu.com/item/%E7%BB%9F%E4%B8%80%E7%A0%81/2985798?fromModulelemma_inlinkfromtitleUnicodefromid750500 https://baike.baidu.com/item/%E4%B8%AD%E6%97%A5%E9%9F%A9%E8%B6%8A%E7%BB%9F%E4%B8%80%E8%A1%A8%E6%84%8F%E6%96%87%E5%AD%97/1301611?fromModulelemma_inlink
https://blog.csdn.net/songyundong1993/article/details/128739919
