Django

【Django】通过 ORM 访问 MySQL，插入 Emoji 报错

Posted by 西维蜀黍 on 2019-10-31, Last Modified on 2021-10-02

Problem

>>> paras = {u'name': u'\U0001f600'}
>>> from core import models
>>> models.Channel.objects.create(**paras)
Traceback (most recent call last):
    ...
  File "/Library/Python/2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
InternalError: (1366, u"Incorrect string value: '\\xF0\\x9F\\x98\\x80' for column 'name' at row 1")

通过 ORM插入一条数据，在其中的一个类型为varchar的字段中插入一个 Emoji 表情，我插的是😀（对应的字符串为u'\U0001f600'），插入后：提示以下错误：

{InternalError}(1366, u"Incorrect string value: '\\xF0\\x9F\\x98\\x80' for column 'name' at row 1")

Solition

查看字符集（Charater Set）

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

修改字符集

# For each database:
ALTER DATABASE {your_db_name} CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each table:
ALTER TABLE {your_tb_name} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE {your_tb_name} CHANGE {your_column_name} name VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

重启 MySQL

$ mysql.server restart

查看字符集（Charater Set）

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Reference

FEATURED TAGS
algorithm algorithmproblem architecturalpattern architecture aws c# cachesystem codis compile concurrentcontrol database dataformat datastructure debug design designpattern distributedsystem django docker domain engineering freebsd git golang grafana hackintosh hadoop hardware hexo http hugo ios iot java javaee javascript kafka kubernetes linux linuxcommand linuxio lock macos markdown microservices mysql nas network networkprogramming nginx node.js npm oop openwrt operatingsystem padavan performance programming prometheus protobuf python redis router security shell software testing spring sql systemdesign truenas ubuntu vmware vpn windows wmware wordpress xml zookeeper
FRIENDS

TOC