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 database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
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 DATABASE
Time: 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 asciidb
You 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 TABLE
Time: 9.641 ms
[local]:5432 pg12@asciidb=# set client_encoding=sql_ascii;
SET
Time: 1.114 ms
[local]:5432 pg12@asciidb=# insert into t1 values(1,'测试');
INSERT 0 1
Time: 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 ms
192.168.26.28:5432 pg12@asciidb=# set client_encoding=sql_ascii;
SET
Time: 1.753 ms
192.168.26.28:5432 pg12@asciidb=# insert into t1 values(2,'测试');
INSERT 0 1
Time: 4.439 ms
192.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 ms
192.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 1
Time: 1.340 ms
[local]:5432 pg12@asciidb=# insert into t1 values (4, E'\xe6\xb5\x00');  
ERROR:  invalid byte sequence for encoding "SQL_ASCII": 0x00
Time: 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