PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding

简介:
+关注继续查看

标签

PostgreSQL , conversion , pg_conversion , 拼音 , 编码转换 , convert , convert_to , convert_from


背景

国内的应用,在文本排序上基本都是按照拼音来进行排序的。

在不同的字符集中,汉字的编码可能不一样,比如UTF8和GBK,其中GBK是按拼音的顺序进行编码的,而UTF8则不是。

所以如果你的数据库使用了UTF8编码,对中文字段进行排序时,可能得到的并不是按拼音排序的结果。

在PostgreSQL中,中文按拼音排序的编码包括GB18030, EUC_CN, GBK, BIG5, EUC_TW 等。

为了得到拼音排序,可以使用编码转换后的值来排序,索引也可以使用编码转换的表达式索引。

编码

PostgreSQL支持的编码如下

https://www.postgresql.org/docs/9.6/static/multibyte.html

PostgreSQL Character Sets

Name Description Language Server? Bytes/Char Aliases
BIG5 Big Five Traditional Chinese No 1-2 WIN950, Windows950
EUC_CN Extended UNIX Code-CN Simplified Chinese Yes 1-3 -
EUC_JP Extended UNIX Code-JP Japanese Yes 1-3 -
EUC_JIS_2004 Extended UNIX Code-JP, JIS X 0213 Japanese Yes 1-3 -
EUC_KR Extended UNIX Code-KR Korean Yes 1-3 -
EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese Yes 1-3 -
GB18030 National Standard Chinese No 1-4 -
GBK Extended National Standard Simplified Chinese No 1-2 WIN936, Windows936
ISO_8859_5 ISO 8859-5, ECMA 113 Latin/Cyrillic Yes 1 -
ISO_8859_6 ISO 8859-6, ECMA 114 Latin/Arabic Yes 1 -
ISO_8859_7 ISO 8859-7, ECMA 118 Latin/Greek Yes 1 -
ISO_8859_8 ISO 8859-8, ECMA 121 Latin/Hebrew Yes 1 -
JOHAB JOHAB Korean (Hangul) No 1-3 -
KOI8R KOI8-R Cyrillic (Russian) Yes 1 KOI8
KOI8U KOI8-U Cyrillic (Ukrainian) Yes 1 -
LATIN1 ISO 8859-1, ECMA 94 Western European Yes 1 ISO88591
LATIN2 ISO 8859-2, ECMA 94 Central European Yes 1 ISO88592
LATIN3 ISO 8859-3, ECMA 94 South European Yes 1 ISO88593
LATIN4 ISO 8859-4, ECMA 94 North European Yes 1 ISO88594
LATIN5 ISO 8859-9, ECMA 128 Turkish Yes 1 ISO88599
LATIN6 ISO 8859-10, ECMA 144 Nordic Yes 1 ISO885910
LATIN7 ISO 8859-13 Baltic Yes 1 ISO885913
LATIN8 ISO 8859-14 Celtic Yes 1 ISO885914
LATIN9 ISO 8859-15 LATIN1 with Euro and accents Yes 1 ISO885915
LATIN10 ISO 8859-16, ASRO SR 14111 Romanian Yes 1 ISO885916
MULE_INTERNAL Mule internal code Multilingual Emacs Yes 1-4
SJIS Shift JIS Japanese No 1-2 Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004 Shift JIS, JIS X 0213 Japanese No 1-2 -
SQL_ASCII unspecified (see text) any Yes 1 -
UHC Unified Hangul Code Korean No 1-2 WIN949, Windows949
UTF8 Unicode, 8-bit all Yes 1-4 Unicode
WIN866 Windows CP866 Cyrillic Yes 1 ALT
WIN874 Windows CP874 Thai Yes 1 -
WIN1250 Windows CP1250 Central European Yes 1 -
WIN1251 Windows CP1251 Cyrillic Yes 1 WIN
WIN1252 Windows CP1252 Western European Yes 1 -
WIN1253 Windows CP1253 Greek Yes 1 -
WIN1254 Windows CP1254 Turkish Yes 1 -
WIN1255 Windows CP1255 Hebrew Yes 1 -
WIN1256 Windows CP1256 Arabic Yes 1 -
WIN1257 Windows CP1257 Baltic Yes 1 -
WIN1258 Windows CP1258 Vietnamese Yes 1 ABC, TCVN, TCVN5712, VSCII

与中文编码排序相关的包括 GB18030, EUC_CN, GBK, BIG5, EUC_TW

简体常用的包括GBK, EUC_CN。

编码转换

在PostgreSQL中,如果要将字符从一个编码转换为另一个编码,需要告诉数据库(create conversion)怎么转换(使用什么C函数),PG内置了一些转换的C函数和转换方法。

https://www.postgresql.org/docs/9.6/static/catalog-pg-conversion.html

pg_conversion

