Shulou(Shulou.com)06/01 Report--

Editor to share with you the case of MySQL supporting emoji icon storage. I hope you will learn a lot after reading this article. Let's discuss it together.

When UPDATA and INSERT data are in MySLQ, if the data has an emoji icon on it, for example:?,? It is easy to update or insert unsuccessfully, resulting in an error.

Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value:'\ xF0\ x9F\ X91\ xBD\ xF0\ x9F..' For column 'name' at row

It was about to collapse, but luckily the ghost problem was finally solved. The reason for the data is that before MYSQL 5.5, UTF8 coding only supported 1-3 bytes and only supported the unicode coding area of BMP, while the emoji icon happened to be a 4-byte code for storage. Starting with MYSQL5.5, it can support 4 bytes of UTF encoding utf8mb4, and a character can have up to 4 bytes, so it can support more character sets. Therefore, in order to solve the problem, it is necessary to change the character encoding of the database table to utf8mb4.

Common character set

ASCII: American standard code for information interchange; English and other Western European languages; single-byte encoding, 7 bits representing one character, a total of 128characters.

GBK: double-byte, Chinese character internal code extension specification; Chinese, Japanese and Korean characters, English, numbers; double-byte coding; a total of 21003 Chinese characters, the extension of GB2312.

UTF-8:Unicode standard variable length character coding; Unicode standard (uniform code), an industry standard, including systems in dozens of languages around the world

UTF-8: encodes each character with one to three bytes.

Utf8mb4: stores four bytes, and the application scenario is used to store emoji emoticons, because you can emoji emoticons with four bytes.

Utf8mb4:MySQL version > 5.5.3.

Other common character sets: UTF-32,UTF-16,Big5,latin1

The character set in the database has two meanings.

A collection of various words and symbols, including national characters, punctuation marks, graphic symbols, numbers, etc.

The encoding method of characters, that is, the mapping rules between binary data and characters.

System environment

MySQL 5.7.14

Mac OSX 10.11.6

Backup first

Back up all the data on your server before upgrading, keep good habits and safety first!

Upgrade your MySQL

The new database can be downloaded here for Upgrade the MySQL server to v5.5.3 +.

Modify your database, tables, fields

# for each database: ALTER DATABASE here database name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;# for each table: ALTER TABLE here is the table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;# for each field: ALTER TABLE here is the table name CHANGE field name repeating field name VARCHAR (191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci # the above sentence or use modify to change ALTER TABLE here is the table name modify field name VARCHAR (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT''

Utf8mb4 is fully backward compatible with utf8, free of garbled codes or other forms of data loss. In theory, you can rest assured to modify, if you are not at ease to modify, you can take a backup to restore the data, and then let the programmer deal with this compatible emoji storage problem, filter it and convert it to base64 when saving, and then transfer it back when you get it? ... It is more convenient to modify the database.

Check your fields and indexes

Don't set everything to utf8mb4, this is necessary. I only set it to utf8mb4 when some of my field types are VARCHAR.

Modify MySQL configuration file

This place is the worst. I can't find / etc/my.cnf on my Mac osx system. It doesn't exist, so we need to create such a file and modify it.

# enter this directory, # there is a file cd / usr/local/mysql/support-files/# with the suffix `.cnf` under this directory. Copy this file to the `etc` directory and name it `my.cnf`sudo cp my-default.cnf / etc/my.cnf#. Then edit the file `my.cnf` and copy the following to it. Sudo vim / etc/my.cnf

MySQL configuration file (/ etc/my.cnf):

[client] default-character-set = utf8mb4 [MySQL] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

Restart MySQL

It is important to restart the steps, otherwise there will be no effect.

Start: / usr/local/mysql/support-files/mysql.server start

Stop: / usr/local/mysql/support-files/mysql.server stop

Restart: / usr/local/mysql/support-files/mysql.server restart

The system can be restarted, stopped, or started in this form

Service mysqld stopservice mysqld startservice mysqld restart

Check to see if the setting is successful

Use the following command to query whether the setting is successful!

# Log in to MySQL to query mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character\ _ set\ _%' OR Variable_name LIKE 'collation%' # run the above code to show the following result # +-- + # | 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_unicode_ci | # | collation_database | utf8mb4_unicode_ci | # | collation_server | utf8mb4_unicode_ci | # +-- +-# View the table mysql > SHOW FULL COLUMNS FROM users_profile

At this point, it means you have succeeded! Congratulations! ~

Repair and optimize tables

There is no need to fix and optimize the table so far. Just to be on the safe side, I ran these two commands, although I don't know what the use of them is. Put it here and take notes.

REPAIR TABLE watch name; OPTIMIZE TABLE table name; after reading this article, I believe you have a certain understanding of the case of MySQL supporting emoji icon storage, want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.