Character set

Error: Insert emoticons in MySQL. This is because the MySQL utF8 character set is not supported for emoji, so you need to change it to UTF8MB4. Before mysql 5.5, UTF8MB4 only supported 1 to 3 bytes of Unicode encoding, and only supported the BMP part of the unicode encoding (from where to where), which is basically 0000 to FFFF. Starting with MYSQL5.5, 4-byte UTF encoding UTF8MB4 is supported. A character can have up to 4 bytes, so more character sets can be supported. Utf8mb4 is a superset of UTF8. Utf8mb4 is compatible with UTF8 and can represent more characters than UTF8

The storage emoticons reported the following error

Error 1366: Incorrect string value: '\\xF0\\x9F\\x98\\x8E</... ' for column 'sn_content' at row 1Copy the code

This problem is caused by the database encoding, first of all, we come to know the difference between utf8 and utf8mb4:

Utf8 generally refers to UTF-8, which is a variable-length character encoding for Unicode of up to three bytes per character, sometimes referred to as UTF8MB3. Utf8mb4 is a superset of UTF8, which stands for Most Bytes 4 and is specifically designed to be compatible with four-byte Unicode. MySQL added utF8MB4 encoding after 5.5.3. MySQL supports utF8 encoding with a maximum length of 3 bytes. If a character is 4 bytes wide, an exception will be inserted. The maximum number of Unicode characters that can be encoded by a three-byte UTF8 is 0xFFFF. Any Unicode character that is not in the basic multitext plane cannot be stored in MySQL's UTF8 character set, including emojis and many unusual Chinese characters, as well as any additional Unicode characters. To store 4-byte UTF8 characters in MySQL, use the UTF8MB4 character set.Copy the code

As can be seen from the above, in order to store emojis, the encoding mode of the database needs to be UTF8MB4, so now let’s find the encoding configuration of the game database:

show variables like 'character_set_database';
Copy the code

Screenshot below:

Modify database, data table character set, and table field character encoding as follows:

datebase charset=utf8mb4 datebase collation=utf8mb4_unicode_ci character set=utf8mb4 collation=utf8mb4_unicode_ci Charset= UTf8MB4 Collation= UTf8MB4_unicode_ci For example: ALTER TABLE 'test' CHANGE COLUMN 'content' 'content' TEXT CHARACTER SET 'utf8MB4' NOT NULL COMMENT 'content ';Copy the code

Next, select emoticon storage in the front-end rich text box as follows:

Then try to save the storage, but still failed, error:

{"code":500,"msg":"Error 3988: Conversion from collation utf8_general_ci into utf8mb4_unicode_ci impossible for parameter","data":null}
Copy the code

To change the content property type from text to blob, modify the mysql configuration file my.conf

[mysqld] # server default character set character-set-server= UTF8MB4 # Connection layer default character set Collation-server = UTf8MB4_unicode_ci [mysql] # default-character-set = UTf8MB4Copy the code

After the modification, restart the mysql service

conclusion

It is recommended to use UTF8MB4 for database and data table. If there is a requirement to store emoticons in a project, it is recommended to use BLOB type for storage. The above are the problems encountered in storing emoticons in my project.