Every character expression has a character set and a collation.
Specify the character set and collation of a character expression
For a simple statement such as SELECT 'string', the default character set and collation of this character 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 for a character expression, with the syntax as follows:
[_charset_name]'string' [COLLATE collation_name]
Here is an example:
obclient> SELECT _utf8mb4'abc' COLLATE utf8mb4_unicode_ci;
+------------------------------------------+
| _utf8mb4'abc' COLLATE utf8mb4_unicode_ci |
+------------------------------------------+
| abc |
+------------------------------------------+
1 row in set
When specifying the character set for a constant in an SQL statement, for gb18030_2022, prefixing the hexadecimal number with _gb18030_2022 converts it to the corresponding character under gb18030_2022. Prefixing a string with _gb18030_2022 specifies its character set as gb18030_2022.
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
Select the character set and collation for a character expression
OceanBase AI Database determines the character set and collation of a character expression in the following manner:
If both
_charset_nameandCOLLATE collation_nameare specified, the character setcharset_nameand 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
_charset_nameis not specified butCOLLATE collation_nameis specified, the default character set specified by thecharacter_set_connectionsystem variable andcollation collation_nameis used.collation_namemust be a collation supported by the default character set.If neither
CHARACTER SETnorCOLLATEis specified, the default character set specified by thecharacter_set_connectionsystem variable andcollation collation_nameis used.