Declaration
COERCIBILITY(str)
Description
Returns the collation priority of the string str for multilingual sorting.
When comparing two variables with the same character set, collation is used as the rule. However, when comparing two variables with different character sets, you need to specify the priority between them. For example, consider the following statements:
obclient> SELECT c1 FROM t WHERE c1 = 'Y';
If c1 and 'Y' use the same collation, no ambiguity arises. However, if table t specifies a different collation in table_option, or if the system variable collation_connection uses a different collation, the collation used for comparison depends on their priority.
The priorities of various collations are shown in the following table. A smaller value indicates a higher priority.
Value |
Description |
Example |
|---|---|---|
| 0 | Explicitly specified collation | explicit COLLATE clause. |
| 1 | No Collation | Concatenation of strings with different collations. |
| 2 | Implicitly Specified Collation | Column values, system parameters, or variables. |
| 3 | System constants | System functions, such as the return value of the USER() function. |
| 4 | Degradable Value | A text string. |
| 5 | Numeric values | Numeric or time value. |
| 6 | Ignorable value | NULL or the result is NULL the expression. |
OceanBase AI Database uses Coercibility values and the following rules to resolve ambiguity:
A smaller Coercibility value indicates a higher priority.
If the two sides being compared have sorting rules with the same Coercibility, the following supplementary rules are used:
If both character sets are Unicode or neither is Unicode, an error is returned if their collations are different.
If one character set uses Unicode and the other does not, the Unicode character set has higher priority. For example, the following statement will not return an error:
obclient> SELECT CONCAT(t1.utf8_column, t2.gbk_column) FROM t1,t2;For character sets that are the same but have collations
_binanda_cior_cs, the_bincollation is used.
Examples
obclient> SELECT COERCIBILITY('oceanbase' COLLATE utf8mb4_bin);
+-----------------------------------------------+
| COERCIBILITY('oceanbase' COLLATE utf8mb4_bin) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
1 row in set
obclient> SELECT COERCIBILITY('oceanbase');
+---------------------------+
| COERCIBILITY('oceanbase') |
+---------------------------+
| 4 |
+---------------------------+
1 row in set
obclient> SELECT COERCIBILITY(user());
+----------------------+
| COERCIBILITY(user()) |
+----------------------+
| 3 |
+----------------------+
1 row in set
obclient> SELECT COERCIBILITY(1000);
+--------------------+
| COERCIBILITY(1000) |
+--------------------+
| 5 |
+--------------------+
1 row in set