问题
- 加载含有中文字符的数据到表中出错
mysql> CREATE TABLE food(
-> id INT(10) PRIMARY KEY AUTO_INCREMENT NOT NULL,
-> NAME VARCHAR(20) NOT NULL,
-> company VARCHAR(30) NOT NULL,
-> price FLOAT,
-> produce_time YEAR,
-> validity_time INT(4),
-> address VARCHAR(50)
-> );
Query OK, 0 rows affected (0.07 sec)mysql> INSERT INTO food(id,NAME,company,price,produce_time,validity_time,address) VALUES(4,'FF咖啡','FF咖啡厂',20,2002,5,'天津'); ERROR 1366 (HY000): Incorrect string value: '\xE5\x92\x96\xE5\x95\xA1' for column 'NAME' at row 1
原因
- mysql默认编码latin1是不支持中文字符的，所以报错。
解决
- 通过设置编码字符为utf8，从而支持中文字符。
1、查看当前库默认编码格式
mysql> show variables like '%char%'; +--------------------------------------+----------------------------+ | Variable_name | Value | +--------------------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | validate_password_special_char_count | 1 | +--------------------------------------+----------------------------+ 9 rows in set (0.00 sec)
2、将character_set_database和character_set_server设置为utf8
mysql> set character_set_database=utf8; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> set character_set_server=utf8; Query OK, 0 rows affected (0.00 sec)
3、删除数据库并新建数据库，并切到该数据库下
- 因为只有在编码格式改变以后，再建库才会生效。
drop database student; create database student; use student;
验证
建表、添加数据
mysql> create table Student(
-> Sno int not null auto_increment primary key,
-> Sname varchar(10) not null,
-> Sex char(1) not null,
-> Sage tinyint(100) not null,
-> Sdept char(4) not null)comment = '学生表';
Query OK, 0 rows affected (0.03 sec)mysql> insert into Student values ( 1,'李勇', '男', 20, 'CS'); Query OK, 1 row affected (0.02 sec)
ok,成功解决
总结
如果某表中需要添加中文字符时需要在其库之前将character_set_database和character_set_server设置为utf8
set character_set_database=utf8; set character_set_server=utf8;
然后再建库，建表插入中文字符数据
