Each "character" column (i.e., a column of type CHAR, VARCHAR, TEXT, or any synonym) has a column character set and a column collation.
Specify the character set and collation of a column
The CREATE TABLE and ALTER TABLE statements support specifying the character set and collation for a column, with the syntax as follows:
CREATE TABLE table_name (
column_name {CHAR | VARCHAR | TEXT} (column_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]
);
ALTER TABLE table_name MODIFY
col_name {CHAR | VARCHAR | TEXT} (column_length)
CHARACTER SET [=] charset_name
[COLLATE [=] collation_name]
An example is as follows:
obclient> CREATE TABLE t (
col1 VARCHAR(5)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci);
Query OK, 0 rows affected
obclient> ALTER TABLE t MODIFY
col1 VARCHAR(5)
CHARACTER SET gbk
COLLATE gbk_chinese_ci;
Query OK, 0 rows affected
Select the character set and collation for a column
OceanBase AI Database determines the character set and collation of a column 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 character set and collation of the table 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.
In addition, after setting the COLLATE for a column in OceanBase AI Database, you can directly use ORDER BY to sort data according to the specified collation. You can also use COLLATE after ORDER BY to set different collations for the same character set.
obclient> CREATE TABLE t(a VARCHAR(10) COLLATE gb18030_2022_radical_ci);
Query OK, 0 rows affected
obclient> INSERT INTO t VALUES('a'),('A'),('b'),('B');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT a FROM t ORDER BY a;
+------+
| a |
+------+
| a |
| A |
| b |
| B |
+------+
4 rows in set
obclient> SELECT a FROM t ORDER BY a COLLATE gb18030_2022_chinese_cs;
+------+
| a |
+------+
| A |
| B |
| a |
| b |
+------+
4 rows in set
Column charset conversion conditions
To convert a binary or non-binary string column to a specified character set, you can use the ALTER TABLE statement. A successful conversion requires meeting one of the following conditions:
If a column has a binary data type (
BINARY,VARBINARY, orBLOB), all values it contains must be encoded using a single character set (the character set to which the column is to be converted). If you use a binary column to store information in multiple character sets, OceanBase AI Database cannot determine which character set is used for which value and cannot convert the data correctly.If a column has a non-binary data type (
CHAR,VARCHAR, orTEXT), its content must be encoded according to the column's character set. If the column content is encoded in a different character set, you can first convert the column to a binary data type, and then convert it to a non-binary column with the desired character set.