W3cubDocs

/MariaDB

Information Schema COLLATIONS Table

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.

The SHOW COLLATION statement returns the same results and both can be reduced in a similar way. For example, the following two statements return the same results:

SHOW COLLATION WHERE Charset LIKE 'utf8';

and

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

NO PAD collations

MariaDB starting with 10.2

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

SELECT collation_name FROM information_schema.COLLATIONS
WHERE collation_name LIKE "%nopad%";  
+------------------------------+
| collation_name               |
+------------------------------+
| big5_chinese_nopad_ci        |
| big5_nopad_bin               |
...

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 |
...

Information Schema COLLATIONS Table

NO PAD collations

Example

See Also