Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ Character Sets and Collations/ Table-level Character Sets and Collations
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Table-level Character Sets and Collations

Set the default character set and default collation for a 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.

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

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, and convert the data in the column to new character set encoding.

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 B-compatible mode (sql_compatibility is set to 'B') 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 of a table must be server_encoding of the database.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
-- 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;

-- Convert the column data of the character type in the table to utf8mb4, and set the collation of the table and column to utf8mb4_bin.
gaussdb=# ALTER TABLE test CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_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;