PostgreSQL DBA(103) - pgAdmin(Don't do this：Encoding)

no zuo no die系列，來(lái)自于pg的wiki。

這是第一部分，關(guān)于數(shù)據(jù)庫(kù)編碼，不要使用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的單字節(jié)字符集如ISO8859P1，可存儲(chǔ)除0x00外（Oracle ISO8859P1字符集可存儲(chǔ)0x00）的其他所有字節(jié)碼（即0x01-0xFF）。

下面的實(shí)驗(yàn)，創(chuàng)建SQL_ASCII的數(shù)據(jù)庫(kù)，分別通過(guò)Windows和Linux客戶端訪問數(shù)據(jù)庫(kù)并插入數(shù)據(jù)來(lái)驗(yàn)證不同客戶端字符編碼的情況下，SQL_ASCII字符集下的數(shù)據(jù)存儲(chǔ)方式。

創(chuàng)建數(shù)據(jù)庫(kù)
使用create database創(chuàng)建數(shù)據(jù)庫(kù)

[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)

插入數(shù)據(jù)
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,'測(cè)試');
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,'測(cè)試');
INSERT 0 1
Time: 4.439 ms
192.168.26.28:5432 pg12@asciidb=#

查詢數(shù)據(jù)
分別在Linux客戶端和Windows客戶端下查詢數(shù)據(jù)
Linux

[local]:5432 pg12@asciidb=# select id,c1,c1::bytea from t1;
 id |   c1   |       c1       
----+--------+----------------
  1 | 測(cè)試 | \xe6b58be8af95
  2 | 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 | 測(cè)試   | \xb2e2cad4
(2 rows)
Time: 3.555 ms
192.168.26.28:5432 pg12@asciidb=#

可以看到，在Linux下插入的數(shù)據(jù)以UTF8編碼，而在Windows平臺(tái)下插入的數(shù)據(jù)則以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 | 測(cè)試
  2 | 2?
  3 | 測(cè)
(3 rows)
Time: 2.117 ms
[local]:5432 pg12@asciidb=#

參考資料
PostgreSQL Server Encoding sql_ascii attention
Character Set Support
Don’t Do This