| COERCIBILITY | Syntax: | COERCIBILITY(String) | | Return value: | INTEGER | | Function type: | Information function | |
|
|
The COERCIBILITY() function returns the coercibility value of the collation of the "String" argument.
Coercibility defines what is converted to what in the event of a collation conflict, converting a higher coercibility expression to the collation of a lesser coercibility expression.
| Coercibility | Description | Example |
| 0 | Explicit | Value using a COLLATE clause |
| 1 | No sorting | Concatenated strings with different collations |
| 2 | Implicit | Column value |
| 3 | Constant | USER() return value |
| 4 | Enforceable | Literal string |
| 5 | Low enforceability | Numbers |
| 6 | Ignorable | NULL or derived from NULL |
|
SQL Examples for the COERCIBILITY function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
| SELECT COERCIBILITY('Test');
SELECT COERCIBILITY(100);
SELECT COERCIBILITY(convert(100.234 USING utf8));
SELECT COERCIBILITY(convert(100 USING utf8));
SELECT COERCIBILITY(convert('Test' USING utf32));
SELECT COERCIBILITY(convert('Test', char));
SELECT COERCIBILITY(convert('Test', nchar)); /* To National Character Set */
SELECT COERCIBILITY(convert('Test', char CHARACTER SET utf8mb4));
SELECT COERCIBILITY(null);
|
|
| COERCIBILITY('Test') |
|---|
| int(10) |
|---|
| 4 |
|
|
| COERCIBILITY(100) |
|---|
| int(10) |
|---|
| 5 |
|
|
| COERCIBILITY(convert(100.234 USING utf8)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(convert(100 USING utf8)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(convert('Test' USING utf32)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(convert('Test', char)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(convert('Test', nchar)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(convert('Test', char CHARACTER SET utf8mb4)) |
|---|
| int(10) |
|---|
| 2 |
|
|
| COERCIBILITY(null) |
|---|
| int(10) |
|---|
| 6 |
|
|
|
| The examples were created with the MyWAY SQL manager: |
How to use the COERCIBILITY() function in MySQL and MariaDB databases |
In MySQL and MariaDB, the COERCIBILITY() function can be used to determine the collation coercivity of an expression or a determine column collation by returning an integer value representing the degree of constraint used to determine how operands in an expression should be compared and implicitly converted when their collations differ. The function takes a table column, a literal value, or any valid expression as an argument.
The coercivity values are assigned as follows:
0: constants and system functions = highest compulsiveness
1: collational coercibility of the result of the COALESCE function
2: Explicit collation enforceability using the COLLATE clause
3: Collation coercibility of a table column
4: Collation constraint capability of an expression
5: Implicit collation coercibility of string literals and numeric values = lowest coercibility
6: NULL values = are ignored
The COERCIBILITY() function is primarily used internally by the database engine to determine collation and implicit conversion behavior when processing complex queries, collation analysis, and solving collation problems. The practical use of the function in SQL queries is relatively limited, but can be useful when troubleshooting collation problems or handling complex queries with different collations and implicit conversions. |
|
Further MySQL und MariaDB SQL Information functions |
|
