Special symbols stored in MySQL database times wrong: Incorrect string value: ' \xf0\x9f\x98\x84\xf0\x9f workaround

Problem description: From Sina Weibo crawl message saved to MySQL data, corresponding database field is varchar, character encoding utf-8. Partial insert succeeded, partial insert failed, error such as title.

In the online query, some people say that is the coding problem, suggest to modify the encoding format, such as change to Gbk,utf-8,blob, etc., but few people give a more detailed answer. In an English web site, only to find the cause of the real error. Link 1 Link 2

< Span style= "margin:0px; padding:0px; font-family:fangsong_gb2312; FONT-SIZE:18PX "> error Reason: we can see the character 0xf0 0x9F 0x98 0x84 in the error prompt, which corresponds to the 4-byte encoding in the UTF-8 encoding format (UTF-8 encoding specification). Normal Chinese characters generally don't exceed 3 bytes, why do they appear 4 bytes? In fact, it corresponds to the expression in the smart phone input method. Then why did you make an error? Because utf-8 in MySQL is not really the utf-8, it can only store the length of a byte utf-8 encoding, if you want to store 4 bytes must be used UTF8MB4 type.  mysql 5.5.3.

Solution:

1) using the UTF8MB4 data type

to use this strategy, if the MySQL version is less than 5.5.3, the version upgrade is done first, and then the corresponding data type is changed to the UTF8MB4 type. If you are using a connector/j connection database, you need to change the encoding format to UTF8MB4 (set character_set_server=utf8mb4 in the Connection config) in the configuration.

2) Custom filter rules that filter or convert the four-byte UTF-8 characters that appear in the text to a custom type.

Transferred from: http://blog.csdn.net/fuxuejun/article/details/20361669

Special symbols stored in MySQL database times wrong: Incorrect string value: ' \xf0\x9f\x98\x84\xf0\x9f workaround