MySQL sets utf8mb4 Encoding

MySQL sets utf8mb4 Encoding

There is a project that needs to store ios emojis)
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

Two Encoded chinese characters:
Http: // 127.0.0.1: 8080/Web/CharsetTest? Content = % 25E4% 25B8% 25AD % 25E6% 2596% 2587

For more information about the two encodings, see:

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 character sets.
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

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: