Let MySQL support emoji emoticons

The company has new requirements, iOS client to go online comments can use emoji expression function, before MySQL 5.5, UTF-8 encoding only support 1-3 bytes, starting from MySQL 5.5, can support 4 byte UTF encoding utf8mb4, A character can support more character sets and can support more emoji.

UTF8MB4 is compatible with UTF8 and can represent more characters than UTF8, which is a superset of the UTF8 character set. So now some new business, such as the emoji expression in ISO, sets the character set of the MySQL database to UTF8MB4.

First look at the question:

caused by: java.sql.sqlexception: incorrect string value:  ' \xF6\x9D\x98\x84 '  for column  ' comment '  at row 1    at  Com.mysql.jdbc.SQLError.createSQLException (sqlerror.java:1074)     at  Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:4096)     at  Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:4028)     at  Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:2490)     at  Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2651)     at  Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2734)     at  Com.mysql.jdbc.PreparedStatement.executeInternal (preparedstatement.java:2155)     at  Com.mysql.jdbc.PreparedStatement.executeUpdate (preparedstatement.java:2458)

If we set the column comment to varchar (100), which is used to store the comment information, and now the new function on the line to store emoji expression, insert emoji expression will report the above error,UTF-8 encoding may be two, three, four bytes. Emoji expression is 4 bytes, and MySQL UTF8 encoding up to 3 bytes, so data is not plugged in . UTF8MB4 is compatible with UTF8 and can represent more characters than UTF8.

Solution: Convert the MySQL encoding from UTF8 to UTF8MB4.

Online article uncompromising, this article on the production of real-world parameters to record

The overall operation process is not difficult

One: First we modify the MY.CNF parameter

[CLIENT]DEFAULT-CHARACTER-SET=UTF8MB4 [MYSQL]DEFAULT-CHARACTER-SET=UTF8MB4 [mysqld]character-set-server = Utf8mb4collation-server = Utf8mb4_unicode_ciinit_connect = ' SET NAMES utf8mb4 ' Character-set-client-handshake = False

Second: Character set modification of database related tables

Convert the database to UTF8MB4

mysql> ALTER DATABASE ERP CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Convert the already built tables to UTF8MB4

Mysql>alter TABLE ' erp_comment ' CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Set the type of field you want to use emoji as:

Mysql>alter TABLE ' erp_comment ' MODIFY COLUMN ' comment ' varchar (+) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Three: Restart the database server for it to take effect

[Email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL .... success!

Four: Login database check whether the following:

mysql> SHOW VARIABLES WHERE variable_name like ' character% ' OR variable_name like ' collation% ';

+--------------------------+--------------------+
| variable_name | Value |
+--------------------------+--------------------+
| character_set_client | UTF8MB4 |
| character_set_connection | UTF8MB4 |
| Character_set_database | UTF8MB4 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8MB4 |
| Character_set_server | UTF8MB4 |
| Character_set_system | UTF8 |
| collation_connection | Utf8mb4_unicode_ci |
| Collation_database | Utf8mb4_unicode_ci |
| Collation_server | Utf8mb4_unicode_ci |
+--------------------------+--------------------+
Rows in Set (0.00 sec)

Special instructions under: Collation_connection/collation_database/collation_server If it is utf8mb4_general_ci, no relationship. But we must ensure that character_set_client/character_set_connection/character_set_database/character_set_results/character_set_ Server is UTF8MB4.

Four: Let the development side of the POM configuration, remove the characterencoding parameter, and recompile

If you are using a Java server, upgrade or make sure that your MySQL connector version is higher than 5.1.13, or you will still not be able to use UTF8MB4

Finally, let the front-end application insert emoji expression, you can.

Some small knowledge points

which Character-set-server and Collation-server These settings for the UTF8MB4 character set are relatively easy to understand, that is, the MySQL database related to the character set to UTF8MB4;

However, in order to implement the client UTF8 connection to MySQL, the use of the UTF8MB4 character set, the Mysqld configuration is configured in the init_connect= ' set NAMES utf8mb4 ' means that the initialization connection is set to the UTF8MB4 character set, and then configure a Skip-character-set-client-handshake = True ignores the client character set settings, regardless of the client's character set, and is used in accordance with the settings in Init_connect, thus satisfying the needs of the application.

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1847087

Let MySQL support emoji emoticons