COERCIBILITY

Syntax

COERCIBILITY(str)

Description

Returns the collation priority of the string str in multibyte sorting.

When comparing two variables of the same character set, the collation order is used as the rule. However, when comparing two variables of different character sets, the priority of the different character sets must be specified. For example, in the following statement:

code-placeholder-dbcc857f-2ef0-49c4-ad85-1add54708c33

If c1 and 'Y' use the same collation, there is no ambiguity. However, if the table t specifies a different collation in table_option, or if the system variable collation_connection uses a different collation, the collation used during comparison depends on the priority of the two collations.

The priority of various collations is shown in the following table, with smaller values indicating higher priority.

Value	Description	Examples
0	Explicitly specified collation	An explicit COLLATE clause.
1	No collation	Concatenation of strings with different collations.
2	Implicitly specified collation	Column values, system parameters, or variables.
3	System constant	Return values of system functions, such as the USER() function.
4	Downgradable value	Literal strings.
5	Numeric value	Numeric or time values.
6	Ignorable value	NULL or expressions that evaluate to NULL.

seekdb uses the coercibility values and the following rules to resolve ambiguities:

• A smaller coercibility value indicates a higher priority.

• If the collations of the two operands have the same coercibility value, the following rules apply:

  • If both character sets are Unicode or neither is Unicode, and the collations are different, an error is returned.

  • If one character set is Unicode and the other is not, the Unicode character set has a higher priority. For example, the following statement does not return an error:

    SELECT CONCAT(t1.utf8_column, t2.gbk_column) FROM t1,t2;

  • For the same character set, if the collations are _bin and a_ci or _cs, the _bin collation is used.

Examples

SELECT COERCIBILITY('oceanbase' COLLATE utf8mb4_bin);
+-----------------------------------------------+
| COERCIBILITY('oceanbase' COLLATE utf8mb4_bin) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.001 sec)

SELECT COERCIBILITY('oceanbase');
+---------------------------+
| COERCIBILITY('oceanbase') |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.001 sec)

SELECT COERCIBILITY(user());
+----------------------+
| COERCIBILITY(user()) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.001 sec)

SELECT COERCIBILITY(1000);
+--------------------+
| COERCIBILITY(1000) |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.001 sec)