Character expression character sets and collations

Each character expression has a character set and a character order.

Specify the character set and collation of the character expression

For a simple statement SELECT 'string', the character set and collation for the string are determined by the character_set_connection and collation_connection system variables.

The COLLATE clause can be used to specify the character set and collation of a character expression. The syntax is as follows:

[_charset_name]'string' [COLLATE collation_name]

For example:

obclient> SELECT _utf8mb4'abc' COLLATE utf8mb4_unicode_ci;
+------------------------------------------+
| _utf8mb4'abc' COLLATE utf8mb4_unicode_ci |
+------------------------------------------+
| abc                                      |
+------------------------------------------+
1 row in set

The gb18030_2022 character set is supported. For more information, see the following statements: - To convert a hexadecimal number to the corresponding character in the gb18030_2022 character set, add the _gb18030_2022 prefix before the hexadecimal number. - To specify a string in the gb18030_2022 character set, add the _gb18030_2022 prefix before the string.

obclient> SELECT _gb18030_2022 0xCDE5 AS c FROM DUAL;
+-----+
| c   |
+-----+
| Bay |
+-----+
1 row in set

obclient> SELECT _gb18030_2022 'bay' AS c FROM DUAL;
+-----+
| c   |
+-----+
| Bay |
+-----+
1 row in set

Choose the character set and collation of a character expression

OceanBase Database determines the character set and collation for a character expression in the following ways:

• If both the _charset_name option and the COLLATE collation_name clause are specified, the character set charset_name and the collation collation_name are used.

• If only CHARSET is specified without COLLATE, 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 _charset_name is not specified but COLLATE collation_name is, the default character set is specified by the character_set_connection system variable and the COLLATE collation_name clause. collation_name must be a collation supported by the default character set.

• If neither CHARACTER SET nor COLLATE are specified, the default character set is determined by the character_set_connection system variable and collation collation_name.