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

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-collations-table/