Solution to the problem of failure to insert emoji expressions into MySQL

Emoji expressions are often encountered in our daily development, but recently I encountered a problem when inserting emoji expressions into mysql. I finally solved it by searching for relevant information, so I will share the process of solving this problem. This article mainly I will introduce to you the solution to the problem of MySQL failure to insert emoji expressions. Friends in need can refer to it.

Preface

I used to think that UTF-8 was a universal solution to character set problems until I encountered this problem recently. Recently, I was working on a crawler for Sina Weibo. When saving, I found that as long as the emoji expression is maintained, the following exception will be thrown:

Incorrect string value: '\xF0\x90\x8D\x83\xF0\x90...'

As we all know UTF-8 It is 3 bytes, which already includes most of the fonts we see every day. But 3 bytes are far from enough to accommodate all the text, so there is utf8mb4. utf8mb4 is a superset of utf8, occupying 4 characters. section, backward compatible with utf8. The emoji expressions we use every day are 4 bytes.

So when we insert data into the utf8 data table, it will report Incorrect string value This error.

It’s easy to find the solution through Google. The specific solution is as follows:

1. Modify the data The character set of the table is utf8mb4

. This is very simple. You can find a lot of modification statements online, but it is recommended to rebuild the table and use mysqldump -uusername -ppassword database_name table_name > table.sql Back up the corresponding data table and modify the character set of the table creation statement to utf8mb4, then mysql -uusername -ppassword database_name < table.sql Reimport sql You can complete the character set modification operation.

2. The MySQL database version must be 5.5.3 or above

Network All the articles above indicate that MySQL 5.5.3 or above is required to support utf8mb4. However, the database version I used is 5.5.18, which can still solve the problem in the end, so students should not rush to the operation and maintenance brother to upgrade the database first. Try to see if you can solve the problem by yourself.

3. Modify the database configuration file /etc/my.cnf and restart the mysql service

Mainly modify the default character set of the database, as well as the connection and query character set. [Mysql supports emoji upgrade encoding to UTF8MB4][1] This article has detailed setting methods, [In-depth Mysql character set settings ][2] This article has the function of each character set set in it. You can learn more about it.

##4. Upgrade MySQL Connector to 5.1.21 and above

Of all the above operations, the most critical is step 3, modifying the database configuration file, which probably

[client]
# 客户端来源数据的默认字符集
default-character-set = utf8mb4
[mysqld]
# 服务端默认字符集
character-set-server=utf8mb4
# 连接层默认字符集
collation-server=utf8mb4_unicode_ci
[mysql]
# 数据库默认字符集
default-character-set = utf8mb4

These configurations specify the character sets used by the pipelines through which data passes from the client to the server. Problems with each pipeline may cause insertion failure or garbled characters.

But many times, online databases cannot modify database files casually, so our operation and maintenance classmates decisively rejected my request to modify the database configuration file (T_T)

So I can only Solved it with code. The first step is to start with the character set specified when connecting to JDBC.

jdbc:mysql://localhost:3306/ding?characterEncoding=UTF-8

Mainly change UTF-8 to utf8mb4 for The Java Style Charset string should be able to solve the problem, right?

But unfortunately, Java JDBC does not have a character set for utf8mb4. When using UTF-8, it can be compatible with urf8mb4 and automatically Convert character set.

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting. – [MySQL:Using Character Sets and Unicode][3]

Later, I did some popular science, and in every query request, you can Explicitly specify the character set used, use

set names utf8mb4 You can specify the character set of this link as utf8mb4, but this setting will become invalid after each connection is released.

The current solution is to explicitly call and execute

set names utf8mb4 when you need to insert utf8mb4, such as:

jdbcTemplate.execute("set names utf8mb4");
jdbcTempalte.execute("...");

It should be noted that when we use the ORM framework, due to performance optimization reasons, the framework will delay submission. Unless the transaction ends or the user actively calls forced submission, the

set names utf8mb4 responsible for execution will still not take effect. .

Here I am using myBatis, taking MessageDao as an example

// MessageDao
public interface MessageDao {
 @Update("set names utf8mb4")
 public void setCharsetToUtf8mb4();
 @Insert("insert into tb_message ......")
 public void insert(Message msg);
}
// test code
SqlSession sqlSession = sqlSessioFactory.openSession();
messageDao = sqlSession.getMapper(MessageDao.class);
messageDao.setCharsetToUtf8mb4();
// 强制提交
sqlSession.commit();
messageDao.insert(message);

At this point, the problem is solved...

Hey, it would be great if things could go so smoothly. In the project, the mybatis instance is managed by Spring, which means I can't get the sqlSession, which means I can't force the submission. And Because of the limitations of the Spring transaction framework, it does not allow users to explicitly call forced submission. I am still struggling with this problem.

There are two solutions:

• Using AOP, when it is possible to insert 4-byte UTF8 characters, the prefix method executes set names utf8mb4, but this solution cannot yet determine that the AOP method will be implemented by Spring. Transaction management, and in the pre-method, whether the link obtained is the same session as the connection object obtained next.

• Study the creation method of Spring JDBC and write one The hook executes set names utf8mb4 every time it creates a new database connection, thus ensuring that the character set has been set for each link obtained.
  

The above is the detailed content of Solution to the problem of failure to insert emoji expressions into MySQL. For more information, please follow other related articles on the PHP Chinese website!