How to detect the character collation name associated to a given character string using the COLLATION() function?

COLLATION(str) is a MySQL built-in function that returns the character collation name associated to a given character string. A character collation name refers to a set of rules to sort a set of characters. For example:

SELECT COLLATION('FYI');
  -- +------------------+
  -- | COLLATION('FYI') |
  -- +------------------+
  -- | utf8_unicode_ci  |
  -- +------------------+

SELECT COLLATION(_latin1'FYI');
  -- +-------------------------+
  -- | COLLATION(_latin1'FYI') |
  -- +-------------------------+
  -- | latin1_swedish_ci       |
  -- +-------------------------+

CREATE TABLE MyTable (comment CHAR(80) 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);

INSERT INTO MyTable (comment) VALUES ('I like it!'); 
INSERT INTO MyTable (comment) VALUES ('Good job1'); 

SELECT comment, COLLATION(comment) FROM MyTable;
  -- +------------+--------------------+
  -- | comment    | COLLATION(comment) |
  -- +------------+--------------------+
  -- | I like it! | utf8mb4_general_ci |
  -- | Good job1  | utf8mb4_general_ci |
  -- +------------+--------------------+

Note that the character collation name has a direct impact on character string comparison operations. For example:

SET @str1 = 'fyicenter.com', @str2 = 'FYIcenter.com';

SELECT @str1=@str2, COLLATION('@str1'), COLLATION('@str2');
  -- +-------------+--------------------+--------------------+
  -- | @str1=@str2 | COLLATION('@str1') | COLLATION('@str2') |
  -- +-------------+--------------------+--------------------+
  -- |           1 | utf8_unicode_ci    | utf8_unicode_ci    |
  -- +-------------+--------------------+--------------------+

SET @str1 = _latin1'fyicenter.com' COLLATE latin1_general_cs;
SET @str2 = _latin1'FYIcenter.com' COLLATE latin1_general_cs;

SELECT @str1=@str2, COLLATION('@str1'), COLLATION('@str2');
  -- +-------------+--------------------+--------------------+
  -- | @str1=@str2 | COLLATION('@str1') | COLLATION('@str2') |
  -- +-------------+--------------------+--------------------+
  -- |           0 | utf8_unicode_ci    | utf8_unicode_ci    |
  -- +-------------+--------------------+--------------------+

Reference information of the COLLATION() function:

COLLATION(str): name
  Returns the character collation name associated to a given character string.

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

Related MySQL functions:

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

⇒ CONCAT() - Concatenating Strings

⇐ COERCIBILITY() - Character Collation Coercibility

⇑ MySQL Functions on Character String Values

⇑⇑ MySQL Function References