步骤说明(utf8mb4):
1、修改my.cnf 配置;
2、修改mysql数据库以及表和字段的类型;
3、修改setting.py 数据库配置;
4、修改mysql -Index.xml 配置
5、重启数据库,重启服务;
django,mysql存储emoji表情,utf8mb4,参考如下:
发现一个错误:
Incorrect string value: '\\xF0\\x9F\\x90\\xA8' for column 'signature' at row 1
发现是参数里面是一个iOS的表情,也就是系统自带的emoji表情。
后台用的是django 1.6,数据库用的是Mysql 5.5.22,缓存用的是redis。
emoji表情,原来一般的字符包括中文用utf8的话,mysql是用3个字节去存储的,而emoji表情要用4个字节的utf8,也就是utf8mb4格式。
首先更改mysql的数据编码,修改mysql的配置文件:/etc/mysql/my.cnf 添加:
[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
然后重启,mysql,查看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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+----------------------------+
OK,mysql改完了,然后创建数据库:
<span style="font-family:SimSun;font-size:12px;">create database xxx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;</span>
创建数据库之后是syncdb,这时MySQLdb模块 需要1.2.4以上版本,关于MySQLdb模块的安装可以参考这篇文章,最新版是1.2.5
syncdb成功之后,再次尝试插入,还是报错。
查看了一下这部分的源代码,发现django/db/backends/mysql/base.py中的DatabaseWrapper类中有个kwargs的属性,里面有个key叫charset,默认值是utf8,后面做数据库连接的时候,会用django的settings中,database的options去更新这一项。
class DatabaseWrapper(BaseDatabaseWrapper):
def get_connection_params(self):
kwargs = {
'conv': django_conversions,
'charset': 'utf8',
}
那么我们修改一下django的settings.py,在数据库的配置中加入options项。
DATABASES = {
'default': {
#'ENGINE': 'sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
'ENGINE': 'django.db.backends.mysql',
#'NAME': '/opt/media/session.db', # Or path to database file if using sqlite3.
'NAME': DATABASE_NAME,
'USER': 'root', # Not used with sqlite3.
'PASSWORD': DATABASE_PASSWORD, # Not used with sqlite3.
'HOST': DATABASE_HOST, # Set to empty string for localhost. Not used with sqlite3.
'PORT': DATABASE_PORT, # Set to empty string for default. Not used with sqlite3.
'OPTIONS': {'charset':'utf8mb4'},
},
}
然后再尝试插入emoji表情,结果正常,取出来结果也正常,ios和android都可以正常使用。
【重要是最后那个xml修改】
对于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即可,修改方法如下:
修改前:
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
修改后:
<charset name="utf8mb4">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
1、修改my.cnf 配置;
2、修改mysql数据库以及表和字段的类型;
3、修改setting.py 数据库配置;
4、修改mysql -Index.xml 配置
5、重启数据库,重启服务;
django,mysql存储emoji表情,utf8mb4,参考如下:
发现一个错误:
Incorrect string value: '\\xF0\\x9F\\x90\\xA8' for column 'signature' at row 1
发现是参数里面是一个iOS的表情,也就是系统自带的emoji表情。
后台用的是django 1.6,数据库用的是Mysql 5.5.22,缓存用的是redis。
emoji表情,原来一般的字符包括中文用utf8的话,mysql是用3个字节去存储的,而emoji表情要用4个字节的utf8,也就是utf8mb4格式。
首先更改mysql的数据编码,修改mysql的配置文件:/etc/mysql/my.cnf 添加:
[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
然后重启,mysql,查看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 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+----------------------------+
OK,mysql改完了,然后创建数据库:
<span style="font-family:SimSun;font-size:12px;">create database xxx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;</span>
创建数据库之后是syncdb,这时MySQLdb模块 需要1.2.4以上版本,关于MySQLdb模块的安装可以参考这篇文章,最新版是1.2.5
syncdb成功之后,再次尝试插入,还是报错。
查看了一下这部分的源代码,发现django/db/backends/mysql/base.py中的DatabaseWrapper类中有个kwargs的属性,里面有个key叫charset,默认值是utf8,后面做数据库连接的时候,会用django的settings中,database的options去更新这一项。
class DatabaseWrapper(BaseDatabaseWrapper):
def get_connection_params(self):
kwargs = {
'conv': django_conversions,
'charset': 'utf8',
}
那么我们修改一下django的settings.py,在数据库的配置中加入options项。
DATABASES = {
'default': {
#'ENGINE': 'sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
'ENGINE': 'django.db.backends.mysql',
#'NAME': '/opt/media/session.db', # Or path to database file if using sqlite3.
'NAME': DATABASE_NAME,
'USER': 'root', # Not used with sqlite3.
'PASSWORD': DATABASE_PASSWORD, # Not used with sqlite3.
'HOST': DATABASE_HOST, # Set to empty string for localhost. Not used with sqlite3.
'PORT': DATABASE_PORT, # Set to empty string for default. Not used with sqlite3.
'OPTIONS': {'charset':'utf8mb4'},
},
}
然后再尝试插入emoji表情,结果正常,取出来结果也正常,ios和android都可以正常使用。
【重要是最后那个xml修改】
对于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即可,修改方法如下:
修改前:
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
修改后:
<charset name="utf8mb4">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>