Information Schema COLLATIONS Table
Contents
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, in MariaDB 10.6, the following two statements return the same results:
SHOW COLLATION WHERE Charset LIKE 'utf8mb3';
and
SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME LIKE 'utf8mb3';
In MariaDB 10.5 and before, utf8
should be specified instead of utf8mb3
.
NO PAD collations
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
- 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.
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.