Character sets and collations for character expressions

Every character expression has a character set and a collation.

Specify the character set and collation for a character expression

The default character set and collation for the character expression in a simple statement such as SELECT 'string' are defined by the character_set_connection and collation_connection system variables.

You can use the COLLATE clause to specify the character set and collation for a character expression, with the following syntax:

[_charset_name]'string' [COLLATE collation_name]

Example:

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

You can prepend the hexadecimal value with the _gb18030_2022 prefix in SQL statements to convert the value to the corresponding character based on the gb18030_2022 encoding. You can also prepend the string with the _gb18030_2022 prefix in SQL statements to specify that the string is based on the gb18030_2022 encoding.

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

Character set and collation of character expressions

OceanBase Database selects the character set and collation for a character expression as follows:

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

• If _charset_name is specified but COLLATE is not specified, the character set charset_name and its default collation are used. Use the SHOW CHARACTER SET statement to see the default collation for each character set.

• If _charset_name is not specified but COLLATE collation_name is specified, the default character set is determined by the value of the character_set_connection system variable and the collation specified by collation_name. collation_name must be a collation supported by the default character set.

• If you do not specify either CHARACTER SET or COLLATE, the default character set is determined by the character_set_connection system variable and collation collation_name.