django 1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x81'

django mysql存储emoji表情 要使utf8mb4 格式

原来一般的字符包括中文用utf8的话，mysql是用3个字节去存储的，而emoji表情要用4个字节的utf8，也就是utf8mb4格式。

改变mysql 数据编码

liunx 修改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

windows 修改mysql的配置文件： mysql57/my.ini 添加：

[mysqld]  
#port 设置端口 ,默认3306 可不指明
#basedir 设置Mysql的安装目录  
#datadir 设置mysql数据库的数据的存放目录
port = 3309
basedir=C:\mysql57
datadir=C:\mysql57\data
# 允许最大连接数  
max_connections=200  
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎    
default-storage-engine=InnoDB
#开启查询缓存以避免 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated
explicit_defaults_for_timestamp=true

collation-server = utf8mb4_unicode_ci

[client]
#cmd.exe mysql命令行所用端口

port = 3309
default-character-set=utf8mb4

 

然后重启，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               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

 

之后还是报错：查看了一下这部分的源代码，发现django/db/backends/mysql/base.py中的DatabaseWrapper类中有个kwargs的属性，里面有个key叫charset，默认值是utf8，
 

class DatabaseWrapper(BaseDatabaseWrapper):
    vendor = 'mysql'

    def get_connection_params(self):
        kwargs = {
            'conv': django_conversions,
            'charset': 'utf8',
        }
        if six.PY2:
            kwargs['use_unicode'] = True
        settings_dict = self.settings_dict
        if settings_dict['USER']:
            kwargs['user'] = settings_dict['USER']
        if settings_dict['NAME']:
            kwargs['db'] = settings_dict['NAME']
        if settings_dict['PASSWORD']:
            kwargs['passwd'] = force_str(settings_dict['PASSWORD'])
        if settings_dict['HOST'].startswith('/'):
            kwargs['unix_socket'] = settings_dict['HOST']
        elif settings_dict['HOST']:
            kwargs['host'] = settings_dict['HOST']
        if settings_dict['PORT']:
            kwargs['port'] = int(settings_dict['PORT'])

后面做数据库连接的时候，会用django的settings中，database的options去更新这一项。

settings 配置： 加上

'charset':'utf8mb4'

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': "gyf",
        'USER': 'root',
        'PASSWORD': "",
        'HOST': "127.0.0.1",
        'PORT': '3309',
        'OPTIONS': {'init_command': 'SET default_storage_engine=INNODB;'             
        ,'charset':'utf8mb4'}
    }
}

 

然后再尝试插入emoji表情，OK