PHP小码农

Appearance

字符集与编码 ​

本节讲解 PostgreSQL 的字符集编码（Character Set Encoding）机制。你将理解服务器编码与客户端编码的关系、编码转换规则与限制、排序规则（Collation）与编码的区别，以及中文环境下避免乱码的完整实践。

前置知识

阅读本节前，建议先了解：字符串操作

基础概念 ​

PostgreSQL 有两套与"文本"相关的设定，初学者极易混淆：

概念作用范围决定什么典型值
字符集编码（Character Set Encoding）数据库集群 / 会话字节如何解释为字符UTF8、GB18030、LATIN1
排序规则（Collation）数据库 / 列 / 表达式字符如何比较与排序en_US.UTF-8、zh_CN.UTF-8、C

关键术语：

  • 服务器编码（Server Encoding）：数据库自己存储文本时使用的编码，创建数据库时确定，之后不可更改（除非 dump 重建）。
  • 客户端编码（Client Encoding）：客户端与服务器之间传输文本时使用的编码，可按会话动态修改。
  • 编码转换（Encoding Conversion）：服务器在收发数据时，于服务器编码与客户端编码之间做实时转换。
  • 排序规则（Collation）：在解码后的字符序列上定义大小顺序、相等判断，与字节编码是正交的两件事。
  • SQL_ASCII：PostgreSQL 提供的"不做任何校验"的特殊编码，实际是字节容器而非字符集。

两者的关系可以这样理解：编码解决"字节 → 字符"，排序规则解决"字符 → 顺序"。

sql
SELECT
  current_setting('server_encoding')  AS server_encoding,
  current_setting('client_encoding')  AS client_encoding,
  current_setting('lc_collate')       AS lc_collate,
  current_setting('lc_ctype')         AS lc_ctype;
text
 server_encoding | client_encoding |   lc_collate   |    lc_ctype
-----------------+-----------------+----------------+----------------
 UTF8            | UTF8            | en_US.UTF-8    | en_US.UTF-8

信息相关的系统目录：

sql
SELECT
  datname,
  pg_encoding_to_char(encoding)  AS encoding,
  datcollate,
  datctype
FROM pg_database
ORDER BY datname;
text
  datname  | encoding |  datcollate   |   datctype
-----------+----------+---------------+---------------
 demo      | UTF8     | en_US.UTF-8   | en_US.UTF-8
 postgres  | UTF8     | en_US.UTF-8   | en_US.UTF-8
 template0 | UTF8     | en_US.UTF-8   | en_US.UTF-8
 template1 | UTF8     | en_US.UTF-8   | en_US.UTF-8

语法与用法 ​

编码相关的操作分散在三个层次：

text
-- 1. 查看当前设定
SHOW server_encoding;                       -- 只读，建库时决定
SHOW client_encoding;
SHOW lc_collate;
SHOW lc_ctype;

-- 2. 会话级修改客户端编码
SET client_encoding TO 'UTF8';
SET client_encoding TO 'GB18030';
SET NAMES 'UTF8';                            -- 等价写法

-- 3. 建库时指定编码与排序规则
CREATE DATABASE demo
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8'
  TEMPLATE template0;

-- 4. 字节与文本互转
convert_from(bytea, src_encoding)   -- bytea → text
convert_to(text, dest_encoding)     -- text → bytea
convert(bytea, src_encoding, dest_encoding)  -- bytea 转编码后仍为 bytea

-- 5. 手动声明字符串的编码
chr(int)                            -- 码点 → 字符
ascii(text)                         -- 字符 → 码点
U&'\4E2D\6587'                      -- Unicode 转义字面量

最简可运行示例：

sql
SHOW server_encoding;

SELECT '中文' AS val, length('中文') AS chars, octet_length('中文') AS bytes;
text
 server_encoding
-----------------
 UTF8

  val  | chars | bytes
-------+-------+-------
 中文  |     2 |     6

详细说明 ​

服务器编码：一次决定，终身不变 ​

服务器编码在 initdb 时确定，或创建数据库时通过 TEMPLATE template0 指定。已有数据库的编码无法用 ALTER 修改：

sql
ALTER DATABASE demo ENCODING 'GB18030';
text
ERROR:  unrecognized configuration parameter "encoding"

