How to specify the server encoding format of a database as the storage format (O engine)-PolarDB(PolarDB)-阿里云帮助中心

You can specify a server encoding format for your database. This format serves as the storage format for the data. This topic provides examples of how to use the GBK and GB18030 encoding formats. Both are compatible with PolarDB.

Introduction

GBK
GBK (Chinese Internal Code Specification) is an extended character encoding for Chinese. It contains 21,886 Chinese characters and graphic symbols, including the following:
- All Chinese characters and non-character symbols from GB2312.
- All Chinese characters from BIG5.
- Other CJK characters from the national standard GB13000.
- Other Chinese characters, radicals, and symbols.
GB18030
GB18030 is the Chinese national standard for character encoding, officially named GB18030-2005 "Information technology—Chinese coded character set". It is the latest standard and is backward compatible with GBK and GB2312. GB18030 contains 70,244 Chinese characters and uses a multi-byte encoding of one, two, or four bytes per character. The single-byte and double-byte portions are fully compatible with GBK. The four-byte portion adds more characters, including 6,582 from CJK Unified Ideographs Extension A. The total encoding space can support up to 1.61 million characters. It supports all scripts from the Chinese mainland and includes traditional Chinese, Japanese, and Korean characters.

Precautions

If your Oracle database uses the GBK character set, use the GB18030 character set for PolarDB. The Oracle GBK character set contains more characters than the PolarDB GBK character set, which makes it more similar to the GB18030 character set in PolarDB. GB18030 is also backward compatible with GBK.
Character lengths differ between UTF-8 and GBK. For example, a common Chinese character has a length of 2 in GBK and 3 in UTF-8. Be aware of the character semantics when you use these encodings.
When you create a database, specify the LC_COLLATE and LC_CTYPE properties as needed. Collation rules can differ between character sets.
Note
Due to internal encoding reasons, do not set the LC_CTYPE property to 'zh_CN.gbk' or 'zh_CN.gb18030'. This can cause functions, such as upper, to return unexpected results.

Examples

Specify the GBK encoding format.

Create the db_gbk database. Set the server encoding format to GBK and the client encoding format to UTF-8.

CREATE DATABASE db_gbk template template0 encoding = gbk LC_COLLATE = 'C' LC_CTYPE = 'C';
\c db_gbk;
SET client_encoding = 'utf-8';

Create the test_table table.
```
CREATE TABLE test_table(f1 varchar(3));
```

Insert data into the test_table table.

INSERT INTO test_table (f1) VALUES ('Zhang San');
INSERT INTO test_table (f1) VALUES ('Li Si');
INSERT INTO test_table (f1) VALUES ('Mr. Wang');
INSERT INTO test_table (f1) VALUES ('Ms. Zhang');

Query the full data in the test_table table and sort it in ascending order.
```
SELECT * FROM test_table ORDER BY f1;
```
The query result is as follows:
```
 Li Si
 Mr. Wang
 Ms. Zhang
 Zhang San
(4 rows)
```

Specify the GB18030 encoding format.

Create the db_gb18030 database. Set the server encoding format to GB18030 and the client encoding format to UTF-8.

CREATE DATABASE db_gb18030 template template0 encoding = gb18030 LC_COLLATE = 'C' LC_CTYPE = 'C';
\c db_gb18030;
SET client_encoding = 'utf-8';

Create the polar_text table.

CREATE TABLE polar_text(i int, f1 text);

Insert data into the polar_text table.

INSERT INTO polar_text (f1) VALUES ('Zhang San');
INSERT INTO polar_text (f1) VALUES ('Li Si');
INSERT INTO polar_text (f1) VALUES ('Mr. Wang');
INSERT INTO polar_text (f1) VALUES ('€Ms. Zhang');

Query the full data in the polar_text table and sort it in ascending order.

SELECT * FROM polar_text ORDER BY f1;

The query result is as follows:

 i |     f1
---+------------  
   | Li Si
   | Mr. Wang 
   | €Ms. Zhang
   | Zhang San
(4 rows)