1. Background of the problem:

When MySQL database is coded utf8, saving emoji expression fails, and abnormal information:

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

View the database character set information as follows

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /opt/mysql/share/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8_general_ci            |
+--------------------------+----------------------------+
11 rows in set

Find out what collation_connection, collation_database, collation_server are.

But it must be guaranteed.

System variables	describe
character_set_client	(Character set for client source data)
character_set_connection	(Connection Layer Character Set)
character_set_database	(The default character set of the currently selected database)
character_set_results	(Query result character set)
character_set_server	(Default internal operation character set)

These variables must be utf8mb4.

Second, the steps to solve the problem:

1. Linux environment, by editing my.cnf file, setting up MySQL database character set, CentOS 6.5 installing MySQL 5.7.21, no my.cnf file was found.

Regarding the order in which MySQL reads configuration parameters, the following description is given:

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File Name	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options
defaults-extra-file	The file specified with --defaults-extra-file=path, if any
~/.my.cnf	User-specific options

That is, / etc/my.cnf->/etc/mysql/my.cnf->/usr/local/mysql/etc/my.cnf

2. The MySQL configuration file my-default.ini installed in Windows environment is copied to the Linux server/usr/local/mysql/etc/directory and renamed my.cnf.

Add the following basic configuration information:

[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'

The complete documents are as follows: File download address

3. Restart MySQL database

Enter the following name to restart MySQL database,

service mysqld restart

Linux enters MySQL command line mode:

mysql -uroot -p

View the modified results:

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                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
| collation_connection     | utf8mb4_unicode_ci               |
| collation_database       | utf8mb4_unicode_ci               |
| collation_server         | utf8mb4_unicode_ci               |
+--------------------------+----------------------------------+
11 rows in set (0.31 sec)

So far, the database character set has been modified successfully.

3. Postscript:

If MySQL is connected through Navicat under Windows, it will be found that after the database is restarted, the query results are as follows:

Has been suspected of database character encoding, modification failure, think about possible connection with the client, Navicat database connection properties, encoding options changed to utf-8

The query results are consistent with those from the Linux MySQL command line. Finally upload a project effect map