Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Character Sets and Collations/ Column-level Character Sets and Collations
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Column-level Character Sets and Collations

You can set the character set and collation for each column of the string type (CHAR, VARCHAR, or TEXT).

CREATE TABLE table_name (
    column_name data_type
    [ {CHARACTER SET | CHAR SET | CHARSET} charset ]
    [ COLLATE collation ]);

Syntax description:

  • table_name

    Table name.

  • data_type

    Data type of a column. The value can be character set and collation syntax.

  • CHARACTER SET | CHAR SET | CHARSET charset

    Character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

  • COLLATE collation

    The COLLATE clause specifies the collation of a column (the data type of the column must support collation). If no collation is specified, the default collation is used.

Setting Method

M-compatible selects character sets and collations of a table column in the following ways:

  • If both charset and collation are specified, charset and collation are used. charset and collation must correspond to each other. Otherwise, an error is reported.

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

  • If only collation is specified, the character set associated with collation and the specified collation are used.

  • If neither charset nor collation is specified, the default character set and collation of the table are used.

  • 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 Supported Collations support default_collation. If another collation is specified, an error is reported.
  • If the collation of a column 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.
  • The character set of the partition key of the partitioned table must be the same as that of the database.
  • Except the database using SQL_ASCII, databases using other character sets support mixed use of multiple character sets.

Example:

1
2
3
4
5
6
7
8
 
-- Set only the character set. The collation is the default collation of the character set.
m_db=# CREATE TABLE test(c1 text CHARSET utf8);

-- Set only the collation. The character set is the character set associated with the collation.
m_db=# 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.
m_db=# CREATE TABLE test(c1 text CHARSET utf8 COLLATE utf8_bin);