Shulou(Shulou.com)05/31 Report--

Editor to share with you mysql storage emoji facial expression error how to do, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The details are as follows:

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 = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'

4. Restart the database and check for variables

The copy code is as follows:

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:

The copy code is as follows:

ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Change the table encoding:

The copy code is as follows:

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci

You can also change the encoding of the column if necessary

7. If you are unsuccessful in setting character_set_database,character_set_server in step 3, you can try it directly under mysql.exe

Set @ @ character_set_server='utf8mb4';set @ @ character_set_database='utf8mb4'

Now the database can store the coding of emoji facial expressions.

Attach my my.ini

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the# * * default location during install, and will be replaced if you# * * upgrade to a newer version of MySQL [client] default-character-set = utf8mb4 [MySQL] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'# Remove leading # client. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin# These are commonly set, remove the # and set as required.# basedir = .# datadir = .# port = .# server_id = .# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Msqlstore emoji expressions error reporting all the contents of the article "what to do if the expression error is reported in mysql Storage?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.