摘要:解决csv文件向mysql导入含有中文数据,导入后中文出现乱码问题。结论,在导入含中文字符时注意两个问题:第一,告诉Mysql文件的编码是什么?第二,数据库表中的列编码要设置成支持中文的字符集。
导入源数据
SQL代码
LOAD DATA LOCAL INFILE 'E:\\stocks.csv'
INTO TABLE `stock_info_tb`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
乱码结果
修改SQL
LOAD DATA LOCAL INFILE 'E:\\stocks.csv'
INTO TABLE `stock_info_tb` CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
这个utf8是根据导入的csv文件编码来决定的,可以通过文本编辑器把要导入的数据修改一个自己喜欢的编码,然后这个一致就OK。
这样修改后,仍然是乱码,有些崩溃的。
查看与修改表信息
表对中文是否支持运行:
SHOW FULL COLUMNS FROM `stock_info_tb`;
或
SHOW CREATE TABLE `stock_info_tb`;
结果显示:
CREATE TABLE `stock_info_tb` (
`type` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`bourse` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
`stock_id` int(11) NOT NULL,
`stock_name` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`stock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
发现要导入来的type,stock_name两个列为latin1字符集,修改为utf8为:
/*DDL 信息*/------------
CREATE TABLE `stock_info_tb` (
`type` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`bourse` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
`stock_id` int(11) NOT NULL,
`stock_name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`stock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
再运行
LOAD DATA LOCAL INFILE 'E:\\stocks.csv'
INTO TABLE `stock_info_tb` CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
结果
喜欢这个样的结果。
【作者:happyprince, http://blog.csdn.net/ld326/article/details/78123282】