Table-level character sets and collations

Every table in OceanBase Database is created with a default character set and collation.

Specify the character set and collation of a table

You can execute the CREATE TABLE or ALTER TABLE statement to specify the character set and collation of a table by using the following syntax:

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]

You can modify the character set and collation of existing data in a table by using the following syntax:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name COLLATE col_name;

Here are some examples:

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

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

// Modify the character set and collation of existing data in a table, and modify 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 of a table

You can select the character set and collation of a table by using the following methods in OceanBase Database:

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

• If you specify only the CHARACTER SET charset_name parameter, the specified charset_name character set and the associated default collation are used. To view the default collation for each character set, execute the SHOW CHARACTER SET statement.

• If you specify only the COLLATE collation_name parameter, the specified collation_name collation and the associated character set are used.

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

If the column-level character set and collation are not specified in the definition of each column, the table-level character set and collation are used by default.