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)