Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to support emoji icon storage in MySQL, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

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, maintain good habits and security!

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 into 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 = FALSE character-set-server = utf8mb4 collation-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 stop service mysqld start service 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 table name; OPTIMIZE TABLE table name; on how to support emoji icon storage in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.