即使语法上不报错，语义上也不支持——因为改编码意味着重写全部索引（索引顺序依赖排序规则）与全部文本数据。

查看集群初始化时的编码：

bash
# 用 initdb 时的 locale 决定默认编码
initdb --encoding=UTF8 --locale=en_US.UTF-8 -D /var/lib/pgsql/data

结论：新建集群时直接选 UTF8，除非有历史系统兼容需求。

客户端编码：会话级可变的传输编码 ​

客户端编码决定了"数据在网络上以什么字节形式传输"。服务器在收发时做实时转换：

text
客户端 (GB18030 字节流)  ⇄  [服务器转换层]  ⇄  服务器 (UTF8 存储)
sql
-- 会话 1：客户端编码设为 UTF8
SET client_encoding TO 'UTF8';
SELECT '中文' AS val;
text
  val
-------
 中文
sql
-- 同一会话改为 GB18030（终端需支持 GB18030 才不会显示乱码）
SET client_encoding TO 'GB18030';
SHOW client_encoding;
text
 client_encoding
-----------------
 GB18030

关键点：转换发生在服务器端，表里存的始终是 UTF8。客户端编码只影响返回值与输入值的字节解释方式：

sql
SET client_encoding TO 'UTF8';
SELECT '中文'::text AS same_value, octet_length('中文') AS internal_bytes;
text
 same_value | internal_bytes
------------+----------------
 中文        |              6

无论客户端编码是什么，octet_length 都是 6（UTF8 下 2 个汉字）。

编码转换的可用组合 ​

服务器只能转换为它"认识"的编码，并且部分编码之间无法直接互转。查询可用的转换：

sql
SELECT
  source_encoding,
  dest_encoding,
  pg_encoding_to_char(source_encoding) AS src_name,
  pg_encoding_to_char(dest_encoding)   AS dst_name
FROM pg_conversion
WHERE pg_encoding_to_char(source_encoding) = 'UTF8'
LIMIT 5;
text
 source_encoding | dest_encoding | src_name | dst_name
-----------------+---------------+----------+----------
               6 |             3 | UTF8     | LATIN1
               6 |             4 | UTF8     | ISO_8859_5
               6 |             5 | UTF8     | ISO_8859_6
               6 |             7 | UTF8     | KOI8R
               6 |             8 | UTF8     | WIN1251

pg_conversion 中的系统默认转换覆盖了绝大多数组合。UTF8 → 其他单字节编码的转换可能失败，因为目标编码无法表示某些字符：

sql
SET client_encoding TO 'LATIN1';
SELECT '中文' AS cn;
text
ERROR:  character with byte sequence 0xe4 0xb8 0xad in encoding "UTF8" has no equivalent in encoding "LATIN1"

反之，从单字节编码转 UTF8 总是安全的（可表示范围更大）：

sql
SET client_encoding TO 'LATIN1';
SELECT convert_to('abc', 'LATIN1') AS latin_bytes;
text
 latin_bytes
-------------
 \x616263

转换失败时的行为由 client_encoding 与数据决定，无法表示就报错回滚，不会静默替换为 ?。这一点与其他数据库不同，是 PostgreSQL 的"宁可报错"设计。

convert_from / convert_to：手工处理字节 ​

当文本以 BYTEA 形式存放（如文件内容、历史遗留数据）时，需要用这两个函数显式指定源编码：

sql
-- 模拟一段 GB18030 编码的字节
SELECT
  convert_from('\x616263'::bytea, 'GB18030')  AS from_gb,
  convert_to('abc', 'GB18030')                AS to_gb;
text
 from_gb |   to_gb
---------+------------
 abc     | \x616263

来看一段真实的 GB18030 中文：

sql
-- '中文' 在 GB18030 下的字节是 d6 d0 ce c4
SELECT
  convert_from('\xd6d0cec4'::bytea, 'GB18030')  AS decoded,
  octet_length(convert_from('\xd6d0cec4'::bytea, 'GB18030')) AS reencoded_bytes;
text
 decoded | reencoded_bytes
---------+-----------------
 中文     |               6

convert_from 输出的 TEXT 会以服务器编码（UTF8）存储，所以字节数从 4 变成 6。

三个函数的区别：

