MySQL supports emoji icon storage

When UPDATA and INSERT data are used in MySLQ, if the data has emoji icons, such as: ?, ?, ?, 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

Almost collapsed, but fortunately, this ghost problem was finally solved. The reason shown in the data is that before MYSQL 5.5, UTF8 encoding only supported 1-3 bytes and only supported the unicode encoding area of ​​​​the BMP part, and emoji icons happened to be stored in 4-byte encoding. Starting from MYSQL5.5, the 4-byte UTF encoding utf8mb4 can be supported. A character can have up to 4 bytes, so it can support more character sets. Therefore, to solve the problem, all database table character encodings must be changed to utf8mb4.

Recommended: mysql video tutorial

Commonly used character sets

ASCII: American Standard Encoding for Information Interchange; English and others Western European languages; single-byte encoding, 7 bits representing one character, 128 characters in total.

GBK: double-byte, Chinese character internal code expansion specification; Chinese, Japanese and Korean Chinese characters, English, numbers; double-byte encoding; a total of 21,003 Chinese characters are included, an extension of GB2312.

UTF-8: Unicode standard variable-length character encoding; Unicode standard (Unicode), a unified standard in the industry, including dozens of text systems in the world;

UTF-8: Use one to three bytes to encode each character.

utf8mb4: stores four bytes. The application scenario is used to store emoji expressions, because emoji expressions can be four bytes.

utf8mb4: MySQL version > 5.5.3.

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

The character set in the database contains two levels of meaning

A collection of various texts and symbols , including the characters, punctuation marks, graphic symbols, numbers, etc. of each country.

Character encoding method, 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 data on your server before upgrading data, maintain good habits, and safety first!

Upgrade your MySQL

The new database can be downloaded hereUpgrade the MySQL server to v5.5.3 .

Modify your database, tables, fields

# 对每一个数据库:
ALTER DATABASE 这里数据库名字 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 对每一个表:
ALTER TABLE 这里是表名字 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 对每一个字段:
ALTER TABLE 这里是表名字 CHANGE 字段名字 重复字段名字 VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 上面一句或者使用modify来更改
ALTER TABLE 这里是表名字 modify 字段名字 VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '';

utf8mb4 is fully backwards compatible with utf8, without garbled characters or other forms of data loss. Theoretically, you can modify it without worry. If you are worried about modifying it, you can restore the data from the backup, and then let the programmer handle the emoji-compatible storage problem. When saving, filter it and convert it to base64, and then convert it back when retrieving it? ... It is more convenient to modify the database.

Check your fields and indexes

Do not 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 most confusing. I can’t find /etc/my.cnf on my Mac osx system. It doesn’t exist at all, so we It is necessary to create such a file and modify it.

# 进入这个目录，
# 在这个目录下面有个后缀为`.cnf`的文件
cd /usr/local/mysql/support-files/
# 将这个文件复制到`etc`目录中并将名字命名为`my.cnf`
sudo cp my-default.cnf /etc/my.cnf
# 然后编辑`my.cnf`文件，将下面内容复制到里面。
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

The restart step is very important, otherwise it will have 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 restart, stop or start in this form

service mysqld stop
service mysqld start
service mysqld restart

Check whether it is set Success

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

# 登陆MySQL进行查询
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
# 运行上面代码显示下面结果
# +--------------------------+--------------------+
# | 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 |
# +--------------------------+--------------------+
# 查看表的情况
mysql>  SHOW FULL COLUMNS  FROM  users_profile;

Getting to this point means you succeeded! congratulations! ~

Repair and optimize the table

I have reached this step and there is no need to repair and optimize the table. To be on the safe side, I still ran these two commands. Although I don’t know what its purpose is, I’ll put it here and make a note.

REPAIR TABLE 表名字;
OPTIMIZE TABLE 表名字;

The above is the detailed content of MySQL supports emoji icon storage. For more information, please follow other related articles on the PHP Chinese website!