MySQL Database UTF8mb4 Settings

Change the encoding of the MySQL database to utf8mb4

Original: http://blog.csdn.net/woslx/article/details/49685111

Utf-8 encodes 2-byte, 3-byte, 4-byte characters, but MySQL's utf8 encoding only supports 3 bytes of data, while mobile emoji data is 4 bytes. If you insert facial expression data directly into a utf-8-encoded database, an SQL exception will be reported in the Java program:

Java.sql.SQLException: Incorrect string value:'\ xF0\ x9F\ x92\ x94' for column 'name' at row 1

At com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1073)

At com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3593)

At com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3525)

At com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:1986)

At com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2140)

At com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2620)

At com.mysql.jdbc.StatementImpl.executeUpdate (StatementImpl.java:1662)

At com.mysql.jdbc.StatementImpl.executeUpdate (StatementImpl.java:1581)

4-byte characters can be encoded and stored, and then decoded when they are taken out. But doing so will result in encoding and decoding wherever the character is used.

Utf8mb4 coding is a superset of utf8 coding, compatible with utf8, and can store 4 bytes of emoji characters.

The advantage of using utf8mb4 coding is that when storing and obtaining data, there is no need to consider the encoding and decoding of emoji characters.

Change the database encoding to utf8mb4:

1. Version of MySQL

The minimum mysql version of utf8mb4 supports version 5.5.3. If not, upgrade to a newer version.

2. MySQL driver

5.1.34 available, with a minimum of 5.1.13

3. Modify MySQL configuration file

Modify the mysql configuration file my.cnf (windows is my.ini)

The my.cnf is usually in the etc/mysql/my.cnf location. When you find it, please add the following in the following three sections:

[client]

Default-character-set = utf8mb4

[mysql]

Default-character-set = utf8mb4

[mysqld]

Character-set-client-handshake = FALSE

Character-set-server = utf8mb4

Collation-server = utf8mb4_unicode_ci

Init_connect='SET NAMES utf8mb4'

4. Restart the database and check for variables

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE' collation%'

Variable_nameValuecharacter_set_clientutf8mb4character_set_connectionutf8mb4character_set_databaseutf8mb4character_set_filesystembinarycharacter_set_resultsutf8mb4character_set_serverutf8mb4character_set_systemutf8collation_connectionutf8mb4_unicode_cicollation_databaseutf8mb4_unicode_cicollation_serverutf8mb4_unicode_ci

It doesn't matter what collation_connection, collation_database and collation_server are.

But you have to make sure

The system variable describes character_set_client (character set used by client source data) character_set_connection (connection layer character set) character_set_database (default character set of currently selected database) character_set_results (query result character set) character_set_server (default internal operation character set)

These variables must be utf8mb4.

5. Configuration of database connection

In the database connection parameters:

CharacterEncoding=utf8 is automatically recognized as utf8mb4, or it can be automatically detected without this parameter.

And autoReconnect=true must be added.

6. Convert the database and built tables to utf8mb4 as well

Change the database code: ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Change table encoding: ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci

You can also change the encoding of the column if necessary