修改MySQL的字符集为utf8mb4
banner 2020-09-22 MySQL
# 一、为什么要修改为utf8mb4
一般情况下，MySQL默认的字符编码为 utf8，emoji的字符集已经超出了 utf8 的编码范畴，所以要使用 utf8mb4 存储emoji
# 二、什么是utf8mb4
utf8mb4 最明显的好处是解决了苹果挖的坑-推广了emoji表情。utf8mb4 解决了MySQL数据库存储emoji表情的问题
utf8mb4 是 utf8 的超集，理论上由 utf8 升级到 utf8mb4 字符编码没有任何兼容问题
# 三、如何升级
# 1、备份
备份所有需要升级字符编码的数据库
# 2、升级数据库
utf8mb4 是MySQL 5.5.3版本之后支持的字符集，所以，如果你需要使用这个字符集，前提条件是你的MySQL版本必须 >= 5.5.3
# 3、修改
在MySQL中，可以为一个database设置字符编码，可以为一张表设置字符编码，甚至可以为某一个字段设置字符编码
- 查看当前系统默认的字符集设置
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 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 |
+--------------------------+-----------------+
10 rows in set (0.01 sec)
mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
- 查看database的字符编码
mysql> show create database polarsnow;
+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
- 查看table的字符编码
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)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
- 查看column的字符编码
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)
1
2
3
4
5
6
7
2
3
4
5
6
7
# 3.1 修改database默认的字符集
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
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)
mysql> show tables;
+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
+---------------------+
1 row in set (0.00 sec)
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> create table test_tb2 (tb2 varchar(100) );
Query OK, 0 rows affected (0.21 sec)
mysql> show tables;
+---------------------+
| Tables_in_polarsnow |
+---------------------+
| ps |
| test_tb2 |
+---------------------+
2 rows in set (0.00 sec)
mysql> show create table test_tb2;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_tb2 | CREATE TABLE `test_tb2` (
`tb2` 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>
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
可以看到，虽然修改了database的字符集为 utf8mb4，但是实际只是修改了database新创建的表，默认使用 utf8mb4，原来已经存在的表，字符集并没有跟着改变，需要手动为每张表设置字符集
# 3.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;
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)
mysql>
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
40
41
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
40
41
# 3.3 修改column默认的字符集
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 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 修改配置文件
SET NAMES utf8 COLLATE utf8_unicode_ci 变成 SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
> 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
检查修改
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
注：character_set_system 一直都会是 utf8，不能被更改
# 3.6 修复&优化所有数据表
> mysqlcheck -u root -p --auto-repair --optimize --all-databases
1