函数入参出参用途
convert_from(bytea, enc)BYTEA + 源编码名TEXT已知字节编码，转为服务器编码文本
convert_to(text, enc)TEXT + 目标编码名BYTEA文本转为指定编码字节
convert(bytea, src, dst)BYTEA + 源/目标编码名BYTEA字节编码间转换，不经过服务器编码

convert 是三参数版本，输入输出都是 BYTEA，适合纯字节层面的转换：

sql
SELECT
  convert('\xd6d0cec4'::bytea, 'GB18030', 'UTF8')       AS converted_bytes,
  convert_from(convert('\xd6d0cec4'::bytea, 'GB18030', 'UTF8'), 'UTF8') AS as_text;
text
 converted_bytes | as_text
-----------------+---------
 \xe4b8ade69687  | 中文

chr / ascii：码点与字符互转 ​

sql
SELECT
  ascii('A')      AS ascii_A,
  chr(65)         AS chr_65,
  ascii('中')     AS ascii_cn,       -- UTF8 下返回 Unicode 码点
  chr(20013)      AS chr_cn,
  chr(128522)     AS emoji;
text
 ascii_A | chr_65 | ascii_cn | chr_cn | emoji
---------+--------+----------+--------+-------
      65 | A      |    20013 | 中     | 😊

ascii() 只有单字符入参才返回码点，多字符入参返回第一个字符的码点：

sql
SELECT ascii('ABC') AS first_only;
text
 first_only
------------
         65

chr() 在 UTF8 数据库下接收 Unicode 码点，因此可以生成任意字符。注意：chr(0) 会报错：

sql
SELECT chr(0);
text
ERROR:  null character not permitted

PostgreSQL 文本类型不允许存储 \0（空字节），这是 C 字符串语义的遗留限制。需要存放含 \0 的二进制数据时必须用 BYTEA。

Unicode 转义字面量 ​

对于无法直接键入的字符，可以用 U&'...' 语法：

sql
SELECT
  U&'\4E2D\6587'                          AS cn,
  U&'d\0061t\+000061'                      AS mixed,       -- \0061 = a
  U&'\0441\043B\043E\043D'                 AS russian,     -- 俄文"大象"
  length(U&'\4E2D\6587')                   AS cn_len;
text
  cn  | mixed | russian | cn_len
------+-------+---------+--------
 中文 | data  | слон    |      2

U&'...' 默认以反斜杠为转义符，可用 UESCAPE 改成其他符号：

sql
SELECT U&'!4E2D!6587' UESCAPE '!' AS cn;
text
  cn
------
 中文

排序规则：与编码正交的第二维度 ​

编码决定"字节 → 字符"，排序规则决定"字符 → 顺序"。两者独立配置：

sql
-- 同一个 UTF8 数据库，可以有不同的排序规则
SELECT
  'a' < 'B' COLLATE "C"            AS c_result,
  'a' < 'B' COLLATE "en_US"        AS en_result,
  'a' < 'B' COLLATE "zh_CN"        AS zh_result;
text
 c_result | en_result | zh_result
----------+-----------+-----------
 f        | t         | t

C 排序规则按字节值比较（'a'=0x61 > 'B'=0x42，所以 'a' < 'B' 为 f），而 en_US 按语言习惯比较（忽略大小写差异）。

查询可用的排序规则：

sql
SELECT
  collname,
  collprovider,
  collcollate
FROM pg_collation
WHERE collname IN ('C', 'POSIX', 'en_US', 'en_US.utf8', 'zh_CN', 'zh_CN.utf8', 'und-x-icu')
ORDER BY collname;
text
   collname    | collprovider |  collcollate
---------------+--------------+---------------
 C             | c            | C
 POSIX         | c            | POSIX
 en_US         | c            | en_US
 en_US.utf8    | c            | en_US.utf8
 und-x-icu     | i            | und
 zh_CN         | c            | zh_CN
 zh_CN.utf8    | c            | zh_CN.utf8
collprovider含义特点
clibc 提供的排序规则依赖操作系统 locale，版本间可能变化
iICU 提供的排序规则跨平台一致，支持更多语言特性
d数据库默认（pg_catalog."default"）继承建库时的 LC_COLLATE

libc 排序规则的一个真实痛点：同一条 SQL 在 macOS 与 Linux 上排序结果可能不同，因为 glibc 与 BSD libc 的排序实现有差异。生产环境跨平台部署时应改用 ICU：

