mysql 插入中文数据乱码

1、新建表grade，有一属性classname，通过insert添加数据时 

mysql> insert into grade(classname,user_id) values('2班',1);

ERROR 1366 (HY000): Incorrect string value: '\xE7\x8F\xAD' for column 'classname' at row 1

看到这里大家应该知道是编码问题了

2、查看表中对应字段的编码

mysql> show full columns from grade（grade是表名）

+-----------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

 

| Field     | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |

 

+-----------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

 

| id        | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |

 

| classname | varchar(100) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references |         |

 

| user_id   | int(11)      | NULL              | NO   |     | NULL    |                | select,insert,update,references |         |

 

+-----------+--------------+-------------------+------+-----+---------+----------------+---

------------------------------+---------+

看到classname属性的编码不是utf8

3、修改classname属性的值

mysql> alter table grade change classname classname varchar(100) character set utf8;

Query OK, 0 rows affected (0.21 sec)

Records: 0  Duplicates: 0  Warnings: 0

再查看一下是否修改

mysql> show full columns from grade;

+-----------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

 

| Field     | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |

 

+-----------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

 

| id        | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |

 

| classname | varchar(100) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |

 

| user_id   | int(11)      | NULL            | NO   |     | NULL    |                | select,insert,update,references |         |

 

+-----------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

可以看到collation属性的值已经修改

4、再次插入1中数据

mysql> insert into grade(classname,user_id) values('2班',1);

Query OK, 1 row affected (0.04 sec)

 

mysql> select * from grade;

+----+-----------+---------+

 

| id | classname | user_id |

 

+----+-----------+---------+

 

|  1 | 2班       |       1 |

 

+----+-----------+---------+

1 row in set (0.00 sec)

执行成功