【错误】]java.sql.SQLException: Incorrect string value: 'xF0x9Fx98xB3...' for column 'XXX'-CSDN博客

本文链接：https://blog.csdn.net/shangrila_kun/article/details/89220249

今天在爬虫资源时，在执行一半的时候报错了。报错内容如下：

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xB3</...' for column 'news_content' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) ~[mysql-connector-java-5.1.45.jar:5.1.45]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) [druid-1.1.9.jar:1.1.9]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) [druid-1.1.9.jar:1.1.9]
	at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181]

本次爬虫出错的网址为：http://www.wangqianfang.cn/14947.html 大家可以尝试下是否会报错。

** 经过查阅资料，网上给出的方案是：**

普通的字符串或者表情都是占位3个字节，所以utf8足够用了，但是移动端的表情符号占位是4个字节，普通的utf8就不够用了，为了应对无线互联网的机遇和挑战、避免 emoji 表情符号带来的问题、涉及无线相关的 MySQL 数据库建议都提前采用 utf8mb4 字符集，这必须要作为移动互联网行业的一个技术选型的要点
Mysql 版本的限制，Mysql 5.5.3之前的版本，支持的utf8为3字节的，Mysql 5.5.3之后的版本支持utf8mb4。

我使用的是linux上安装的mysql，更改字符集的操作如下:

mysql -uroot -p
输入密码即可

查看字符集类型，下面是我的数据库的字符集
show variables like '%character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

更改字符集

 /*数据库客户端的编码*/
 set character_set_client = utf8mb4;
 /*建立连接使用的编码*/
 set character_set_connection = utf8mb4;
 /*数据库的编码*/
 set character_set_database = utf8mb4;
 /*结果集的编码*/
 set character_set_results = utf8mb4;
 /*数据库服务器的编码*/
 set character_set_server=utf8mb4;