1
2
3
4
5
6

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) NOT NULL,
  `age` int(4) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1
2
3
4

User user = new User();
user.setName("\uD83D\uDC8B");
user.setAge(18);
userMapper.insertUser(user);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1
### The error may involve com.wakzz.database.persistence.UserMapper.insertUser-Inline
### The error occurred while setting parameters
### SQL: insert into user_info (name, age)   values   (?, ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy81.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy92.insertUser(Unknown Source)

1
2

mysql> insert into user_info (name,age) values ('💋',18);
Query OK, 1 row affected

1

character_set_server = utf8mb4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql> show variables like "%char%";
+
| Variable_name            | Value                                  |
+
| character_set_client     | utf8mb4                                |
| character_set_connection | utf8mb4                                |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8mb4                                |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/soft/mysql-5.6.31/share/charsets/ |
+
8 rows in set

mysql> SHOW VARIABLES LIKE 'collation%';
+
| Variable_name        | Value              |
+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | utf8_general_ci    |
+
3 rows in set

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql> show variables like "%char%";
+
| Variable_name            | Value                                  |
+
| character_set_client     | utf8mb4                                |
| character_set_connection | utf8mb4                                |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8mb4                                |
| character_set_server     | utf8mb4                                |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/soft/mysql-5.6.31/share/charsets/ |
+
8 rows in set

mysql> SHOW VARIABLES LIKE 'collation%';
+
| Variable_name        | Value              |
+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | utf8mb4_general_ci |
+
3 rows in set

1
2
3
4
5

mysql> SET NAMES utf8;
Query OK, 0 rows affected

mysql> insert into user_info (name,age) values ('💋',18);
1366 - Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1

1
2
3
4
5

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected

mysql> insert into user_info (name,age) values ('💋',18);
Query OK, 1 row affected

1
2

collation[33] = new Collation(33, "utf8_general_ci", 1, MYSQL_CHARSET_NAME_utf8);
collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);

1
2
3
4
5
6
7
8
9
10
11
12
13

boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);

boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));

if (!getUseOldUTF8Behavior()) {
    if (dontCheckServerMatch || !characterSetNamesMatches("utf8") || (utf8mb4Supported && !characterSetNamesMatches("utf8mb4"))) {
        
        execSQL(null, "SET NAMES " + (useutf8mb4 ? "utf8mb4" : "utf8"), -1, null, DEFAULT_RESULT_SET_TYPE,
                DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);
        this.serverVariables.put("character_set_client", useutf8mb4 ? "utf8mb4" : "utf8");
        this.serverVariables.put("character_set_connection", useutf8mb4 ? "utf8mb4" : "utf8");
    }
}