Information Schema COLLATIONS Table

The Information Schema COLLATIONS table contains a list of supported collations, indicating their associated character sets and compilation status.

The Information Schema COLLATIONS table contains a list of supported collations.

It contains the following columns:

Column	Description
COLLATION_NAME	Name of the collation.
CHARACTER_SET_NAME	Associated character set.
ID	Collation id.
IS_DEFAULT	Whether the collation is the character set's default.
IS_COMPILED	Whether the collation is compiled into the server.
SORTLEN	Sort length, used for determining the memory used to sort strings in this collation.
PAD_ATTRIBUTE	Determines whether or not trailing spaces are regarded as normal characters. See below. Available from MariaDB 12.1.
COMMENT	For utf8mb4_0900 collations, contains the corresponding alias collation. From MariaDB 11.4.5, MariaDB 11.7.2, MariaDB 11.8.1.

The SHOW COLLATION statement returns the same results and both can be reduced in a similar way.

Current

The following two statements return the same results:

SHOW COLLATION WHERE Charset LIKE 'utf8mb3';

SELECT * FROM information_schema.COLLATIONS 
WHERE CHARACTER_SET_NAME LIKE 'utf8mb3';

< 10.5

NO PAD Collations

NO PAD collations regard trailing spaces as normal characters. You can get a list of all NO PAD collations as follows:

Current

SELECT collation_name FROM information_schema.COLLATIONS
WHERE pad_attribute = "NO PAD";  
+------------------------------+
| collation_name               |
+------------------------------+
| big5_chinese_nopad_ci        |
| big5_nopad_bin               |
...

< 12.1

In comparisons, NO PAD collations evaluate to 0 (FALSE). Example:

SELECT 'a ' = 'a'; 
+------------+ 
| 'a ' = 'a' | 
+------------+
|          0 | 
+------------+

PAD SPACE Collations

PAD SPACE collations pad strings to equal lengths in comparisons, so that the comparison evaluates to 1 (TRUE). Example:

SELECT 'a ' = 'a'; 
+------------+ 
| 'a ' = 'a' | 
+------------+
|          1 | 
+------------+

Example

SELECT * FROM information_schema.COLLATIONS;
+------------------------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME               | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------------+--------------------+------+------------+-------------+---------+
| big5_chinese_ci              | big5               |    1 | Yes        | Yes         |       1 |
| big5_bin                     | big5               |   84 |            | Yes         |       1 |
| big5_chinese_nopad_ci        | big5               | 1025 |            | Yes         |       1 |
| big5_nopad_bin               | big5               | 1108 |            | Yes         |       1 |
| dec8_swedish_ci              | dec8               |    3 | Yes        | Yes         |       1 |
| dec8_bin                     | dec8               |   69 |            | Yes         |       1 |
| dec8_swedish_nopad_ci        | dec8               | 1027 |            | Yes         |       1 |
| dec8_nopad_bin               | dec8               | 1093 |            | Yes         |       1 |
| cp850_general_ci             | cp850              |    4 | Yes        | Yes         |       1 |
| cp850_bin                    | cp850              |   80 |            | Yes         |       1 |
...

See Also

• Setting Character Sets and Collations - specifying the character set at the server, database, table and column levels

• Supported Character Sets and Collations - full list of supported characters sets and collations.

_{This page is licensed: CC BY-SA / Gnu FDL}