完美解决 爬虫时遇到的Incorrect string value: '\xF0\x9F\x98\xAD",...' for column 'commentContent' at row 1 问题

完美解决 爬虫时遇到的Incorrect string value: ‘\xF0\x9F\x98\xAD",…’ for column ‘commentContent’ at row 1 问题

---

最近在学习爬虫时，向数据库(mysql)存储数据时报错：*** ' \xF0\x9F\x98\xAD",...' for column 'commentContent' at row 1，***

分析：

爬虫爬取到的数据中含有表情，而表情一般都是四字节Unicode编码，而数据库设置的编码为utf8,`mysql中utf8编码最大长度为3字节，遇到4字节编码时就会报错`，也就是我们遇到的错误。

解决办法：

• 修改数据库编码

1.查看当前数据库编码

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

2.找到数据库的配置文件，windows 下一般为my-default.ini,在该文件下边添加下面几行代码.

[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_general_ci    

3重新启动mysql

net stop mysql
net start mysql

4查看当前数据库编码

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb4                                                 |
| character_set_connection | utf8mb4                                                 |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb4                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

如果配置修改无效，可尝试下边方法。

• 将配置文件另存为一份my.ini文件，放到mysql的根目录下。

• 以管理员身份运行CMD，关闭数据库服务（net stop mysql）

• 执行sc delete MYSQL ，

• 执行mysqld install MYSQL –defaults-file=”C:\mysql-5.7.17-winx64\my.ini”

• 重启mysql服务