Each character expression has a character set and a collation.
Specify the character set and collation for a character expression
For simple statements such as SELECT 'string', the default character set and collation of the string expression are defined by the system variables character_set_connection and collation_connection.
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]
Examples:
obclient> SELECT _utf8mb4'abc' COLLATE utf8mb4_unicode_ci;
+------------------------------------------+
| _utf8mb4'abc' COLLATE utf8mb4_unicode_ci |
+------------------------------------------+
| abc |
+------------------------------------------+
1 row in set
When you specify the character set of a SQL constant, you can add the prefix _gb18030_2022 to a hexadecimal value to convert it to the corresponding character in the gb18030_2022 character set. Similarly, you can add the prefix _gb18030_2022 to a string to specify that the string uses the gb18030_2022 character set.
obclient> SELECT _gb18030_2022 0xCDE5 AS c FROM DUAL;
+-----+
| c |
+-----+
| |
+-----+
1 row in set
obclient> SELECT _gb18030_2022 'Bay' AS c FROM DUAL;
+-----+
| c |
+-----+
| |
+-----+
1 row in set
Character set and collation for character expressions
OceanBase Database chooses the character set and collation for character expressions in the following ways:
If both
_charset_nameandCOLLATE collation_nameare specified, the character setcharset_nameand the collationcollation_nameare used.If
_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 you omit the
_charset_nameparameter but specifyCOLLATE collation_name, the character set is determined by thecharacter_set_connectionsystem variable and the specifiedcollation collation_name. Thecollation_namemust be supported by the default character set.If neither
CHARACTER SETnorCOLLATEis specified, the default character set is specified by thecharacter_set_connectionsystem variable and thecollation collation_nameclause.