今天在爬虫资源时,在执行一半的时候报错了。报错内容如下:
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;
4.重启mysql
service mysqld restart