In MySQL, the COERCIBILITY() returns the collation coercibility value of its string argument.

The COERCIBILITY() function defines how collations will be converted in the case of collation conflict. In such conflicts, an expression with a higher coercibility is converted to the collation of an expression with a lower coercibility.

Syntax

The syntax goes like this:

COERCIBILITY(str)

Where str is the expression that you’d like to get the coercibility for.

The COERCIBILITY() function returns a value between 0 and 6, as outlined in the following table:

Coercibility	Meaning	Example
0	Explicit collation	Value with COLLATE clause
1	No collation	Concatenation of strings with different collations
2	Implicit collation	Column value, stored routine parameter or local variable
3	System constant	USER() return value
4	Coercible	Literal string
5	Numeric	Numeric or temporal value
6	Ignorable	NULL or an expression derived from NULL

Example

Here’s an example to demonstrate:

SELECT COERCIBILITY('Cat');

Result:

+---------------------+
| COERCIBILITY('Cat') |
+---------------------+
|                   4 |
+---------------------+

This is a normal string and so the coercibility is 4.

Explicit Collation

Here’s an example of explicitly specifying the collation with the COLLATE clause.:

SELECT COERCIBILITY('Cat' COLLATE utf8mb4_slovak_ci);

Result:

+-----------------------------------------------+
| COERCIBILITY('Cat' COLLATE utf8mb4_slovak_ci) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

This returns 0 because I explicitly specified the collation with the COLLATE clause.

Database Column

In this example, I return data from a database column.:

SELECT 
    PetName,
    COERCIBILITY(PetName)
FROM Pets
ORDER BY PetName
LIMIT 1;

Result:

+---------+-----------------------+
| PetName | COERCIBILITY(PetName) |
+---------+-----------------------+
| Fetch   |                     2 |
+---------+-----------------------+

The fact that it’s a database column means that it’s an implicit collation, and therefore we get a coercibility of 2.

Constants

In this example, I get the coercibility of the return value from the inbuilt VERSION() function:

SELECT COERCIBILITY(VERSION());

Result:

+-------------------------+
| COERCIBILITY(VERSION()) |
+-------------------------+
|                       3 |
+-------------------------+

Numbers

Numeric values return 5:

SELECT COERCIBILITY(256);

Result:

+-------------------+
| COERCIBILITY(256) |
+-------------------+
|                 5 |
+-------------------+

Null Values

Here’s what happens when we pass null:

SELECT COERCIBILITY(null);

Result:

+--------------------+
| COERCIBILITY(null) |
+--------------------+
|                  6 |
+--------------------+

Null values are ignorable, and so we get a coercibility of 6.

No Arguments

Calling COERCIBILITY() without any arguments results in an error:

SELECT COERCIBILITY();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'COERCIBILITY'