utf8mb4 linux mysql_mysql设置字符集utf8mb4

为什么要把数据库的字符集设置成utf8mb4呢？以前一直用的都是utf8啊？

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

介绍设置MySQL的字符集：

1.在ubuntu安装mysql (密码自己设置)

sudo apt-get install mysql-server

apt-get isntall 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)

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

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

sudo find / -name my.cnf

我的配置文件位置如下，个别的位置有所不同

/etc/mysql/mysql.conf.d/mysqld.cnf

4.修改配置文件

在原文中添加以下内容：

[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]”，需要手动添加上。

......

# 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服务

sudo service mysql restart

6.查看结果

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)

ok了，这就是我想要的结果，现象可以去连项目测试了。