Using utf8mb4 in Django to Support Emoji

1. What Is utf8

In theory, utf8 uses 1-6 characters,

In practice, the latest utf8 specification uses only one to four bytes, and can encode at most 21 bits, which is exactly enough to represent all 17 Unicode planes.

2. What Is utf8mb4

utf8mb4 is a superset of utf8. In theory, if you were using utf8 before and then change the charset to utf8mb4, it will not cause any problem when reading the existing utf8-encoded data.

3. utf8 in MySQL

utf8 in MySQL supports only utf8 characters of up to three bytes, that is, the Basic Multilingual Plane in Unicode.

The reason only three characters are used may be that characters outside the Basic Multilingual Plane are rarely used.

After MySQL 5.5.3, to store 4-byte UTF-8 characters in MySQL you can use the utf8mb4 charset. For example, you can use utf8mb4 character encoding to store emoji expressions directly, rather than storing a replacement character for the expression.

4. Fixing the \xF0\x9F\x90\xAF Error in Django

If you write an expression character on a MySQL utf8 charset, you will get the error Incorrect string value: ’\xF0\x9F\x90\xAF’ for column ....

The solution is to change the encoding format of the relevant column or table, then configure the database access encoding in Django. Since utf8mb4 is a superset of utf8 and compatible with utf8 data, you do not need to modify the original data and can use it normally.

4.1 Changing the MySQL Encoding

Inspect the table structure encoding

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

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

Change the table structure charset, as needed

1
2
3
4
5
6
# Change a database
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
# Change a table
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Change a column
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

4.2 Django utf8mb4 Configuration

1
2
3
4
5
6
DATABASES = {
    'default': {
        ...
        'OPTIONS': {'charset':'utf8mb4'},
    },
}

4.3 Changing the MySQL Configuration [Optional]

C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[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