问题再现 -- 创建数据库
create database if not exists testStudent;
-- 建学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 插入数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
出现错误:ERROR 1366 (HY000): Incorrect string value: '\xD5\xD4\xC0\xD7' for column 'student' at row 1
解决方案:
分别查看数据库、表、报错列字段的字符集,错误就是由于三者不一致造成的,设置一致.
首先查看一下Student数据表的字符集:
在mysql所在数据库输入命令行:
show create table Student;
如下:
可以发现,字段的字符集和数据表的字符集是不一致的。
把字段的字符集改成utf-8,与数据表一致,命令:
alter table Student change S_id S_id char(20) character set utf8;
alter table Student change S_name S_name char(20) character set utf8;
alter table Student change S_birth S_birth char(20) character set utf8;
alter table Student change S_sex S_sex char(20) character set utf8;
现在执行第3步的insert into 命令就可以了。
最后总结:
(注意:以下内容都需要首先进入Mysql客户端)
查看mysql的字符集(直接粘贴):
show variables where Variable_name like '%char%';
查看某一个数据库字符集:
show create database testStudent;#(注:testStudent为数据库)
查看某一个数据表字符集:
show create table Student;(注:Student为数据表)
查看mysql的字符集后会出现下面窗口
修改mysql的字符集:
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_filesystem=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
修改数据库的字符集:
alter database testStudent character set utf8;
修改数据表的字符集:
alter table Student character set utf8;
修改数据表字段的字符集:
alter table Student change S_name S_name varchar(20) character set utf-8;