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 allow you 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 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

Choose the character set and collation for a table

OceanBase Database selects the character set and collation for a table based on the following rules:

• 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 individual columns, the character set and collation of the table are used as the default values for the columns.