How to detect the collation coercibility associated to a given character string using the COERCIBILITY() function?

COERCIBILITY(str) is a MySQL built-in function that returns the collation coercibility associated to a given character string.

A collation coercibility refers to the coercibility index, which is used in the collation conversion process when comparing character strings of mixed collations. The character string with higher collation coercibility is converted to the lower collation coercibility. For example:

SELECT COERCIBILITY('fyi'), COLLATION('fyi');
  -- +---------------------+------------------+
  -- | COERCIBILITY('fyi') | COLLATION('fyi') |
  -- +---------------------+------------------+
  -- |                   4 | utf8_unicode_ci  |
  -- +---------------------+------------------+

SELECT COERCIBILITY('FYI' COLLATE utf8_general_ci);
  -- +---------------------------------------------+
  -- | COERCIBILITY('FYI' COLLATE utf8_general_ci) |
  -- +---------------------------------------------+
  -- |                                           0 |
  -- +---------------------------------------------+

SELECT 'fyi' = 'FYI' COLLATE utf8_general_ci;
  -- +---------------------------------------+
  -- | 'fyi' = 'FYI' COLLATE utf8_general_ci |
  -- +---------------------------------------+
  -- |                                     1 |
  -- +---------------------------------------+

Reference information of the COERCIBILITY() function:

COERCIBILITY(str): idx
  Returns the collation coercibility associated to a given character string.

Arguments, return value and availability:
  str: Required. The character string to be examined.
  idx: Return value. The coercibility index. 
  Available since MySQL 4.0.

Coercibility index supported:
  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

Related MySQL functions:

• CHARSET() - Detecting Character Set Name
• COLLATION() - Detecting Character Collation Name
• COERCIBILITY() - Character Collation Coercibility

⇒ COLLATION() - Detecting Character Collation Name

⇐ CHARSET() - Detecting Character Set Name

⇑ MySQL Functions on Character String Values

⇑⇑ MySQL Function References