MySQL UTF8 encoding

Do project, error

"Incorrect string value: ' \\xF0\\x9F\\x98\\x8B ' for column ' nickname ' at row 1"

The reason is that the nickname contains emoticons, the expression is 4-byte encoding, and the MySQL utf-8 encoding only supports 1-3 bytes. Starting with MYSQL5.5, support for 4 byte UTF encoding UTF8MB4,UTF8MB4 compatible UTF8, that is, UTF8 is a subset of UTF8MB4. So then the project should use the UTF8MB4 subset by default, not so much.

So the code to change the table, I have no good way. Back up the database on the line, and then manually adjust the encoding of the table that contains the nickname.

I used Django to develop it, so I looked at how Django set the default encoding. The answer is:

Hyper simple, just add to ‘OPTIONS‘: {‘charset‘: ‘utf8mb4‘} your DATABASES configuration:

DATABASES = {    ’default’: {        ’ENGINE’: ’django.db.backends.mysql’,        ’NAME’: ’example’,        ’USER’: ’example’,        ’PASSWORD’: ’example’,        ’HOST’: ’’,        ’PORT’: ’’,        ’OPTIONS’: {’charset’: ’utf8mb4’},    }}

Depending on the settings above, it is natural to use Django's migrate to take effect.

Before this project because of the number of people to handle, so did not use migrate instead of manually create the table, but also caused the settings utf8mb4 but the actual default utf-8.

Reference:

Http://blog.manbolo.com/2014/03/31/using-emojis-in-django-model-fields

https://my.oschina.net/wingyiu/blog/153357

MySQL UTF8 encoding