mysql添加语句出现报错ERROR 1366 (HY000)

mysql 报错ERROR 1366 (HY000)及解决办法

今日在mysql创建学生表后添加信息，发现出现如下报错：

ERROR 1366 (HY000): Incorrect string value: '\xE9\x92\xB1\xE7\x94\xB5' for column 'Sname' at row 1

于是查看了创建表的语句，发现默认字符集出现错误。

mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                            |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

default charset 是latin1格式，不是utf8格式。所以修改字符集的格式。

<mysql> alter table student default character set utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后再重新查看建表语句中的字符集格式

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                         |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `SId` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这样default charset 就是utf8了。
但是插入后又报同样的错误，返回来一看是表的default charset是utf8 但是列的仍然是latain。
所以挨个修改列的字段默认字符集。

student | CREATE TABLE `student` (
  `SId` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `Sage` datetime DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

alter table student change SId SId varchar(10) CHARACTER SET utf8;

alter table student change Sname Sname varchar(10) CHARACTER SET utf8;

alter table student change Ssex Sex varchar(10) CHARACTER SET utf8;

然后重新插入信息即可。

mysql> insert into Student values('04' , '李云' , '1990-12-06' , '男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('05' , '周梅' , '1991-12-01' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('09' , '张三' , '2017-12-20' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('10' , '李四' , '2017-12-25' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('11' , '李四' , '2012-06-06' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('12' , '赵六' , '2013-06-13' , '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Student values('13' , '孙七' , '2014-06-01' , '女');
Query OK, 1 row affected (0.00 sec)

所以是计算字段的字符集出现错误了。修改了就可以了。