全网最详细 | MySQL中字符集和排序规则的详细介绍，为什么中文等字符编码(Unicode)下一定要使用utf8mb4？-优快云博客

本文详细介绍了MySQL中字符集和排序规则，特别是utf8与utf8mb4的区别。utf8mb4是真正支持Unicode的编码，能存储包括中文、emoji在内的4字节字符。推荐在需要处理这些字符时使用utf8mb4。文章还讨论了utf8mb4_bin排序规则，以及如何在创建数据库、表、列时指定字符集和排序规则，并提供了修改已有对象字符集的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

两个不同的字符集不能有相同的排序规则。且每个字符集都有一个默认的排序规则。

本篇大部分内容出自官网文档…

查看当前支持的字符集

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)