你了解数据库区域和编码吗？

呆呆的私房菜 2024-07-19

179

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

阅读本文可以了解PostgreSQL区域支持属性和字符编码及其转化的内容。

区域支持概述

区域指的是由于国家和习惯不同，在处理文字、日期、数字、货币格式等问题都会有所不同，所以操作系统的区域（locale）就是用来解决这个问题的。

[root@docker-chen ~]# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=


[root@docker-chen ~]# cat etc/locale.conf 
LANG="en_US.UTF-8"

注意：C或POSIX是特殊的区域，它是一个可移植的语言环境。它的字符分类（LC_CTYPE）部分对应于7位ASCII字符集。

区域支持指的是应用遵循文化偏好的问题，包含字母表、排序和数字格式等。PostgreSQL使用了操作系统提供的标准ISO C和POSIX的区域机制。

区域配置的影响

默认情况下，初始化数据库（initdb）的时候会按照它的执行环境的区域设置初始化数据库集簇。但是也可以在初始化的时候手工指定区域属性。

initdb -D $PGDATA -E utf-8 --locale=C

对数据库集簇的影响（postgresql.conf）：

[pg11@host-01 data]$ grep lc_ postgresql.conf 
lc_messages = 'en_US.utf8'      # locale for system error message
lc_monetary = 'en_US.utf8'      # locale for monetary formatting
lc_numeric = 'en_US.utf8'      # locale for number formatting
lc_time = 'en_US.utf8'        # locale for time formatting


## 影响C/S间字符编码转换
[pg11@host-01 data]$ grep encoding postgresql.conf
#client_encoding = sql_ascii    # actually, defaults to database


[pg11@host-01 data]$ grep earch_config postgresql.conf 
default_text_search_config = 'pg_catalog.english'

对创建数据库的影响（pg_database）：

## 主要影响字符编码、字符排序顺序和字符分类
postgres=# \l+
                                                                      数据库列表
   名称    |  拥有者  | 字符编码 |  排序规则  |   Ctype    |       存取权限        |  大小   |   表空间   |                    描述                    
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7461 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7281 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7281 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 207 MB  | pg_default | 
(4 行记录)


## Encoding：数据库编码，影响PostgreSQL进程和数据文件的字符编码
## Collate：字符排序顺序，影响字符比较、order by、字符列的索引使用
## Ctype：字符分类（什么是字母、是否区分大小写），影响upper、lower、initcap、大小写不敏感的模式匹配和使用了字符分类的正则表达式匹配

注意：创建数据库时默认集成数据库模板template1的区域设置。也可以指定其他的区域设置，但是必须使用template0作为模板。

字符编码

字符编码是字符在计算机中以二进制值表示的方法。支持简体中文的字符编码包括：UTF8、GB2312（ENC_CN）、GBK、GB18030。

PostgreSQL服务端支持的简体中文字符编码有：UTF8、ENC_CN。

PostgreSQL客户端支持的简体中文字符编码有：UTF8、ENC_CN、GBK、GB18030。

PostgreSQL使用的flex按照ASCII编码解析SQL语句，由于ASCII不兼容GBK的双字节编码和GB18030双字节和四字节编码，所以服务端也就不支持GBK和GB18030。

编码涉及的问题

编码问题主要涉及如下三个方面：

数据库服务器编码
指数据库服务器能够从客户端接收、存储以及向客户端提供该种编码的字符，并且能将该种编码的字符转换到其他编码。

postgres=# show server_encoding;
 server_encoding 
-----------------
 UTF8
(1 行记录)


postgres=# \l+
                                                                      数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则  |   Ctype    |       存取权限        |  大小   |   表空间   |                    描述                    
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7461 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7281 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7281 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 207 MB  | pg_default | 
(4 行记录)

数据库客户端编码
客户端工具支持某种编码，必须能够显示从数据库读取的该种编码的字符，也能通过当前工具将该种编码的字符提交给服务器端。

postgres=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 行记录)


postgres=# \encoding
UTF8


## 可以用SET命令修改客户端字符编码
set client_encoding to 'utf8';

本地环境编码
如果使用的是某个编辑器，则本地编码环境指的是编辑器编码设置
如果使用dos的命令行界面，本地环境就是指dos命令行环境的编码

C:\Users\12627>chcp
活动代码页: 936
## 936指的就是简体中文，GBK

注意：C/S编码不一致时服务端自动进行字符编码转换。

编码建议

建议服务端使用UTF8作为数据库编码
客户端通过client_encoding通知服务端自己的编码
数据库的Collate设置为C
只在真正需要按拼音排序的列或者表达式上使用Collate "zh_CN"
使用标准的区域无关的数据输出格式

本文内容就到这啦，阅读完本篇，相信你也对PostgreSQL的区域和编码有了一定的认知了吧！我们下篇再见！

点击上方公众号，关注我吧！

utf8 字符编码编码转换数据库字符

文章转载自呆呆的私房菜，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

你了解数据库区域和编码吗？

评论