ITPub博客

首页 > 数据库 > MySQL > mysql字符集--utf8mb4

mysql字符集--utf8mb4

原创 MySQL 作者:warren20 时间:2015-09-15 21:08:42 2 删除 编辑
今天有个新项目,要将客户留言的表情符号emjoi 字符给存在mysql数据库中,可是之前数据库创建的时候,都是用的utf8的字符集,导致这些表情符号存进来就是乱码;

对于mysql来说,在中国,我们通常选择的有utf8和gbk,但是在MySQL5.5版本之后,开始支持utf8mb4字符集,这里就浅析一下utf8和utf8mb4的区别。
首先说一下utf8mb4是utf8的一个超集,它完全兼容utf8字符集。以往mysql里面的utf8字符集的一个字符最多只有3个字节,只支持bmp这部分的unicode编码区,而utf8mb4则扩展到一个字符最多能够有4个字节,所以能够支持更多的字符集。

1. 调整my.cnf 文件
[client]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4

2 . mysqldump 将数据库脚本导出来
    由于项目上线不久,数据量不大,所以就mysqldump导出,其实个人还没有找到其他的方案

3. 重启mysql服务

重启服务是OK了,但是用innobackupex进行数据库备份的时候,却报错了
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.


This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.


Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p


150915 15:30:25  innobackupex: Executing a version check against the server...
150915 15:30:25  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3011
main::mysql_connect('abort_on_error', 1) called at /usr/bin/innobackupex line 1551
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup','root',...) failed: Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)
 at /usr/bin/innobackupex line 2995
150915 15:30:25  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3011
main::mysql_connect('abort_on_error', 1) called at /usr/bin/innobackupex line 1570
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup','root',...) failed: Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)
 at /usr/bin/innobackupex line 2995


在网上搜索了一下,是mysql字符集的配置文件“/usr/share/mysql/charsets/Index.xml” 的问题;

把配置文件/usr/share/mysql/charsets/Index.xml中的utf8编码改为utf8mb4即可,修改方法如下:
修改前:
  1. <charset name="utf8">
  2. <family>Unicode</family>
  3. <description>UTF-8 Unicode</description>
  4. <alias>utf-8</alias>
  5. <collation name="utf8_general_ci" id="33">
  6. <flag>primary</flag>
  7. <flag>compiled</flag>
  8. </collation>
  9. <collation name="utf8_bin" id="83">
  10. <flag>binary</flag>
  11. <flag>compiled</flag>
  12. </collation>
  13. </charset>
修改后:
  1. <charset name="utf8mb4">
  2. <family>Unicode</family>
  3. <description>UTF-8 Unicode</description>
  4. <alias>utf-8</alias>
  5. <collation name="utf8_general_ci" id="33">
  6. <flag>primary</flag>
  7. <flag>compiled</flag>
  8. </collation>
  9. <collation name="utf8_bin" id="83">
  10. <flag>binary</flag>
  11. <flag>compiled</flag>
  12. </collation>
  13. </charset>


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1800526/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-03-03

  • 博文量
    56
  • 访问量
    320814