Table-level character set and collation

Each table has a default character set and a default collation.

Specify the character set and collation for a table

The CREATE TABLE and ALTER TABLE statements can be used to specify the character set and collation for a table. The syntax is as follows:

CREATE TABLE table_name (column_list)
    [[DEFAULT] CHARACTER SET [=] charset_name]
    [COLLATE [=] collation_name]

ALTER TABLE table_name
    [DEFAULT] CHARACTER SET [=] charset_name
    [COLLATE [=] collation_name]

The syntax for changing the character set and collation of existing data in a table is as follows:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name COLLATE col_name;

Here is an example:

obclient> CREATE TABLE test_collation (c1 INT, c2 VARCHAR(32), c3 VARCHAR(32), PRIMARY KEY (c1),UNIQUE KEY idx_test_collation_c2(c2));
Query OK, 0 rows affected

// Change the character set and collation of the table.
obclient> ALTER TABLE test_collation CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
Query OK, 0 rows affected

// Change the character set and collation of the existing data in the table and update the corresponding settings.
obclient> ALTER TABLE test_collation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected

Select the character set and collation for a table

OceanBase Database selects the character set and collation for a table as follows:

• If both CHARACTER SET charset_name and COLLATE collation_name are specified, the character set charset_name and collation collation_name are used.

• If CHARACTER SET charset_name is specified but COLLATE is not, the character set charset_name and its default collation are used. To view the default collation for each character set, use the SHOW CHARACTER SET statement.

• If COLLATE collation_name is specified but CHARACTER SET is not, the character set associated with collation_name and the specified collation are used.

• If neither CHARACTER SET nor COLLATE is specified, the character set and collation of the database are used.

If the character set and collation are not specified for each column in the column definitions, the character set and collation of the table are used as the default values for the column definitions.