PostgreSQL DBA(103) - pgAdmin(Don't do this：Encoding)
no zuo no die系列，来自于pg的wiki。
这是第一部分，关于数据库编码，不要使用SQL_ASCII字符集编码。原因是：
While the name suggests that this encoding is in some meaningful way related to ASCII, it is not. Instead, it simply forbids the use of NUL bytes.
More importantly, SQL_ASCII means "no conversions" for the purpose of all encoding conversion functions. That is to say, the original bytes are simply treated as being in the new encoding, subject to validity checks, without any regard for what they mean. Unless extreme care is taken, an SQL_ASCII database will usually end up storing a mixture of many different encodings with no way to recover the original characters reliably.
PostgreSQL中的SQL_ASCII类似于Oracle的单字节字符集如ISO8859P1，可存储除0x00外（Oracle ISO8859P1字符集可存储0x00）的其他所有字节码（即0x01-0xFF）。
下面的实验，创建SQL_ASCII的数据库，分别通过Windows和Linux客户端访问数据库并插入数据来验证不同客户端字符编码的情况下，SQL_ASCII字符集下的数据存储方式。
创建数据库
使用create database创建数据库
[local]:5432 pg12@testdb=# \help create databaseCommand: CREATE DATABASEDescription: create a new databaseSyntax:CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]URL: https://www.postgresql.org/docs/12/sql-createdatabase.html[local]:5432 pg12@testdb=# create database asciidb with encoding=sql_ascii;ERROR: new encoding (SQL_ASCII) 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.Time: 3.200 ms[local]:5432 pg12@testdb=# create database asciidb with encoding=sql_ascii template=template0;CREATE DATABASETime: 633.163 ms[local]:5432 pg12@testdb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+-----------+---------+-------+------------------- asciidb | pg12 | SQL_ASCII | C | C | monitor | pg12 | UTF8 | C | C | postgres | pg12 | UTF8 | C | C | template0 | pg12 | UTF8 | C | C | =c/pg12 + | | | | | pg12=CTc/pg12 template1 | pg12 | UTF8 | C | C | =c/pg12 + | | | | | pg12=CTc/pg12 testdb | pg12 | UTF8 | C | C | (6 rows)插入数据
Linux
[local]:5432 pg12@testdb=# \c asciidbYou are now connected to database "asciidb" as user "pg12".[local]:5432 pg12@asciidb=# show client_encoding; client_encoding ----------------- UTF8(1 row)Time: 0.486 ms[local]:5432 pg12@asciidb=# create table t1(id int,c1 varchar(20));CREATE TABLETime: 9.641 ms[local]:5432 pg12@asciidb=# set client_encoding=sql_ascii;SETTime: 1.114 ms[local]:5432 pg12@asciidb=# insert into t1 values(1,'测试');INSERT 0 1Time: 1.867 ms[local]:5432 pg12@asciidb=#Windows
192.168.26.28:5432 pg12@asciidb=# show client_encoding; client_encoding----------------- GBK(1 row)Time: 1.953 ms192.168.26.28:5432 pg12@asciidb=# set client_encoding=sql_ascii;SETTime: 1.753 ms192.168.26.28:5432 pg12@asciidb=# insert into t1 values(2,'测试');INSERT 0 1Time: 4.439 ms192.168.26.28:5432 pg12@asciidb=#查询数据
分别在Linux客户端和Windows客户端下查询数据
Linux
[local]:5432 pg12@asciidb=# select id,c1,c1::bytea from t1; id | c1 | c1 ----+--------+---------------- 1 | 测试 | \xe6b58be8af95 2 | ²㋔ | \xb2e2cad4(2 rows)Time: 2.254 ms[local]:5432 pg12@asciidb=#Windows
192.168.26.28:5432 pg12@asciidb=# select id,c1,c1::bytea from t1; id | c1 | c1----+--------+---------------- 1 | 娴嬭瘯 | \xe6b58be8af95 2 | 测试 | \xb2e2cad4(2 rows)Time: 3.555 ms192.168.26.28:5432 pg12@asciidb=#可以看到，在Linux下插入的数据以UTF8编码，而在Windows平台下插入的数据则以GBK编码，除了ASCII 0外的其他字符，"照单全收"。
[local]:5432 pg12@asciidb=# insert into t1 values (3, E'\xe6\xb5\x8b'); INSERT 0 1Time: 1.340 ms[local]:5432 pg12@asciidb=# insert into t1 values (4, E'\xe6\xb5\x00'); ERROR: invalid byte sequence for encoding "SQL_ASCII": 0x00Time: 1.164 ms[local]:5432 pg12@asciidb=# select * from t1; id | c1 ----+-------- 1 | 测试 2 | ²㋔ 3 | 测(3 rows)Time: 2.117 ms[local]:5432 pg12@asciidb=#参考资料
PostgreSQL Server Encoding sql_ascii attention
Character Set Support
Don't Do This
