MySQL sets utf8mb4 encoding _ MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.