All Products
Search
Document Center

Collations

Last Updated: Jun 18, 2021

Collations (COLLATE operators) allow you to specify a collation and reset the collation of the character type or specify the collation of a single expression.

COLLATE is a postfix unary operator. It has the same precedence as other unary operators, but it is executed after all the prefix unary operators are executed. The COLLATE operator can be applied to the expressions of the VARCHAR2, CHAR, LONG, NVARCHAR, or NCHAR type.

The collation_name parameter that follows the COLLATE operator is the collation that is to be specified or used. When the collation name includes spaces, you must enclose the name in double quotation marks ('').

Example:

obclient> create table t1(c1 varchar(20));
obclient> insert into t1 values('a');
obclient> insert into t1 values('b');
obclient> insert into t1 values(1);
obclient> select * from t1 order by c1 collate utf8mb4_general_ci;
+------+
| C1   |
+------+
| 1    |
| a    |
| b    |
+------+
3 rows in set (0.00 sec)

The following collations are supported:

obclient> show collation;
+--------------------+---------+-----+---------+----------+---------+
| COLLATION          | CHARSET | ID  | DEFAULT | COMPILED | SORTLEN |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |  46 | NULL    | Yes      |       1 |
| binary             | binary  |  63 | Yes     | Yes      |       1 |
| gbk_chinese_ci     | gbk     |  28 | Yes     | Yes      |       1 |
| gbk_bin            | gbk     |  87 | NULL    | Yes      |       1 |
| utf16_general_ci   | utf16   |  54 | Yes     | Yes      |       1 |
| utf16_bin          | utf16   |  55 | NULL    | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | NULL    | Yes      |       1 |
| utf16_unicode_ci   | utf16   | 101 | NULL    | Yes      |       1 |
| gb18030_chinese_ci | gb18030 | 248 | Yes     | Yes      |       1 |
| gb18030_bin        | gb18030 | 249 | NULL    | Yes      |       1 |
+--------------------+---------+-----+---------+----------+---------+
11 rows in set (0.01 sec)