两个不同的字符集不能有相同的排序规则。且每个字符集都有一个默认的排序规则。
本篇大部分内容出自官网文档…
查看当前支持的字符集
INFORMATION_SCHEMA.CHARACTER_SETS
INFORMATION_SCHEMA.CHARACTER_SETS 表中可以查看可用的字符集。
MAXLEN 列表示:The maximum number of bytes required to store one character.
MariaDB [(none)]> select * from INFORMATION_SCHEMA.CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
| latin1 | latin1_swedish_ci | cp1252 West European | 1 |
| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |
...
...
或者查看包含 utf8 的字符集,可以使用LIKE子句或WHERE子句。
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
-> WHERE CHARACTER_SET_NAME LIKE '%utf8%';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+
2 rows in set (0.000 sec)
SHOW CHARACTER SET
相对于
INFORMATION_SCHEMA.CHARACTER_SETS 表,SHOW CHARACTER SET 语句更常用于查看支持的字符集。
MariaDB [(none)]> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
...
或者查看包含 utf8 的字符集,可以使用LIKE子句或WHERE子句。
MariaDB [(none)]> SHOW CHARACTER SET LIKE '%utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.005 sec)
查看当前支持的排序规则Collation
一个给定的字符集至少有一个排序规则,大多数字符集有多个。
使用
INFORMATION_SCHEMA.COLLATIONS 表或 SHOW COLLATION 语句,可以显示一个字符集的排序规则。
SHOW COLLATION默认显示所有的排序规则,使用WHERE、LIKE等子句可以过滤显示指定字符集的排序规则。
查看默认字符集 utf8mb4 的排序规则。