Shulou(Shulou.com)06/01 Report--

No zuo no die series, from wiki of pg.

This is the first part, about database encoding, do not use SQL_ASCII character set encoding. The reason is:

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.

SQL_ASCII in PostgreSQL is similar to Oracle's single-byte character set such as ISO8859P1, which stores all bytecodes (that is, 0x01-0xFF) except 0x00 (the Oracle ISO8859P1 character set can store 0x00).

The following experiment is to create a database of SQL_ASCII, access the database through Windows and Linux clients and insert data to verify the data storage under the SQL_ASCII character set under different client character encodings.

Create a database

Create a database using 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)

Insert data

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; INSERT 0 1Time: 1.867 ms [local]: 5432 pg12@asciidb=#

Windows

192.168.26.28 pg12@asciidb=# show client_encoding; client_encoding- GBK 5432 (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; INSERT 0 1Time: 4.439 ms192.168.26.28:5432 pg12@asciidb=#

Query data

Query data under Linux client and Windows client respectively

Linux

[local]: 5432 pg12@asciidb=# select id,c1,c1::bytea from T1; id | C1 | C1-+-+-1 | Test |\ xe6b58be8af95 2 | ²㋔ |\ xb2e2cad4 (2 rows) Time: 2.254 ms [local]: 5432 pg12@asciidb=#

Windows

192.168.26.28xb2e2cad4 5432 pg12@asciidb=# select id,c1,c1::bytea from T1; id | C1 | C1 xb2e2cad4 rows Time: 3.555 ms192.168.26.28:5432 pg12@asciidb=#

As you can see, the data inserted under Linux is encoded in UTF8, while the data inserted in Windows platform is encoded in GBK, and all characters except ASCII 0 are accepted according to order.

[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 | Test 2 | ²㋔ 3 | Test (3 rows) Time: 2.117 ms [local]: 5432 pg12@asciidb=#

references

PostgreSQL Server Encoding sql_ascii attention

Character Set Support

Don't Do This

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.