sql
CREATE COLLATION zh_pinyin (
  provider = icu,
  locale   = 'zh-u-co-pinyin'
);

SELECT name FROM (VALUES ('张三'), ('李四'), ('王五')) AS t(name)
ORDER BY name COLLATE zh_pinyin;
text
 name
------
 李四
 王五
 张三

ICU 的 zh-u-co-pinyin 按拼音排序，李(li) < 王(wang) < 张(zhang)。libc 的 zh_CN.UTF-8 在多数平台上按笔画或 ICU 默认规则排序，结果不同。

确定性排序规则与非确定性排序规则 ​

默认排序规则是确定性（Deterministic）的：不同字符串视为不等。ICU 允许创建非确定性（Nondeterministic）排序规则，忽略大小写与重音：

sql
-- PostgreSQL 12+ 支持
CREATE COLLATION case_insensitive (
  provider = icu,
  locale   = 'und-u-ks-level2',
  deterministic = false
);

SELECT
  'ABC' = 'abc' COLLATE case_insensitive   AS eq_ignoring_case,
  'ABC' = 'abc'                             AS eq_default;
text
 eq_ignoring_case | eq_default
------------------+------------
 t                | f

非确定性排序规则的重要限制：不能用于 LIKE、正则、|| 之类的模式匹配操作，也不能作为 B-tree 的唯一性排序规则（因为相等判断不唯一）。

SQL_ASCII：危险的字节容器 ​

SQL_ASCII 编码下 PostgreSQL 完全不做编码校验，也不做客户端/服务器转换：

sql
-- 在 SQL_ASCII 数据库中可以存入非法 UTF8 字节
INSERT INTO raw_bytes (payload) VALUES (convert_to('\xff\xfe'::bytea, 'SQL_ASCII'));
text
INSERT 0 1

这看似"灵活"，实则是数据灾难的温床：

问题后果
无编码校验非法字节入库，后续查询报错或显示乱码
无转换客户端编码设置失效，SET client_encoding 无实际作用
函数行为异常upper() / lower() / length() 对多字节字符按字节处理
无法迁移无法可靠地从 SQL_ASCII 转到 UTF8
sql
-- SQL_ASCII 下 length 按字节计数，中文长度错误
SELECT length('中文') AS wrong_len;
text
 wrong_len
-----------
         6

length('中文') 在 SQL_ASCII 下返回 6（字节数）而非 2（字符数）。生产环境绝不应使用 SQL_ASCII，唯一合理场景是纯粹的二进制归档表，而这种需求应当直接用 BYTEA。

中文环境的常见乱码场景 ​

乱码的本质是编码的写入方与读取方不一致。三种典型情况：

场景 1：客户端编码与终端实际编码不一致

text
终端是 UTF-8，client_encoding 被设为 GB18030
→ 显示：涓枃（UTF8 字节被按 GB18030 解释）
sql
-- 检查并修正
SHOW client_encoding;
SET client_encoding TO 'UTF8';

场景 2：数据以错误编码写入

sql
-- 错误：把 GB18030 字节当 UTF8 文本插入
INSERT INTO logs (msg) VALUES ('\xd6d0cec4');   -- 直接写会尝试按 UTF8 解析
text
ERROR:  invalid byte sequence for encoding "UTF8": 0xd6 0xd0

正确做法是先声明源编码：

sql
INSERT INTO logs (msg)
VALUES (convert_from('\xd6d0cec4'::bytea, 'GB18030'));

场景 3：从 CSV/文本文件导入

bash
# 导入 GB18030 编码的 CSV
psql -d demo -c "SET client_encoding TO 'GB18030'; \copy logs(msg) FROM 'gb.csv' CSV"

或直接转码后导入：

bash
iconv -f GB18030 -t UTF-8 gb.csv > utf8.csv
psql -d demo -c "\copy logs(msg) FROM 'utf8.csv' CSV"

Unicode 规范化：NFC 与 NFD ​

Unicode 中同一"字符"可能有多种码点表示。例如 é 可以是一个码点 U+00E9，也可以是 e + 组合重音符 U+0065 U+0301：

