Mysql 字符集报错 Incorrect string value

报错信息：

Incorrect string value: '\xF0\x9F\x98\x9D\xE6\xB5...' for column 'nickname' at row 1

原因分析

utf8字符集本身没有问题，但是mysql的utf8不是真正的utf8，只支持最多3个字节的字符（也就是说mysql的utf8只支持部分utf8字符），而表情符号占四个字节，因此报错。

问题复现：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.03 sec)

mysql>
mysql>
mysql> create database if  not exists db_com;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| db_com             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use db_com;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db_com     |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> create table user (id int primary key,name varchar(30) unique not null,xi
ngbie varchar(30) not null,score float default 0);
Query OK, 0 rows affected (0.12 sec)

mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   | UNI | NULL    |       |
| xingbie | varchar(30) | NO   |     | NULL    |       |
| score   | float       | YES  |     | 0       |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table modify score float not null default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'score
 float not null default 0' at line 1
mysql> alter table user change score score float not null default 0;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   | UNI | NULL    |       |
| xingbie | varchar(30) | NO   |     | NULL    |       |
| score   | float       | NO   |     | 0       |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into user (name,xingbie,score) values ('张三','男',89),('李四','男
',99),('小红','女',100),('嚣张','男',150);
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for colum
n 'name' at row 1

解决方案

第一步，修改字符集

将字符集换成 utf8mb4 即可，对mysql来说utf8mb4才是真正的utf8, 支持4个字节的字符，也兼容mysql的自己的utf8 。

执行如下语句：该语句直接将表的字符集设置为utf8mb4，并将表中字符类型的字段也转换为utf8mb4。

ALTER TABLE `user_info` CONVERT TO CHARACTER SET  utf8mb4;  

如果使用如下语句：则只是将表的字符集设置为utf8mb4，而表中的字段可能还是utf8（由于表中的字段可以单独指定字符集的）。

ALTER TABLE `block_template` CHARSET=utf8mb4;

表设置字符集为utf8mb4的作用：为该表创建字段时的默认字符集直接继承表的字符集utf8mb4，不需要指定，除非你想用不同的字符集。

ps：建议数据库建立时直接指定utf8mb4字符集，这样建表和字段时可以不再指定字符集（默认直接使用所在数据库的字符集）

CREATE database testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

题外话：COLLATE用于设置排序规则，“_ci”后缀的表示忽略大小写（Case Insensitive），“_cs”后缀的大小写敏感（Case Sensitive），在排序和比较大小等操作时会用到。
查看所有utf8mb4能够使用的排序规则:SHOW COLLATION LIKE 'utf8mb4%';。

第二步，设置连接使用的字符集

修改后发现还是报错，原因是客户端连接相关的字符集配置不对：

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'

1
2

在这里插入图片描述
设置字符集：

SET NAMES utf8mb4;
SHOW VARIABLES LIKE 'character%';

设置之后再查看发现变成 utf8mb4 了：

在这里插入图片描述

SET NAMES utf8mb4 相当于如下三个语句：
SET character_set_client ='utf8mb4'; #客户端字符集
SET character_set_connection ='utf8mb4'; #客户端与服务器端连接采用的字符集
SET character_set_results ='utf8mb4'; #查询的结果集使用的字符集，如果不是utf8mb4时查询结果可能会显示乱码

一般来说，
为了插入的数据表以正确的编码入表，character_set_client、character_set_connection、character_set_database 这三个字符集要一致；
为了查询结果集不出现乱码character_set_results也要与上面三个选项保持一致。
character_set_database 是在创建数据库时决定的。

通过sql客户端连接时设置编码

“SET NAMES”配置只对当前session有效，因此每次连接都需要去SET，可以在客户端连接时自动做初始化，以SQLyog为例：

jdbc连接时设置编码

加上连接参数即可：jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8
注意这里没有“utf8mb4”,设置为“utf8”即对应MySQL的utf8mb4
在服务端设置连接编码my.ini

可以直接在MySQL服务端配置my.ini，配置好重启服务即可，客户端连接时不用再指定编码：

[mysqld]
#character-set-client-handshake = FALSE 这个表示跳过客户端设置，直接使用character-set-server，即就算客户端带上了“characterEncoding=xxx”连接参数，也会被忽略
character-set-client-handshake = FALSE
character-set-server = utf8mb4

下面两个选项在 mysql_5.7.26 貌似无效：

[client]
default-character-set = utf8mb4 此处设置无效
[mysql]default-character-set = utf8mb4 此处设置无效

                        版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/iteye_19045/article/details/95949325