Table-level Character Sets and Collations

Set the default character set and collation for a table. For details about how to create a table, see CREATE TABLE.

CREATE TABLE table_name (column_list)
    [ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ]
    [ [DEFAULT] COLLATE [ = ] default_collation ]

Modify the default character set and collation of a table. The modification does not affect the existing columns in the table. For details about how to modify a table, see ALTER TABLE.

ALTER TABLE table_name
    [ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ]
    [ [DEFAULT] COLLATE [ = ] default_collation ]

Currently, you cannot modify the default character set and collation of a table to the specified values, set the character set and collation of all columns with character type to the specified value, or convert the data in the column to new character set encoding. The syntax is as follows:

ALTER TABLE table_name
    CONVERT TO CHARACTER SET | CHARSET charset [ COLLATE collation ]

Parameters

• table_name

  Specifies the name of the table.

• default_charset

  Specifies the default character set of a table. If you specify a character set separately, the default collation of the table is set to the default collation of the specified character set.

• default_collation

  Specifies the default collation of a table. If you specify a collation separately, the default character set of the table is set to the character set corresponding to the specified collation.

GaussDB selects a character set and collation of a table in the following ways:

• If both default_charset and default_collation are set, the character set default_charset and collation default_collation are used. In addition, default_charset and default_collation must correspond to each other. Otherwise, an error is reported.

• If only default_charset is set, the character set default_charset and its default collation are used.

• If only default_collation is set, the collation default_collation and its corresponding character set are used.

• If neither default_charset nor default_collation is set, the default character set and collation of the schema where the table is located are used as the default character set and collation of the table.

• Only character sets with a default collation support default_charset. If the specified character set does not have a default collation, an error is reported.

• Only the collations in MySQL-compatible mode (sql_compatibility = 'MYSQL') support default_collation. If another collation is specified, an error is reported.

• If the default collation of a table is binary, the text types whose collation is not specified in the table are converted to the corresponding binary type, and the collation is set to binary.

• Currently, the default character set and collation of a table must be server_encoding of the database.
• Currently, the data of columns of the character type in the table cannot be converted into encoding.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Set only the character set. The collation is the default collation of the character set.
gaussdb=# CREATE TABLE test(c1 text) CHARSET utf8;

-- Set only the collation. The character set is the character set associated with the collation.
gaussdb=# CREATE TABLE test(c1 text) COLLATE utf8_bin;

-- Set both the character set and collation. The character set and collation must correspond to each other.
gaussdb=# CREATE TABLE test(c1 text)  CHARSET utf8 COLLATE utf8_bin;

-- Change the default character set of the table to utf8mb4 and the default collation to utf8mb4_bin.
gaussdb=# ALTER TABLE test CHARSET utf8mb4 COLLATE utf8mb4_bin;