sql
SELECT
  U&'\00E9'                                  AS precomposed,
  U&'e\0301'                                 AS decomposed,
  U&'\00E9' = U&'e\0301'                     AS equal,
  length(U&'\00E9')                          AS len_nfc,
  length(U&'e\0301')                         AS len_nfd;
text
 precomposed | decomposed | equal | len_nfc | len_nfd
-------------+------------+-------+---------+---------
 é           | é          | f     |       1 |       2

NFC（组合形式）与 NFD（分解形式）的字符串被判为不等，唯一索引也会把两者当两条记录。PostgreSQL 内置没有规范化函数，需要借助扩展：

sql
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT unaccent('café') AS stripped;
text
 stripped
----------
 cafe

unaccent 只能去掉重音，不能做完整 NFC 规范化。完整方案是在应用层（如 Python 的 unicodedata.normalize('NFC', s)）先规范化再入库，并在列上加约束：

sql
ALTER TABLE users ADD CONSTRAINT chk_name_nfc
  CHECK (full_name = normalize(full_name, NFC));

上述 normalize() 函数需要 PostgreSQL 13+ 且仅在部分平台可用；更稳妥的做法是在应用层保证。

编码与索引、LIKE 的相互影响 ​

非 C 排序规则下，LIKE 的优化会失效，而 text_pattern_ops 又是按 C 语义比较的：

sql
-- 默认列（en_US.UTF-8 排序规则）不能高效支持前缀 LIKE
CREATE TABLE t_default (name TEXT);
CREATE INDEX idx_default ON t_default (name);

-- 显式指定 C 语义操作符类才能支持前缀匹配
CREATE TABLE t_cops (name TEXT);
CREATE INDEX idx_cops ON t_cops (name text_pattern_ops);

同理，varchar_pattern_ops 用于 VARCHAR：

sql
CREATE INDEX idx_varchar_prefix ON users (email varchar_pattern_ops);
SELECT * FROM users WHERE email LIKE 'zhang%';   -- 可走索引

结论：中文环境下若大量使用前缀模糊查询，建库时就应选 C 或 C.UTF-8 排序规则（或在需要的列上显式建 *_pattern_ops 索引）。

数据库、表、列层面的编码与排序规则控制 ​

排序规则可以在三个层级覆盖：

sql
-- 1. 建库时指定（影响默认值）
CREATE DATABASE demo_zh
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8'
  TEMPLATE template0;

-- 2. 建表/列时指定
CREATE TABLE products (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT COLLATE "C",                    -- 该列按字节序比较
  title  TEXT COLLATE "zh_CN"                 -- 该列按中文规则比较
);

-- 3. 表达式中临时指定
SELECT * FROM products ORDER BY name COLLATE "zh_CN";

注意：列级 COLLATE 会影响索引。一个 COLLATE "C" 的列上的索引只能用于 C 语义的查询：

sql
CREATE INDEX idx_products_name ON products (name);        -- 按 C 排序规则索引
SELECT * FROM products ORDER BY name;                     -- 走索引
SELECT * FROM products ORDER BY name COLLATE "zh_CN";      -- 不走索引

实战示例 ​

以下示例在 demo 库中按顺序执行。

第 1 步：查看当前环境编码设定

sql
SHOW server_encoding;
SHOW client_encoding;

SELECT
  current_database()             AS db,
  pg_encoding_to_char(encoding)  AS db_encoding,
  datcollate,
  datctype
FROM pg_database
WHERE datname = current_database();
text
 server_encoding
-----------------
 UTF8

 client_encoding
-----------------
 UTF8

    db   | db_encoding |  datcollate   |   datctype
---------+-------------+---------------+---------------
 demo    | UTF8        | en_US.UTF-8   | en_US.UTF-8

第 2 步：建一张多语言文本表

sql
DROP TABLE IF EXISTS i18n_demo;
CREATE TABLE i18n_demo (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  lang     VARCHAR(8)  NOT NULL,
  content  TEXT       NOT NULL
);

INSERT INTO i18n_demo (lang, content) VALUES
  ('zh',   '中文内容'),
  ('en',   'English text'),
  ('ja',   '日本語のテキスト'),
  ('ru',   'Русский текст'),
  ('emoji','数据可视化 📊📈'),
  ('混排',  'Mixed 中英 123 混排');

第 3 步：验证字符数与字节数差异

