MySQL sets utf8mb4 encoding _ MySQL

MySQL sets utf8mb4 encoding. there is a project that needs to store ios expressions (emoji expressions)

Although this type of expression is UTF-8 encoded, a single character occupies 4 bytes, while MySQL utf8 encoding can only store 3 bytes of characters.

In MySQL 5.6, you can set the encoding to utf8mb4, which is the superset of utf8.

Lab environment

MySQL 5.6.14

JDBC 5.1.31

Test table create table test (content varchar (50) engine = innodb, charset = utf8mb4;

Test procedure:

Import java. io. IOException;

Import java.net. URLDecoder;

Import java.net. URLEncoder;

Import java. SQL. Connection;

Import java. SQL. DriverManager;

Import java. SQL. PreparedStatement;

Import javax. servlet. ServletException;

Import javax. servlet. annotation. WebServlet;

Import javax. servlet. http. HttpServlet;

Import javax. servlet. http. HttpServletRequest;

Import javax. servlet. http. HttpServletResponse;

/**

* Servlet implementation class CharsetTest

*/

@ WebServlet ("/CharsetTest ")

Public class CharsetTest extends HttpServlet {

Protected void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String str = request. getParameter ("content ");

Str = URLDecoder. decode (str, "utf8 ");

System. out. println (URLEncoder. encode (str, "utf8 "));

Try {

Save (str );

} Catch (Exception e ){

E. printStackTrace ();

}

}

Protected void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

DoGet (request, response );

}

Private static void save (String content) throws Exception {

/**

* Create table test (content varchar (50) engine = innodb, charset = utf8mb4

*/

Class. forName ("com. mysql. jdbc. Driver ");

Connection connection = DriverManager. getConnection ("jdbc: mysql: // 127.0.0.1: 3306/xx", "xx", "xx ");

Connection. setAutoCommit (true );

// Query and run the command to set the character set

// Connection. prepareStatement ("set names utf8mb4" cmd.exe cuteQuery ();

PreparedStatement cmd = connection. prepareStatement ("insert into test values (?) ");

Cmd. setString (1, content );

Cmd.exe cuteUpdate ();

Cmd. close ();

Connection. close ();

}

}

Test link:

Ios emoticons after two encoding:

Http: // 127.0.0.1: 8080/Web/CharsetTest? Content = % 25F0% 259F % 2598% 2584

The encoded Chinese characters: http: // 127.0.0.1: 8080/Web/CharsetTest? Content = % 25E4% 25B8% 25AD % 25E6% 2596% 2587

For two encodings, see: http://www.linuxidc.com/Linux/2014-07/104232.htm

First, modify the mysql configuration file.

Character_set_server = utf8mb4

Restart the database and middleware.

Click the link of the two Tests to view the database. The data is inserted successfully.

Theoretically, the database does not need to be restarted. However, in actual tests, if the database is not restarted, an error is reported during the insertion.

If you are lucky, directly modify the character_set_server parameter and restart the database. if everything is normal, it will be OK.

Bad luck (such as me) is a tragedy.

I modified the configuration on the production database and restarted the database.

I found that ios expressions inserted into the database are garbled (all are question marks ????)

Even more tragic, after a few minutes, we suddenly found that all the data inserted on the line was garbled (and all of them were question marks ).

Thanks to the early detection, the database configuration is restored. otherwise, after several days of operation, it is estimated that you have to pack up your bags and leave.

Later troubleshooting this problem is caused by the JDBC driver, the online JDBC driver version is mysql-connector-java-5.1.6-bin

If the MySQL server is set to utf8mb4 or later, the JDBC driver does not matter, but after the driver of the earlier version is inserted, it will look like the following.

All non-English characters entered are garbled.

Because the JDBC driver does not support the utf8mb4 character set, the characterEncoding of the jdbc url cannot be set.

However, there are three ways to set the character set. 1. do not explicitly set the character set to inherit the server configuration.

2. run the set names Query (Query method) before executing the SQL statement)

3. set MySQL init_connect parameters

The results of various factors tested are as follows:

JDBC version	Common Chinese	Apple expressions
Server utf8 encoding	5.1.6	Normal	Insert error
5.1.6 Query	Normal	Normal
5.1.6 init_connect	Normal	Insert error
5.1.31	Normal	Insert error
5.1.31 Query	Normal	Normal
5.1.31 init_connect	Normal	Insert error
Server utf8mb4 encoding	5.1.6	Garbled	Garbled
5.1.6 Query	Garbled	Garbled
5.1.6 init_connect	Garbled	Garbled
5.1.31	Normal	Normal
5.1.31 Query	Normal	Normal
5.1.31 init_connect	Normal	Normal

Summary:

1. the character_set_server parameter has been modified and the database needs to be restarted.

2. use JDBC of a later version