Each character expression has a character set and collation.
Specify the character set and collation for a character expression
For the simple statement SELECT 'string', the default character set and collation of this string expression are determined 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 following syntax:
[_charset_name]'string' [COLLATE collation_name]
As follows:
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 _gb18030_2022 before a hexadecimal number or string to indicate that the number or string should be interpreted as a character under the gb18030_2022 character set.
obclient> SELECT _gb18030_2022 0xCDE5 AS c FROM DUAL;
+-----+
| c |
+-----+
| Cove |
+-----+
1 row in set
obclient> SELECT _gb18030_2022 'Bay' AS c FROM DUAL;
+-----+
| c |
+-----+
| |
+-----+
1 row in set
The character set and collation of a character expression
The character set and collation of an expression in the OceanBase Database are determined as follows:
If you specify both
_charset_nameandCOLLATE collation_name, the specified character setcharset_nameand collationcollation_nameare used.If
_charset_nameis specified butCOLLATEis not specified, the character setcharset_nameand its default collation are used. For information about the default collation for each character set, see theSHOW CHARACTER SETstatement.If you specify
COLLATE collation_namewithout_charset_name, then MySQL uses the default character set specified by thecharacter_set_connectionsystem variable and bycollation collation_name.collation_namemust be a collation supported by the default character set.If neither
CHARACTER SETnorCOLLATEare specified, the default character set is determined by thecharacter_set_connectionsystem variable and thecollation collation_nameclause.