详解MySQL字符集和Collation-阿里云帮助中心

在使用MySQL的时候，是否经常遇到过字符串乱码的现象？想获取一个英文字符串结果却是特殊字符？字符串排序有时小写在前有时大写在前？如果在使用MySQL时不在意字符集、Collation等相关配置，那么就可能会碰到这些问题，本文将以几个例子作为开篇。

例1

一个客户端的错误配置，可能导致字符串二进制存储错乱，并且导致其他正常客户端查询出现乱码，如下：

-- 1.创建表t1，有两列，一列使用utf8mb4字符集，一列使用latin1字符集
mysql> create table t1 (a char(5) character set utf8mb4, b char(5) character set latin1);

-- 2.客户端实际使用utf8mb4字符集，这里模拟客户端在服务端错误配置，并插入数据
mysql> set names latin1;
-- 哪怕列b的latin1字符集没有字符'张'也能成功插入，正常情况下都无法插入该列，从这里就开始出错了
mysql> insert into t1 values('张','张');

-- 3.正常客户端查询数据
mysql> set names utf8mb4;
-- a列(utf8mb4字符集)内容完全错乱，b列(latin1字符集)内容竟是'张'的utf8mb4编码
mysql> select a,hex(a),b,hex(b) from t1;
+--------+--------------+--------+--------+
| a      | hex(a)       | b      | hex(b) |
+--------+--------------+--------+--------+
| å¼     | C3A5C2BCC2A0 | å¼     | E5BCA0 |
+--------+--------------+--------+--------+

例2

用等值条件查询字符串列时，多返回了一个完全不同的字符串，难道MySQL出Bug了？其实不是的，字符串的比较和Collation配置息息相关，稍不注意就可能得到令人费解的结果。

-- 1.创建表t1，包含一列，默认使用utf8mb4字符集和utf8mb4_0900_ai_ci Collation
mysql> create table t1 (a char(5));

-- 2.插入两行数据
mysql> insert into t1 values ('ß'),('ss');

-- 3.按等值条件查询该表，结果编码完全不同的两个字符串都返回了！
mysql> select a,hex(a) from t1 where a='ss';
+------+--------+
| a    | hex(a) |
+------+--------+
| ß    | C39F   |
| ss   | 7373   |
+------+--------+

-- 4.换一个Collation试试，又正常了
mysql> select a,hex(a) from t1 where a='ss' collate utf8mb4_0900_as_cs;
+------+--------+
| a    | hex(a) |
+------+--------+
| ss   | 7373   |
+------+--------+

例3

下面这个例子展现了Collation对于字符串排序的影响，怎么有时候是小写优先，有时候是大写优先，不同字符串的先后顺序是怎么确定的？这都是配置的Collation决定的。

-- 1.创建表t1，包含一列，默认使用utf8mb4字符集和utf8mb4_0900_ai_ci Collation
mysql> create table t1 (a char(5));

-- 2.插入两行数据
mysql> insert into t1 values ('abc'),('ABC');

-- 3.返回字符串排序结果，小写优先
mysql> select * from t1 order by a;
+------+
| a    |
+------+
| abc  |
| ABC  |
+------+

-- 4.换一个Collation，成大写优先了
mysql> select * from t1 order by a collate utf8mb4_0900_bin;
+------+
| a    |
+------+
| ABC  |
| abc  |
+------+

通过上述3个例子可以看出，我们在MySQL中使用字符串时，字符集和Collation是非常重要的配置项，一旦配置错误就可能无法按照预期进行使用。当我们在使用MySQL查看表定义时，经常可以看到如下例所示的CHARSET、COLLATE等字眼，所以相信大家对于这些字眼并不陌生。然而但很多时候我们并不清楚这些字符集、Collation的具体含义，也不知道究竟如何配置才好，要么跟着默认配置走，要么从已有库表的定义那里copy过来，但是这些“祖传配置”真的适合当前的应用吗？合理地选择字符集、Collation、了解字符串如何比较将能很大程度上帮我们避免前文例子中所描述的问题，因此本文就将对此展开介绍，希望能对你有帮助。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `b` varchar(10) CHARACTER SET latin2 COLLATE latin2_general_ci DEFAULT NULL,
  `c` char(10) COLLATE utf8mb4_0900_as_cs DEFAULT NULL,
  `d` char(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `e` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs

MySQL本身支持很多种字符集（Character Set）与校对规则（Collation），本身的配置有时会使用户眼花缭乱，后续章节将围绕它们由浅入深进行介绍，希望能够解答以下几个问题：

字符集（Charset）和校对规则（Collation）是什么？
如何配置不同层次的字符集和校对规则？它们的影响范围有多大？
最常用的Unicode字符集是如何存储、比较字符串的？
Binary字符集和非二进制字符集的_bin Collation该如何选择？