Expression Store Exception--mybatis throws exception (Java.sql.SQLException:Incorrect string value: ' \xf0\x9f\x92\x94 ' for column ' name ' at row 1)

Article reference

42171035

Https://www.cnblogs.com/WangYunShuaiBaoLe/p/9055215.html

Https://www.jb51.net/article/112879.htm

Background

iOS side test found that in the Memo column output emoji expression, save the system exception

Java Project Architecture spring-boot+mybatis+ Druid Connection Pool

Phenomenon

Throw Java.sql.SQLException:Incorrect String value: ' \xf0\x9f\x92\x94 ' for column ' name ' at row 1

Positioning

1, database fields, tables, databases, MySQL encoding needs to be set to UTF8MB4

2. Database Connection Setting code

Show variables like "%char%";

Resolution 1, SET database 1) Modify the field character set

TABLE  Change SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2) Setting the character set of the table

TABLE CONVERT  to SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3) Setting the character set of the database

DATABASE SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

4) Modify the database application character set

find the MySQL location under Linux $ whereis mysql          find location $  vi my.cnf  "There is My.ini, if only My-default.ini, copy one and name it My.ini" [ Add or modify the default character set for][client]# client source data-character-set = utf8mb4[mysqld]# Server default character set character-set-server= utf8mb4# Connection Layer default character set collation-server=utf8mb4_unicode_ci[mysql]# database default character Set defaults-character-set = UTF8MB4
$ Service mysqld Restart Restart services

2. Set the encoding

Entered on the command line, but this only works in the current session

Set character_set_database=utf8;set character_set_server=utf8;

3. Modify Connection Pool Properties

< Property name="connectioninitsqls" >    <list>        <value>set Names utf8mb4</value>    </list></Property >

Or

And

jdbc.url=jdbc:mysql://localhost:3306/database?useunicode=true&  Characterencoding=utf8&autoreconnect=true&rewritebatchedstatements=true
Special description of the Jdbc.url configuration: If you have upgraded the Mysql-connector, Characterencoding=utf8 can be automatically recognized as UTF8MB4 (and of course the original UTF8),
And the AutoReConnect configuration I strongly recommend matching, I just ignored this property, resulting from the cache because of the latest configuration, not read to the DB, resulting in the inability to use the UTF8MB4 character set, how painful the understanding!!

Pro-Test can

UTF8 and utf8mb4 Description:

Utf-8:unicode transformation Format-8bit, the BOM is allowed, but is usually not included in the BOM. is a multi-byte encoding used to solve the international character, which uses 8 bits (or one byte) in English, and Chinese uses 24 (three bytes) to encode.

UTF-8 contains the characters that all countries in the world need to use, and is an international code with strong versatility. UTF-8 encoded text can be displayed on browsers that support the UTF8 character set in each country. If it is UTF8 code, it can also display Chinese in the foreigner's English ie, they do not need to download IE's Chinese language support package.

Utf8mb4:mysql added UTF8MB4 code after 5.5.3, Mb4 is the most bytes 4 meaning, specifically for compatibility with four-byte Unicode.

Expression Store Exception--mybatis throws exception (Java.sql.SQLException:Incorrect string value: ' \xf0\x9f\x92\x94 ' for column ' name ' at row 1)