Name Type References Description
oid oid - Row identifier (hidden attribute; must be explicitly selected)
conname name - Conversion name (unique within a namespace)
connamespace oid pg_namespace.oid The OID of the namespace that contains this conversion
conowner oid pg_authid.oid Owner of the conversion
conforencoding int4 - Source encoding ID
contoencoding int4 - Destination encoding ID
conproc regproc pg_proc.oid Conversion procedure
condefault bool - True if this is the default conversion

查看内置的转换方法

可以看到utf8转换为中文编码的都支持了

postgres=> select * from pg_conversion where conname ~* 'gbk|gb18|euc_cn|euc_tw|big5' order by 1;
     conname     | connamespace | conowner | conforencoding | contoencoding |     conproc     | condefault 
-----------------+--------------+----------+----------------+---------------+-----------------+------------
 big5_to_euc_tw  |           11 |       10 |             36 |             4 | big5_to_euc_tw  | t
 big5_to_mic     |           11 |       10 |             36 |             7 | big5_to_mic     | t
 big5_to_utf8    |           11 |       10 |             36 |             6 | big5_to_utf8    | t
 euc_cn_to_mic   |           11 |       10 |              2 |             7 | euc_cn_to_mic   | t
 euc_cn_to_utf8  |           11 |       10 |              2 |             6 | euc_cn_to_utf8  | t
 euc_tw_to_big5  |           11 |       10 |              4 |            36 | euc_tw_to_big5  | t
 euc_tw_to_mic   |           11 |       10 |              4 |             7 | euc_tw_to_mic   | t
 euc_tw_to_utf8  |           11 |       10 |              4 |             6 | euc_tw_to_utf8  | t
 gb18030_to_utf8 |           11 |       10 |             39 |             6 | gb18030_to_utf8 | t
 gbk_to_utf8     |           11 |       10 |             37 |             6 | gbk_to_utf8     | t
 mic_to_big5     |           11 |       10 |              7 |            36 | mic_to_big5     | t
 mic_to_euc_cn   |           11 |       10 |              7 |             2 | mic_to_euc_cn   | t
 mic_to_euc_tw   |           11 |       10 |              7 |             4 | mic_to_euc_tw   | t
 utf8_to_big5    |           11 |       10 |              6 |            36 | utf8_to_big5    | t
 utf8_to_euc_cn  |           11 |       10 |              6 |             2 | utf8_to_euc_cn  | t
 utf8_to_euc_tw  |           11 |       10 |              6 |             4 | utf8_to_euc_tw  | t
 utf8_to_gb18030 |           11 |       10 |              6 |            39 | utf8_to_gb18030 | t
 utf8_to_gbk     |           11 |       10 |              6 |            37 | utf8_to_gbk     | t
(18 rows)

编码转换函数

注意数据库版本

PostgreSQL 8.x(如Greenplum), 将字符串从原编码转换为指定编码的字符串返回。

可能存在显示的问题。

                              List of functions
   Schema   |     Name      | Result data type | Argument data types |  Type  
------------+---------------+------------------+---------------------+--------
 pg_catalog | convert       | text             | text, name          | normal
 pg_catalog | convert       | text             | text, name, name    | normal

PostgreSQL 9.x, 将源编码字符串的字节流转换为指定编码的字符串的字节流返回。

避免了显示的问题。

                              List of functions
   Schema   |     Name     | Result data type | Argument data types |  Type  
------------+--------------+------------------+---------------------+--------
 pg_catalog | convert      | bytea            | bytea, name, name   | normal
 pg_catalog | convert_from | text             | bytea, name         | normal
 pg_catalog | convert_to   | bytea            | text, name          | normal

如果8.x需要避免显示问题,返回字节流,可以这样使用,推荐使用。

byteain(textout(convert(字符,'源编码','目标编码')))  

例子

当前数据库编码为UTF-8,中文排序未按拼音排序。

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 db0       | postgres | UTF8     | C       | C     | 
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

postgres=# select * from (values ('刘德华'), ('刘少奇')) t(id) order by id;
   id   
--------
 刘少奇
 刘德华
(2 rows)

按拼音排序方法(目标可以改成EUC_CN)

8.x

postgres=> select * from (values ('刘德华'), ('刘少奇')) t(id) order by byteain(textout(convert(id,'UTF-8','GBK')));
   id   
--------
 刘德华
 刘少奇
(2 rows)


9.x
postgres=# select * from (values ('刘德华'), ('刘少奇')) t(id) order by convert(id::bytea,'UTF-8','GBK');
   id   
--------
 刘德华
 刘少奇
(2 rows)

注意多音字

中文有一些多音字,比如重庆(chongqing), 但是编码时它可能是按zhong编码的,所以看这个例子。

postgres=> select * from (values ('中山'), ('重庆')) t(id) order by byteain(textout(convert(id,'UTF-8','GBK')));
  id  
------
 中山
 重庆
(2 rows)

索引

表达式索引即可,使用immutable function.

代码

8.x

