MySQL表插入中文错误：ERROR 1366 (HY000): Incorrect string value: '\xE8\x87\xAA\xE5\x8A\xA8...'-优快云博客

当尝试在MySQL中插入中文数据时，出现ERROR 1366 (HY000)错误，原因是数据库和表的字符集设置不支持UTF8。解决方法包括检查并修改数据库及表的默认字符集到UTF8，以及调整字段的字符集设置。执行相应SQL语句进行修改后，可以成功插入中文内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

插入语句

insert into ops_user(username,password,duties,age,gender,entry_time) values('beckham','123456','自动化运维','43','男','20190313');

错误信息

ERROR 1366 (HY000): Incorrect string value: '\xE8\x87\xAA\xE5\x8A\xA8...' for column 'duties' at row 1

原因数据库以及表再创建的时候默认为latin1字符集

查看数据库字符集语句

show create database devops;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| devops   | CREATE DATABASE `devops` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

修改默认字符集为utf8

alter database devops character set utf8;
Query OK, 1 row affected (0.00 sec)

show create database devops;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| devops   | CREATE DATABASE `devops` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

查看表字符集语句

show create table ops_user;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                         |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ops_user | CREATE TABLE `ops_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(20) DEFAULT NULL,
  `duties` varchar(20) DEFAULT NULL,
  `age` char(20) DEFAULT NULL,
  `gender` char(20) DEFAULT NULL,
  `entry_time` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改默认字符集

alter table ops_user character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

show create table ops_user;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ops_user | CREATE TABLE `ops_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `password` char(20) CHARACTER SET latin1 DEFAULT NULL,
  `duties` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `age` char(20) CHARACTER SET latin1 DEFAULT NULL,
  `gender` char(20) CHARACTER SET latin1 DEFAULT NULL,
  `entry_time` char(20) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改字段字符集

alter table ops_user change username username varchar(20) character set utf8;
alter table ops_user change duties duties char(20) character set utf8;
alter table ops_user change gender gender char(20) character set utf8;

再次插入

insert into ops_user(username,password,duties,age,gender,entry_time) values('beckham','123456','自动化运维','43','男','20190313');
Query OK, 1 row affected (0.00 sec)