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

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

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

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.

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

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

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

// 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);

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!