postgres=> \df+ convert
                                                                                        List of functions
   Schema   |  Name   | Result data type | Argument data types |  Type  | Data access | Volatility |   Owner   | Language | Source code |                       Description                       
------------+---------+------------------+---------------------+--------+-------------+------------+-----------+----------+-------------+---------------------------------------------------------
 pg_catalog | convert | text             | text, name          | normal | no sql      | stable     | xxx | internal | pg_convert  | convert string with specified destination encoding name
 pg_catalog | convert | text             | text, name, name    | normal | no sql      | stable     | xxx | internal | pg_convert2 | convert string with specified encoding names
(2 rows)


9.x

postgres=# \df+ convert
                                                                                                List of functions
   Schema   |  Name   | Result data type | Argument data types |  Type  | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code |                 Description                  
------------+---------+------------------+---------------------+--------+------------+----------+----------+----------+-------------------+----------+-------------+----------------------------------------------
 pg_catalog | convert | bytea            | bytea, name, name   | normal | stable     | safe     | postgres | invoker  |                   | internal | pg_convert  | convert string with specified encoding names
(1 row)

src/backend/utils/mb/mbutils.c

/*
 * Convert string between two arbitrary encodings.
 *
 * BYTEA convert(BYTEA string, NAME src_encoding_name, NAME dest_encoding_name)
 */
Datum
pg_convert(PG_FUNCTION_ARGS)
{
        bytea      *string = PG_GETARG_BYTEA_PP(0);
        char       *src_encoding_name = NameStr(*PG_GETARG_NAME(1));
        int                     src_encoding = pg_char_to_encoding(src_encoding_name);
        char       *dest_encoding_name = NameStr(*PG_GETARG_NAME(2));
        int                     dest_encoding = pg_char_to_encoding(dest_encoding_name);
        const char *src_str;
        char       *dest_str;
        bytea      *retval;
        int                     len;

        if (src_encoding < 0)
                ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                 errmsg("invalid source encoding name \"%s\"",
                                                src_encoding_name)));
        if (dest_encoding < 0)
                ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                 errmsg("invalid destination encoding name \"%s\"",
                                                dest_encoding_name)));

        /* make sure that source string is valid */
        len = VARSIZE_ANY_EXHDR(string);
        src_str = VARDATA_ANY(string);
        pg_verify_mbstr_len(src_encoding, src_str, len, false);

        /* perform conversion */
        dest_str = (char *) pg_do_encoding_conversion((unsigned char *) src_str,
                                                                                                  len,
                                                                                                  src_encoding,
                                                                                                  dest_encoding);

        /* update len if conversion actually happened */
        if (dest_str != src_str)
                len = strlen(dest_str);

        /*
         * build bytea data type structure.
         */
        retval = (bytea *) palloc(len + VARHDRSZ);
        SET_VARSIZE(retval, len + VARHDRSZ);
        memcpy(VARDATA(retval), dest_str, len);

        if (dest_str != src_str)
                pfree(dest_str);

        /* free memory if allocated by the toaster */
        PG_FREE_IF_COPY(string, 0);

        PG_RETURN_BYTEA_P(retval);
}

参考

1. https://www.postgresql.org/docs/9.6/static/multibyte.html#AEN39011

2. https://www.postgresql.org/docs/9.6/static/sql-createconversion.html

3. https://www.postgresql.org/docs/9.6/static/catalog-pg-conversion.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
HybridDB for PostgreSQL排序键使用
#### 排序键是什么 排序键是表的一种属性,可以将数据按照排序键顺序存储在磁盘文件中。 排序键主要有两大优势:(1) 加速列存优化,收集的min、max元信息很少重叠,过滤性很好 (2) 对于含有order by和group by等需要排序的SQL可以避免再次排序,直接从磁盘中读取出来就是满足条件的有序数据 #### 创建表 ``` Command: CREAT
1185 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10062 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.10. 索引和排序规则
11.10. 索引和排序规则 一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则,你可能需要多个索引。 考虑这些语句: CREATE TABLE test1c ( id integer, content varchar COLLATE "x" ); CREATE INDEX test1c_content_index ON test1c (content); 该索引自动使用下层列的排序规则。
934 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
11605 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 7 章 查询_7.5. 行排序
7.5. 行排序 在一个查询生成一个输出表之后(在处理完选择列表之后),还可以选择性地对它进行排序。如果没有选择排序,那么行将以未指定的顺序返回。 这时候的实际顺序将取决于扫描和连接计划类型以及行在磁盘上的顺序,但是肯定不能依赖这些东西。
1071 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13875 0
PostgreSQL 百亿级数据范围查询, 分组排序窗口取值 极致优化 case
本文将对一个任意范围按ID分组查出每个ID对应的最新记录的CASE做一个极致的优化体验。优化后性能维持在可控范围内,任意数据量,毫秒级返回,性能平稳可控。比优化前性能提升1万倍。 CASE如下: 有一张数据表,结构: CREATE TABLE target_position
12786 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7361 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载