mysql设置字符集utf8mb4

95 阅读 0 评论 63 点赞

我是靠谱客的博主英俊夕阳，这篇文章主要介绍mysql设置字符集utf8mb4，现在分享给大家，希望可以做个参考。

为什么要把数据库的字符集设置成utf8mb4呢？以前一直用的都是utf8啊？
答案在这里：utf8适用于不使用移动设备的互联网交互，utf8mb4适用于当前的移动设备互联网开发，因为移动设备中常常会有表情符号（emoji）的存储，它占用4个字节的存储空间，而utf8是3个字节，这样，用3个字节去存储4个字节的东西，很明显是存不下的，会报错，所以要用utf8mb4，并且utf8mb4是兼容utf8的，那么，就没有理由不用utf8mb4字符集了。

介绍设置mysql的字符集：
1 在ubuntu安装mysql （密码自己设置）

复制代码

1
2
3
sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

2 进入mysql，查看信息

复制代码

zhangl@zhangl:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.
Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.12-0ubuntu1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
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
| latin1
|
| character_set_filesystem | binary
|
| character_set_results
| utf8
|
| character_set_server
| latin1
|
| character_set_system
| utf8
|
| collation_connection
| utf8_general_ci
|
| collation_database
| latin1_swedish_ci |
| collation_server
| latin1_swedish_ci |
+--------------------------+-------------------+
10 rows in set (0.02 sec)

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
zhangl@zhangl:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.
Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.12-0ubuntu1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
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
| latin1
|
| character_set_filesystem | binary
|
| character_set_results
| utf8
|
| character_set_server
| latin1
|
| character_set_system
| utf8
|
| collation_connection
| utf8_general_ci
|
| collation_database
| latin1_swedish_ci |
| collation_server
| latin1_swedish_ci |
+--------------------------+-------------------+
10 rows in set (0.02 sec)

可以看到我的mysql版本是5.7的，utf8mb4有一个使用限制，mysql版本必须是5.5以上，大家需要注意，我目前用的ubuntu系统是16.04的。当前mysql的字符集配置如上表，我们的目的是更改成utf8mb4。

3 找到mysql的配置文件，可用命令

复制代码

1
sudo find / -name my.cnf

我的配置文件位置如下，个别的位置有所不同
/etc/mysql/mysql.conf.d/mysqld.cnf

4 修改配置文件
在原文中添加以下内容：

复制代码

1
2
3
4
5
6
7
8
9
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

原文件中无“[client]”和“[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
......
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld_safe]
socket
= /var/run/mysqld/mysqld.sock
nice
= 0
[mysqld]
#
# * Basic Settings
#
user
= mysql
pid-file
= /var/run/mysqld/mysqld.pid
socket
= /var/run/mysqld/mysqld.sock
port
= 3306
basedir
= /usr
datadir
= /var/lib/mysql
tmpdir
= /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
#
# Instead of skip-networking the default is now to listen only on
......

5 重启mysql服务

复制代码

1
sudo service mysql restart

6 查看结果

复制代码

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
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
3
Current database: *** NONE ***
+--------------------------+--------------------+
| 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)