Three solutions to MySQL insert emoji expression errors, Incorrect '\ xF0\x9F\x98\x84

The first step is to modify the mysql configuration file my conf

# Default character set for client source data
default-character-set = utf8mb4
character-set-client-handshake = FALSE
# Server default character set
# Connection layer default character set

init_connect='SET NAMES utf8mb4'
# Database default character set
default-character-set = utf8mb4

Step two Modify the character set of database/table and column
Enter mysql and execute the command as follows:

1) modify database Character set:

ALTER DATABASE Database name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


ALTER DATABASE xxxdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

2) After step 1) is completed, you need to perform use Database name, indicating the database that needs character set modification at present; Example: use xxxdb;

3)modify table Character set:

ALTER TABLE Table name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


ALTER TABLE user_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4) modify column Character set of:

ALTER TABLE Table name CHANGE Field name field name the original data type of the field CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


ALTER TABLE user_comments CHANGE content content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


After the above modification, exit sign out mysql

Step three Restart mysql

When I restart here, I started with service mysql restart,Finally, it was found that this command did not work, and the restart was unsuccessful, resulting in the failure to reach the desired character set when viewing the character set later.
Therefore, the following methods can be used to restart correctly mysql.
3.1 stop it msql Operation of
 adopt/etc/init.d/mysql implement stop command

3.2 start-up mysql
 adopt/etc/init.d/mysql implement start command

4. Check character set:

get into mysql Medium, use SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';Command to view the character set

Because mysql database needs backup and restore, it has emoji expression When importing and exporting sql files, you should pay attention to the encoding format during execution. The following are the specific operations of importing and exporting:

5. Import and export of sql file with emoji expression (mainly export in windows)
(if the export in the server code is: mysqldump – default character set utf8mb4 – u user name – p database name > physical path to export the file; the imported command does not need to specify the encoding format, but only needs to execute the command normally)
5.1 export
In the process, the suffix is sql file, and the file data contains emoji expression data. When backing up and exporting, do not use the third-party software to export, but use the command line to perform the export action. The main reason is that when exporting the file using the third-party, because the default export encoding format is utf-8, the encoding format only supports 3 bytes at most, while an emoji expression has 4 bytes, This will cause emoji expression data to become garbled. Therefore, when exporting locally, the specific steps are as follows:

1, Open cmd and find the path where the mysqldump executable file is located first;

2, After the path, Enter mysqldump – default character set-utf8mb4 – u user name – p database name > export the physical path of the file, press "Enter" to complete the export function, and the exported file can be found in the exported physical path.

5.2 import
In the linux system When importing sql files locally, do not use Navicat Premium to import, but use the source in the command line as shown below The physical path of the sql file to import as follows.

Posted by Skudd on Sat, 14 May 2022 09:30:40 +0300