sql
SELECT
  lang,
  content,
  length(content)        AS chars,
  octet_length(content)  AS bytes,
  round(octet_length(content)::numeric / length(content), 2) AS bytes_per_char
FROM i18n_demo
ORDER BY id;
text
  lang |      content      | chars | bytes | bytes_per_char
-------+-------------------+-------+-------+----------------
 zh    | 中文内容          |     4 |    12 |           3.00
 en    | English text      |    12 |    12 |           1.00
 ja    | 日本語のテキスト   |     8 |    24 |           3.00
 ru    | Русский текст     |    13 |    25 |           1.92
 emoji | 数据可视化 📊📈    |     7 |    19 |           2.71
 混排   | Mixed 中英 123 混排 |    14 |    22 |           1.57

从 bytes_per_char 可以看出：ASCII 1 字节/字符、中日文 3 字节/字符、西里尔字母 2 字节/字符、emoji 4 字节/字符。混合内容取加权平均，所以 混排 行是 1.57。

第 4 步：码点验证

sql
SELECT
  content,
  ascii(content)          AS first_cp,
  chr(ascii(content))     AS first_char,
  to_hex(ascii(content))  AS first_hex
FROM i18n_demo
WHERE lang IN ('zh', 'en', 'emoji')
ORDER BY id;
text
    content     | first_cp | first_char | first_hex
----------------+----------+------------+-----------
 中文内容        |    20013 | 中          | 4e2d
 English text   |       69 | E          | 45
 数据可视化 📊📈  |    25968 | 数          | 6570

中文 中 的码点是 U+4E2D（十进制 20013），验证了 ascii() 在 UTF8 下返回 Unicode 码点。

第 5 步：客户端编码切换对返回值的影响

sql
-- 切到 GB18030，模拟 Windows 中文终端
SET client_encoding TO 'GB18030';
SELECT content, octet_length(content) AS internal_bytes
FROM i18n_demo WHERE lang = 'zh';
text
 content  | internal_bytes
----------+----------------
 中文内容  |             12

即使客户端编码是 GB18030，octet_length 仍为 12（UTF8 存储），因为函数在服务器端执行。改变的只是返回值传输到客户端时的字节表示。

sql
-- 切回 UTF8
SET client_encoding TO 'UTF8';

第 6 步：编码转换失败的观察

sql
SET client_encoding TO 'LATIN1';
SELECT content FROM i18n_demo WHERE lang = 'zh';
text
ERROR:  character with byte sequence 0xe4 0xb8 0xad in encoding "UTF8" has no equivalent in encoding "LATIN1"

转换失败会整条语句报错，而不是部分行显示 ?。

sql
SET client_encoding TO 'UTF8';

第 7 步：GB18030 字节的手工解码

sql
SELECT
  '\xd6d0cec4'::bytea                                        AS raw_bytes,
  convert_from('\xd6d0cec4'::bytea, 'GB18030')               AS decoded,
  octet_length(convert_from('\xd6d0cec4'::bytea, 'GB18030')) AS utf8_bytes,
  convert_to(convert_from('\xd6d0cec4'::bytea, 'GB18030'), 'GB18030') AS roundtrip;
text
 raw_bytes | decoded | utf8_bytes | roundtrip
-----------+---------+------------+-----------
 \xd6d0cec4 | 中文     |          6 | \xd6d0cec4

往返转换（GB18030 → UTF8 → GB18030）后字节完全一致，验证了转换无损。

第 8 步：排序规则对排序结果的影响

sql
DROP TABLE IF EXISTS sort_demo;
CREATE TABLE sort_demo (name TEXT);

INSERT INTO sort_demo (name) VALUES
  ('Banana'), ('apple'), ('Cherry'), ('banana');

SELECT
  string_agg(name, ', ' ORDER BY name COLLATE "C")      AS c_order,
  string_agg(name, ', ' ORDER BY name COLLATE "en_US")  AS en_order
FROM sort_demo;
text
           c_order            |          en_order
------------------------------+-----------------------------
 Banana, Cherry, apple, banana | apple, Banana, banana, Cherry

C 排序规则按字节值排序（大写字母 0x41~0x5A 全部小于小写字母 0x61~0x7A），所以 Banana、Cherry 排在 apple 之前。en_US 按语言习惯忽略大小写差异。

