Centos系统下载大全 | Redhat系统下载大全 | Windows2012系统下载大全 | Windows2008系统下载大全| CMS教程 | 网站地图 51运维网-专注Linux/Unix系统安全运维!
当前位置:51运维网 > 数据库 > Oracle > 正文

mysql设置字符集utf8mb4

时间:2016-05-24 23:31 来源:网络整理 作者:51ou.com 阅读:

为什么要把数据库的字符集设置成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了,这就是我想要的结果,现象可以去连项目测试了。


本文永久更新地址:

感谢您对【51运维网 http://www.51ou.com/】的支持,我们为您免费提供《mysql设置字符集utf8mb4》技术文章,《mysql设置字符集utf8mb4》详细使用和说明,有时《mysql设置字符集utf8mb4》可能不完善、敬请谅解!如果《mysql设置字符集utf8mb4》有错误请给我们留言,我们将尽快修复文章错误,如果您觉得本站不错,请分享给周围的朋友!谢谢!

顶一下
(0)
0%
踩一下
(0)
0%
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
验证码:点击我更换图片