数据库中插入中文的时候报错ERROR 1366 (HY000): Incorrect string value: '\xE4\xB8\xAD\xE9\xAB\x98...' for column 'Cname' at row 1

在我们插入数据中有中文的时候会报错如下：

root@leadchina 07:43:32->INSERT INTO student VALUES(0001,'宏志','男',30,'计算机网络');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xAE\x8F\xE5\xBF\x97' for column 'Sname' at row 1

方法一：更改表的字符集格式

root@leadchina 04:35:11->set names latin1;
Query OK, 0 rows affected (0.00 sec)

root@leadchina 04:35:25->insert into school(name,age,dept) values('胡辉',15,'数学');
Query OK, 1 row affected (0.00 sec)

这种问题是由于我们在创建table的时候默认的编码格式是latin1,如下：

show create table SC;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SC    | CREATE TABLE `SC` (
  `SCid` int(12) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `Cno` int(10) NOT NULL COMMENT '课程号',
  `Sno` int(10) NOT NULL COMMENT '学号',
  `Grade` tinyint(2) NOT NULL COMMENT '分数',
  PRIMARY KEY (`SCid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1          |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时我们需要通过一下命令修改编码格式

 

alter table SC  default character set utf8;

再去插入之前的中文还是一会提示同样的错误

root@leadchina 07:48:47->show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `Sno` int(10) NOT NULL COMMENT '学号',
  `Sname` char(16) CHARACTER SET latin1 NOT NULL COMMENT '学号',
  `Ssex` char(2) CHARACTER SET latin1 NOT NULL COMMENT '性别',
  `Sege` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  `Sdept` varchar(16) CHARACTER SET latin1 DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (`Sno`),
  KEY `INDEX_NAME` (`Sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8               |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时会发现虽然当前表的默认字段已经修改为utf8,但是字符串格式的

 方法三：

首先在系统中创建tablesname.sql的文件 将所要添加的内容加入进去

set names latin1;
insert into school(name,age,dept) values('胡辉11',15,'数学22');

在使用source将文件导入进去就可以了

root@leadchina 08:57:14->source /root/school.sql;

但是方法一和方法三当退出mysql后再次查看后还是乱码

root@leadchina 09:00:39->select * from school;
+----+-----------------+-----+-----------------+
| id | name            | age | dept            |
+----+-----------------+-----+-----------------+
|  1 | test1           |  10 | shuxue          |
|  2 | test1           |  10 | shuxue          |
|  3 | test2           |  19 | huaxue          |
|  4 | test3           |  15 | huaxue          |
|  5 | 胡辉          |  15 | 数学          |
|  6 | 胡辉11        |  15 | 数学22        |
+----+-----------------+-----+-----------------+

要想永久生效，需要在mysql的配置my.cnf  在[client]模块中加入：default-character-set=latin1,在mysqld模块中增加

character-set-server=latin1

 

 最好是将库设置为utf-8的格式