第 9 步：非确定性排序规则实验（PostgreSQL 12+）

sql
DROP COLLATION IF EXISTS ci_test;
CREATE COLLATION ci_test (
  provider = icu,
  locale = 'und-u-ks-level2',
  deterministic = false
);

SELECT
  'ABC' = 'abc' COLLATE ci_test  AS ci_equal,
  'ABC' = 'abc'                   AS default_equal,
  'ABC' LIKE 'abc' COLLATE ci_test AS ci_like;
text
 ci_equal | default_equal | ci_like
----------+---------------+---------
 t        | f             |

ci_like 那一列为空，因为非确定性排序规则不能用于 LIKE：

text
ERROR:  nondeterministic collations are not supported for LIKE

第 10 步：清理实验对象

sql
DROP COLLATION IF EXISTS ci_test;
DROP TABLE IF EXISTS sort_demo;
DROP TABLE IF EXISTS i18n_demo;

注意事项 ​

1. 服务器编码建库后不可更改

唯一变更途径是 pg_dump 导出后重建数据库：

bash
pg_dump -E UTF8 demo > demo.sql
createdb -E UTF8 -T template0 demo_new
psql -d demo_new -f demo.sql

2. pg_dump 的 -E 指定的是导出文件编码，不是源库编码

源库编码由数据本身决定，-E 只影响输出文件的字节表示。跨编码迁移时：

bash
pg_dump -E UTF8 --no-owner demo > demo.sql   # 导出为 UTF8

3. SQL_ASCII 数据库中 length() 按字节计数

sql
-- SQL_ASCII 库中
SELECT length('中文');   -- 返回 6，不是 2

所有字符串函数行为都会退化到字节级别，包括 upper()（对中文无效）、substr()（可能切断汉字）。

4. 客户端编码错误会导致写入乱码而非报错

当客户端编码设置正确但终端/文件实际编码不一致时，PostgreSQL 会在应用层正确转换，但终端显示仍是乱码。这类问题的排查顺序：

text
1. SHOW client_encoding;        -- 会话设定
2. echo $LANG;                  -- 终端 locale
3. 检查文件实际编码 file -i x.csv
4. 检查驱动配置（JDBC: characterEncoding=UTF-8）

JDBC 的典型错误配置：

text
jdbc:postgresql://host/demo?characterEncoding=GBK   # 与库内 UTF8 冲突

5. chr(0) 与 \0 均不可用于文本

sql
SELECT chr(0);
SELECT convert_from('\x00'::bytea, 'UTF8');
text
ERROR:  null character not permitted

含 \0 的数据必须用 BYTEA：

sql
SELECT '\x00'::bytea AS null_byte;
text
 null_byte
-----------
 \x00

6. 非确定性排序规则不能用于 LIKE、正则、UNIQUE 的默认判断

text
ERROR:  nondeterministic collations are not supported for LIKE
ERROR:  nondeterministic collations are not supported for regular expression

需要大小写不敏感的唯一约束，应使用 citext 扩展或 lower() 表达式索引。

7. libc 排序规则跨平台不一致

同一份数据在 macOS 开发、Linux 生产上可能排序不同：

sql
-- macOS（BSD libc）
SELECT string_agg(name, ',' ORDER BY name) FROM sort_demo;

-- Linux（glibc）可能给出不同顺序

跨平台项目应统一使用 ICU 排序规则，或在建库时指定 C。

8. locale 设定影响 to_char 的月份名、日期名

sql
SET lc_time TO 'zh_CN.UTF-8';
SELECT to_char(DATE '2026-09-22', 'Day') AS weekday;
text
 weekday
---------
 星期二

lc_time 是会话级可变的，但依赖操作系统安装了对应 locale 数据。

9. LIKE 与排序规则操作符类的强绑定

sql
-- 建了普通索引却期望前缀匹配走索引 —— 不会发生
CREATE INDEX idx_bad ON t_default (name);
SELECT * FROM t_default WHERE name LIKE 'abc%';   -- 全表扫描（en_US 排序规则）

必须用 text_pattern_ops：

sql
CREATE INDEX idx_good ON t_default (name text_pattern_ops);

10. 编码转换错误会中断整个事务

