MySQL插中文报错“ERROR 1366 (HY000): Incorrect string value: ‘\xE7\xAC...”修改MySQL字符集,数据库字符集,表的字符集,表中字段的字符集

问题：在MySQL中建中文名的表，并向其中插入数据时出现了问题：

mysql> create table 商品 (商品编码 int primary key,商品名称 char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into 商品 values(1,'耳机');
ERROR 1366 (HY000): Incorrect string value: '\xE8\x80\xB3\xE6\x9C\xBA' for column '商品名称' at row 1

解决方法：

1、修改MySQL的字符集

（1）、查看MySQL的字符集：

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
（2）、修改MySQL的字符集：

在/etc/my.cnf文件中添加：character_set_server=utf8

重启MySQL：systemctl restart mysqld.service

再次查询字符集：

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

2、修改表所在数据库的字符集：

mysql> alter database test1 character set 'utf8';
Query OK, 1 row affected (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

3、修改表的字符集：

查看表的字符集：

mysql> alter table 商品 character set 'utf8';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table 商品;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 商品   | CREATE TABLE `商品` (
  `商品编码` int(11) NOT NULL,
  `商品名称` char(10) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`商品编码`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8               |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、插入中文数据

如果直接插入数据还是报错，可以把原表删除，重新建表进行插入

mysql> insert into 商品 values(1,'耳机');
ERROR 1366 (HY000): Incorrect string value: '\xE8\x80\xB3\xE6\x9C\xBA' for column '商品名称' at row 1
mysql>
mysql> drop table 商品;
Query OK, 0 rows affected (0.01 sec)

mysql> create table 商品 (商品编码 int primary key,商品名称 char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into 商品 values(1,'耳机');
Query OK, 1 row affected (0.00 sec)