Every table has a default character set and a default collation.
Specify the character set and collation of a table
The CREATE TABLE and ALTER TABLE statements support specifying 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 modifying 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;
An example is as follows:
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 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 of a table
OceanBase AI Database determines the character set and collation of a table in the following manner:
If both
CHARACTER SET charset_nameandCOLLATE collation_nameare specified, the character setcharset_nameand collationcollation_nameare used.If
CHARACTER SET charset_nameis specified butCOLLATEis not, the character setcharset_nameand its default collation are used. To view the default collation for each character set, use theSHOW CHARACTER SETstatement.If
COLLATE collation_nameis specified butCHARACTER SETis not, the character set associated withcollation_nameand the specified collation are used.If neither
CHARACTER SETnorCOLLATEis specified, the database's character set and collation are used.
If the character set and collation for individual columns are not specified in their definitions, the table's character set and collation will be used as the default values for those column definitions.