sql
BEGIN;
INSERT INTO logs (msg) VALUES ('ok');
SET client_encoding TO 'LATIN1';
SELECT '中文';             -- 报错
COMMIT;                    -- 事务已中止
text
ERROR:  character with byte sequence 0xe4 0xb8 0xad in encoding "UTF8" has no equivalent in encoding "LATIN1"
ROLLBACK

必须在应用层捕获这类错误并重置 client_encoding。

最佳实践 ​

1. 新集群统一用 UTF8 + ICU 排序规则

bash
initdb --encoding=UTF8 -D /var/lib/pgsql/data
sql
CREATE DATABASE demo
  ENCODING 'UTF8'
  LOCALE_PROVIDER icu
  ICU_LOCALE 'zh-CN'
  TEMPLATE template0;

LOCALE_PROVIDER icu 与 ICU_LOCALE 是 PostgreSQL 15+ 提供的选项，可让默认排序规则由 ICU 提供，跨平台一致。

2. 应用连接串显式声明编码

text
# JDBC
jdbc:postgresql://host:5432/demo?characterEncoding=UTF-8&allowUnicode=true

# libpq
PGCLIENTENCODING=UTF8

# Django
DATABASES = {'default': {'OPTIONS': {'client_encoding': 'UTF8'}}}

3. 前缀模糊查询的列显式建 *_pattern_ops 索引

sql
CREATE INDEX idx_users_email_prefix ON users (email varchar_pattern_ops);
CREATE INDEX idx_users_name_prefix  ON users (name  text_pattern_ops);

4. 外部数据入口统一先转码

bash
# 任何外部文件先转成 UTF8 再导入
iconv -f GB18030 -t UTF-8 -c input.csv > utf8.csv
psql -d demo -c "\copy staging FROM 'utf8.csv' CSV HEADER"

-c 参数会丢弃无法转换的字符，避免导入中断。丢弃前建议先统计：

bash
iconv -f GB18030 -t UTF-8 input.csv > utf8.csv 2> errors.txt
wc -l errors.txt

5. 用 CHECK 约束阻止非法字节进入文本列

sql
ALTER TABLE logs ADD CONSTRAINT chk_msg_valid_utf8
  CHECK (octet_length(msg) = octet_length(convert_from(convert_to(msg, 'UTF8'), 'UTF8')));

更简单的做法是利用 PostgreSQL 本身在 UTF8 库中的强制校验——只要库是 UTF8，非法字节就会在插入时报错，这本身就是最好的防线。

6. 应用层做 Unicode 规范化后再入库

python
import unicodedata
normalized = unicodedata.normalize('NFC', user_input)

配合唯一索引可避免 é（NFC）与 é（NFD）重复入库。

7. 中文全文检索用专门的分词方案

内置 to_tsvector 不支持中文分词：

sql
SELECT to_tsvector('simple', '中文内容测试');
text
     to_tsvector
---------------------
 '中文内容测试':1

整串作为一个 token，无法检索子串。中文场景应使用 zhparser 或 pg_jieba 扩展：

sql
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;
SELECT to_tsvector('chinese', '中文内容测试');

8. 排序需求多样时用 ICU 命名排序规则，不要在 SQL 里散落 COLLATE

sql
CREATE COLLATION zh_pinyin (provider = icu, locale = 'zh-u-co-pinyin');
CREATE COLLATION zh_stroke (provider = icu, locale = 'zh-u-co-stroke');

-- 建表时把语义固化到列上
CREATE TABLE contacts (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT COLLATE zh_pinyin NOT NULL
);

9. 定期检查数据库中是否存在异常字节（历史脏数据）

sql
SELECT
  count(*) FILTER (WHERE octet_length(msg) <> octet_length(msg::text)) AS suspicious,
  count(*) AS total
FROM logs;

对可疑行导出后人工核对，或用 pg_verify_checksums / amcheck 检查页级损坏。

10. 中文排序不确定时，优先显式 COLLATE "C" 保证可重现性

若业务只关心"顺序稳定"，不关心语言学正确性（如生成缓存 key、分页排序），用 C 最简单：

sql
SELECT id, name
FROM products
ORDER BY name COLLATE "C", id;   -- 顺序完全可重现

下一节

继续学习：DATE / TIME / TIMESTAMP

参考链接 ​