MySQL Insert emoji expression failure problem solving method

Objective

It was previously thought that UTF-8 was a universal Character set problem solution until recently encountered with this problem. Recently in the Sina Weibo crawler, in the library when it was found that as long as the emoji expression, it throws the following exception:

Incorrect string value: ' \xf0\x90\x8d\x83\xf0\x90 ... '

It is well known that UTF-8 is 3 bytes, which already includes most of the fonts we can see everyday. But 3 bytes is not enough to hold all the text, so there is utf8mb4, Utf8mb4 is UTF8 superset, accounting for 4 bytes, backwards-compatible UTF8. The emoji expression we use every day is 4 bytes.

So we're going to report this error when we insert data like UTF8 's data sheet Incorrect string value .

Google is easy to find a solution, the specific solution is as follows:

One, modify the data table of the character set is UTF8MB4

This is very simple, modify the statement online to find a large pile, but it is recommended to re-build the table, using the mysqldump -uusername -ppassword database_name table_name > table.sql backup of the corresponding data table, and modify the set of the table statement is UTF8MB4, and then mysql -uusername -ppassword database_name < table.sql  re-import SQL to complete the modified character set operation.

Second, MySQL database version to 5.5.3 and above

All the articles on the web show that MySQL 5.5. More than 3 of the version is supported UTF8MB4, but I use a database version of 5.5.18, the final can still solve the problem, so students do not hurry to find OPS brother upgrade database First, try to solve the problem by themselves.

Third, modify the database configuration file/etc/my.cnf and restart the MySQL service

The main is to modify the default character set of the database, as well as the connection, query the character set, [MySQL support emoji emoji upgrade code for UTF8MB4][1] This article has a detailed set of methods, [deep MySQL character set settings][2] This article has set the role of the various character sets, Everyone can be popular.

Iv. upgrading MySQL connector to 5.1.21 and above

Above all the operations, the most critical is step 3, modify the database configuration file, which probably modified the

[client]# default character set for client source data Default-character-set = utf8mb4[mysqld]# Server default Character set character-set-server=utf8mb4# Connection layer default character set collation-server=utf8mb4_unicode_ci[mysql]# database default Character Set Default-character-set = Utf8mb4

These configurations specify the character set used by a pipeline of data from the client to the service side, where problems with each of these pipelines can cause insertion to fail or garbled.

But most of the time, the online database can not be arbitrarily modified database files, so our operations are very decisive rebuffed my request to modify the database configuration file (T_T)

So it can only be solved with code, and at first it is ready to use the specified character set at the time of the JDBC connection.

Jdbc:mysql://localhost:3306/ding?characterencoding=utf-8

The main thing is to change UTF-8 to UTF8MB4 for Java Style CharSet string should be able to solve the problem?

Unfortunately, Java JDBC does not exist for the UTF8MB4 character set. Use UTF-8 to be compatible with URF8MB4 and automatically convert character sets.

For example, "to" 4-byte UTF-8 character sets with connector/j, configure the MySQL server with Character_set_server=utf 8MB4, and leave characterencoding out of the connector/j connection string. Connector/j'll then autodetect the UTF-8 setting. –[mysql:using Character sets and unicode][3]

Later on, in every query request, you can explicitly specify the character set used,  set names utf8mb4  you can specify the link character set is utf8mb4, but this setting will be invalidated after each connection is released.

The current solution is to invoke execution when the UTF8MB4 is inserted, set names utf8mb4 such as:

Jdbctemplate.execute ("Set names Utf8mb4"); Jdbctempalte.execute ("...");

It is important to note that when we use the ORM Framework, the framework delays the commit because of performance optimizations, and the execution set names utf8mb4 will not take effect unless the transaction is over or the user actively invokes the mandatory commit.

I'm using MyBatis here, taking Messagedao as an example.

Messagedaopublic interface Messagedao {@Update ("Set names utf8mb4") public void Setcharsettoutf8mb4 (); @Insert ("Inser T into Tb_message ... ") public void Insert (message msg); Test Codesqlsession sqlsession = Sqlsessiofactory.opensession (); Messagedao = Sqlsession.getmapper (MessageDao.class ); Messagedao.setcharsettoutf8mb4 ();//Mandatory submission of sqlsession.commit (); Messagedao.insert (message);

At this point, the problem is solved.

Hey, if things can be so smooth, in the project, MyBatis is the example is to spring to manage, that is to say I can't get sqlsession, that is, forced to submit. And because of the limitations of the spring transaction framework, he does not allow users to explicitly invoke forced commits. The problem is still being tangled.

There are two solution ideas:

• Using AOP, the predecessor method executes when it is possible to insert a 4-byte UTF8 character, set names utf8mb4 but the scheme does not yet determine whether the AOP method will be transacted by spring, and in the predecessor method, the link received is the same session as the connection object to be taken next.
• Research the method of creating spring jdbc, write a hook every time you create a new database connection, do it once set names utf8mb4 , so that each time you get the link is set.

Summarize

The above is the whole content of this article, to have time to experiment with the above two options. Hope that the content of this article on everyone's study or work can bring some help, if there are questions you can message exchange, thank you to the script home support.

MySQL Insert emoji expression failure problem solving method