mysql

MySQL 修改字符集为utf8mb4

Posted by Sunday on 2018-02-28

一般情况下，我们会设置MySQL默认的字符编码为utf8，但是近些年来，emoji表情的火爆使用，给数据库带来了意外的错误，就是emoji的字符集已经超出了utf8的编码范畴😄

emoji是Unicode编码，在MySQL中使用utf8编码无法正常显示emoji的表情，为了解决这个问题，MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。

1. 什么是utf8mb4

但抛开数据库，标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符，这几乎包含了是世界上所有能看见的语言了。然而在MySQL里实现的utf8最长使用3个字节，也就是只支持到了 Unicode 中的 基本多文本平面）U+0000至U+FFFF，包含了控制符、拉丁文，中、日、韩等绝大多数国际字符，但并不是所有，最常见的就算现在手机端常用的表情字符 emoji和一些不常用的汉字，如 “墅” ，这些需要四个字节才能编码出来。

注：QQ里面的内置的表情不算，它是通过特殊映射到的一个gif图片。一般输入法自带的就是。

这两个归类都是用于UTF-8字符编码。差异在于如何对文本进行排序和比较。
注意：自从MySQL 5.5.3你应该使用utf8mb4而不是utf8。它们都指的是UTF-8编码，但是较旧的utf8有一个MySQL特有的限制，防止使用高于0xFFFD的字符。

2. utf8mb4_unicode_ci 与 utf8mb4_general_ci 如何选择

字符除了需要存储，还需要排序或比较大小，涉及到与编码字符集对应的 排序字符集（collation）。ut8mb4对应的排序字符集常用的有 utf8mb4_unicode_ci、utf8mb4_general_ci，到底采用哪个在 stackoverflow 上有个讨论，What’s the difference between utf8_general_ci and utf8_unicode_ci

主要从排序准确性和性能两方面看：

  • 准确性
    utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
    utf8mb4_general_ci 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。
    但是在绝大多数情况下，这种特殊字符的顺序一定要那么精确吗。比如Unicode把ß、Œ当成ss和OE来看；而general会把它们当成s、e，再如ÀÁÅåāă各自都与 A 相等。

  • 性能
    utf8mb4_general_ci 在比较和排序的时候更快
    utf8mb4_unicode_ci 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
    但是在绝大多数情况下，不会发生此类复杂比较。

general理论上比Unicode可能快些，但相比现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。
在国内如果用到微信接口了，就用utf8mb4_general_ci否则Emoji表情符号会出现问题,国际化的时候用utf8mb4_unicode_ci

3. utf8转换为utf8mb4

3.1 创建备份

在要升级的服务器上创建所有数据库的备份。安全第一！

3.2 升级MySQL服务器

将MySQL服务器升级到v5.5.3 +

3.3 查看修改数据库 表格和列

将数据库，表和列的字符集和整理属性更改为使用utf8mb4而不是utf8。

  • 3.3.1.查看当前默认字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables where Variable_name like 'character%' or Variable_name like 'collation%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | utf8                                       |
| character_set_connection | utf8                                       |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | utf8                                       |
| character_set_server     | utf8                                       |
| character_set_system     | utf8                                       |
| collation_connection     | utf8_general_ci                            |
| collation_database       | utf8_general_ci                            |
| collation_server         | utf8_general_ci                            |
+--------------------------+--------------------------------------------+
  • 3.3.2.查看database的字符编码
1
2
3
4
5
6
7
mysql> show create database polarsnow;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 3.3.3.查看table的字符编码
1
2
3
4
5
6
7
8
9
mysql> show create table ps;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+---------------------------------------------------------------------------------------------+
| ps    | CREATE TABLE `ps` (
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 3.3.4.查看column的字符编码
1
2
3
4
5
6
7
mysql> show full columns from ps;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

3.4 字符集修改

3.4.1. 修改database默认的字符集
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

1
2
3
4
5
6
7
8
9
10
mysql> ALTER DATABASE polarsnow CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Query OK, 1 row affected (0.03 sec)

mysql> show create database polarsnow;
+-----------+--------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                  |
+-----------+--------------------------------------------------------------------------------------------------+
| polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+-----------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注：虽然修改了database的字符集为utf8mb4，但是实际只是修改了database新创建的表，默认使用utf8mb4，原来已经存在的表，字符集并没有跟着改变，需要手动为每张表设置字符集

3.4.2. 修改table的字符集

只修改表默认的字符集
ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
修改表默认的字符集和所有字符列的字符集
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> show create table ps;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+---------------------------------------------------------------------------------------------+
| ps    | CREATE TABLE `ps` (
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show full columns from ps;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE ps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ps;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| ps    | CREATE TABLE `ps` (
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show full columns from ps;
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| name  | varchar(100) | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

3.4.3 修改column默认的字符集
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注：VARCHAR(191) 根据字段实例的类型填写

3.4 检查字段的最大长度和索引列

  • 字段长度
    由于从utf8升级到了utf8mb4，一个字符所占用的空间也由3个字节增长到4个字节，但是我们当初创建表时，设置的字段类型以及最大的长度没有改变。例如，你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节，三个字节一个字符的情况下可以容纳85个字符，四个字节一个字符的情况下只能容纳63个字符，如果原表中的这个字段的值有一个或多个超过了63个字符，那么转换成utf8mb4字符编码时将转换失败，你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码

  • 索引
    在InnoDB引擎中，最大的索引长度为767字节，三个字节一个字符的情况下，索引列的字符长度最大可以达到255，四个字节一个字符的情况下，索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码

3.5 修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set = utf8mb4

# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set = utf8mb4

# 本地mysql服务的配置
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

service mysqld restart

3.6 检查修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

注：character_set_system 一直都会是 utf8，不能被更改

3.7 修复&优化所有数据表

1
mysqlcheck -u root -p --auto-repair --optimize --all-databases

4.参考

修改MySQL的字符集为utf8mb4
mysql使用utf8mb4经验吐血总结
How to support full Unicode in MySQL databases
MySQL修改表、字段、库的字符集及字符集说明

CATALOG
    FEATURED TAGS
    mysql
    FRIENDS