MySQL的编码问题

[qingquan-li]

参考：

• infoq.cn - 记住，永远不要在 MySQL 中使用“utf8”
• dbalife.info - MySQL8默认字符集utf8mb4
• 腾讯云+社区 - MYSQL中的COLLATE是什么？

一、MySQL 的 utf8、utf8mb4 与 UTF-8

MySQL 中 utf8 字符集使用三个字节编码一个字符，自2004（ mysql4.1 ）年被引入，能够支持绝大多数语言，但依然有些字符不能正确编码，如表情字符。 MySQL 的 utf8 只支持每个字符最多三个字节，是一种“专属的编码”，它能够编码的 Unicode 字符并不多。 而真正的 UTF-8 是每个字符最多四个字节。

为此 mysql5.5 引入了 utf8mb4 字符集，提供了另一种选择。在 mysql5.7 对 utf8mb4 进行了大幅优化，并丰富了校验字符集。 MySQL 的 utf8mb4 是真正的 UTF-8 。 所有在使用 utf8 的 MySQL 和 MariaDB 用户都应该改用 utf8mb4 ，永远都不要再使用 utf8 。

直到 mysql8 ， utf8mb4 成为了默认字符集，并极大地扩充了 collation set 。 增加了 accent sensitive(as) ， accent_insensitive(ai) （重读敏感，对于某些语言需要重读的字符可以是用专用字符标识，如：ã ），可以支持更加丰富的字符校验。

连接 MySQL 后，输入 STATUS; 可以查看 MySQL 的状态：

连接 MySQL 8 后，通过 mysql> STATUS; 可以查询到默认的 characterset 字符集都为 utf8mb4 。

mysql> STATUS;
--------------
mysql  Ver 8.0.16 for macos10.14 on x86_64 (MySQL Community Server - GPL)

Connection id:		9
Current database:	scraping
Current user:		root@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.16 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		6
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql.sock
Uptime:			6 days 5 hours 18 min 3 sec

Threads: 2  Questions: 231  Slow queries: 0  Opens: 174  Flush tables: 3  Open tables: 80  Queries per second avg: 0.000
--------------

二、utf8mb4 和 utf8mb4_unicode_ci

连接 MySQL 5.7 后，通过 mysql> STATUS; 可以查询到默认的 characterset 字符集为 latin1 和 utf8 。

mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.7.28, for macos10.14 (x86_64) using  EditLine wrapper

Connection id:		183
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		5.7.28 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			6 days 57 min 55 sec

Threads: 3  Questions: 8580  Slow queries: 0  Opens: 557  Flush tables: 1  Open tables: 532  Queries per second avg: 0.016
--------------

# 查看当前 MySQL 版本默认的字符集：
mysql> SHOW VARIABLES LIKE 'character_set%';

# 查看当前 MySQL 版本默认的校对：
mysql> SHOW VARIABLES LIKE 'collation%';

# 查看当前 MySQL 版本所支持的字符集完整列表：
mysql> SHOW CHARACTER SET;

# 查看当前 MySQL 版本所支持的校对的完整列表：
mysql> SHOW COLLATION;

如果数据库（表）默认的 CHARACTER SET 为 utf8 ，utf8 严格来说也属于 Unicode，但是对大多数 Unicode 字符的支持都非常不好，例如不支持 emoji 等。

这时候需要将 MySQL 的字符集 CHARACTER SET 编码从 utf8 转变成 utf8mb4 ，校对顺序 COLLATE 可以设置为 utf8mb4_unicode_ci （MySQL 5.7 字符集 utf8mb4 默认的校对为 utf8mb4_general_ci ）。

注意： COLLATE 的作用主要应用于：排序和查询。一般情况下，使用 utf8mb4_general_ci 和 utf8mb4_unicode_ci 区别不大： utf8mb4_general_ci 的性能略快（但基本可以忽略）， utf8mb4_unicode_ci 对中文字符支持会更好一些。 其中， ci is for case-insensitive（不区分大小写） sorting and comparison. 相对的是 cs： case-sensitive 。

ALTER DATABASE 数据库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE 表名 CHANGE 字段1（列名） 字段1（新列名） 新数据类型（例如：VARCHAR(200)） CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE 表名 CHANGE 字段1（列名） 字段1（新列名） 新数据类型（例如：VARCHAR(200)） CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

以上操作，把数据库、数据表，以及两个字段的默认编码都从 utf8mb4 转变成了 utf8mb4_unicode_ci 。 ALTER 修改语法参考：https://github.com/FatliTalk/blog/issues/37 。