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](https://billtian.github.io/digoal.blog/2013/03/15/01.html)
[Character Set Support](https://www.postgresql.org/docs/12/multibyte.html)
[Don't Do This](https://wiki.postgresql.org/wiki/Don't_Do_This)

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

相關文章