MySQL COERCIBILITY() Function

The MySQL COERCIBILITY() function returns the mandatory value of the collation of the specified parameter.

Mandatory index values ​​for MySQL collations specify whose rules are used when comparing two values. MySQL provides mandatory values ​​for 7 collations, from 0 to 6, lower values have higher precedence.

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 for example the return value of USER(), VERSION()
4 Mandatory literal string
5 numerical numeric or temporary value
5 ignorable NULL or an expression derived from NULL

COERCIBILITY() Syntax

Here is the syntax of the MySQL COERCIBILITY() function:

COERCIBILITY(val)

Parameters

val

Required. A Text value.

Return value

The COERCIBILITY() function returns the mandatory value of the collation of the specified parameter.

COERCIBILITY() Examples

The following example shows how to use the COERCIBILITY() function to get the collation enforcement value for a specified parameter.

SET @val = '1';
SELECT
    COERCIBILITY('a' COLLATE utf8mb4_0900_ai_ci),
    COERCIBILITY(@val),
    COERCIBILITY(USER()),
    COERCIBILITY('a'),
    COERCIBILITY(1),
    COERCIBILITY(NULL)\G

*************************** 1\. row ***************************
COERCIBILITY('a' COLLATE utf8mb4_0900_ai_ci): 0
                          COERCIBILITY(@val): 2
                        COERCIBILITY(USER()): 3
                           COERCIBILITY('a'): 4
                             COERCIBILITY(1): 5
                          COERCIBILITY(NULL): 6