How to save Emoji into MySQL? Enter the database Emoji expression question mark character problem

If the Emoji expression is stored in utf8 encodingMySQL databaseor MariaDB, it cannot be displayed.

Why does the Emoji expression entered into the database become a question mark character?

Because Emoji expressions are 4-byte characters, and the utf8 character set only supports 1-3 bytes of characters, Emoji expressions cannot be written into the database.

How to solve the problem that the Emoji expression entered into the database becomes a question mark character?

There are 2 solutions:

1. Enter text manually, replacing four-byte characters with custom characters.
2. modify MySQL Database character set, change the database character set from utf8 to utf8mb4, support 1-4 byte characters.

In fact, the first method is too much work and is not advisable.

It is recommended to use the second solution, modify the MySQL database character set.

Starting from MySQL 5.5.3 version, the database can support the utf4mb8 character set of 4 bytes, and a character can have up to 4 bytes, so it can support more character sets, and also can store Emoji expressions.

• After MySQL 5.5.3, you can basically seamlessly upgrade to the utf8mb4 character set.
• At the same time, utf8mb4 is compatible with the utf8 character set.
• The encoding, position and storage of utf8 characters are in utf8mb4.
• Same as utf8 character set, no damage to existing data.

Due tophpMyAdminFor newly created tables in a newly created database, the default character encoding is:Latin1

• This encoding cannot insert Chinese and Emoji expressions;
• You need to change the character set encoding of the database character set and table to utf8mb4;
• Then, restart MySQL, you can insert Chinese and Emoji expressions.

MySQL Emoji character problem solution

Step 1:Modify the MySQL configuration file my.cnf

• (Windows server is my.ini,LinuxThe server is my.cnf)
• my.cnf file, usually in/etc/mysql/my.cnfposition.
• CWP Control PanelThe my.cnf file, is in/etc/my.cnfposition.

After you find it, please add the following content in the following three parts ▼

[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

init_connect='SET NAMES utf8mb4'

Step 2:Restart the MySQL database

service mysqld restart

Step 3: Check the character set again and enter ▼ in the SQL command line

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_database';

Check if the following ▼

Enter the following SQL command▼

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Check if it is as follows▼

 +--------------------------+--------------------+

| 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 |

+--------------------------+--------------------+

After creating a new database in phpMyAdmin, you mustView database code ▼

SHOW CREATE DATABASE db_name;

• Such as:SHOW CREATE DATABASE test;

If the database encoding is notutf8mb4, you must modify the database character set toutf8mb4 .

How to modify the database character set to utf8mb4?

Step 1: Click the current database name in phpMyAdmin, then click SQL.

Step 2: Enter the following command to modify the database character set toutf8mb4▼

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4

• Such as:ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4;

Step 3:Restart the MySQL database

service mysqld restart

• That's it.

Here are some examples of SQL commands:

Change the default character set and all character columns of the table to the new character set ▼

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

• Such as:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

If you only modify the default character set of the table▼

ALTER TABLE tbl_name  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

• Such as:ALTER TABLE logtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;

Modify the character set of a field ▼

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name 

• Such as:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

View table code ▼

SHOW CREATE TABLE tbl_name;

View Field Codes ▼

SHOW FULL COLUMNS FROM tbl_name;

View current database code ▼

SHOW VARIABLES LIKE 'character_set_%';

• The mysql-connector-java driver package only supports utf5.1.13mb8 in 4+