mysql导入csv文件

前景：有大量的csv数据重oracle导出字符编码gb2312要导入mysql

1、shell先修改csv文本编码格式

?

1	iconv -f GB18030 -t utf-8 "$gb2312file" > "$utf8file"    #gb18030是2312的一个超集

2、登录mysql执行下面命令

?

1 2 3 4 5 6 7 8

LOAD DATA INFILE '/tmp/file.csv' INTO TABLE t1 CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'   (1)字段之间以逗号分隔，数据行之间以\r\n分隔； (2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示

3、mysql一些方便语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

csv导出 select * from test_info   into outfile '/tmp/test.csv'   fields terminated by ',' optionally enclosed by '"' escaped by '"'   lines terminated by '\r\n';   csv导入 load data infile '/tmp/test.csv'   into table test_info    fields terminated by ','  optionally enclosed by '"' escaped by '"'   lines terminated by '\r\n';     mysql快速执行脚本 mysql -h 172.21.210.24 -P 4000 -u root -D rkw_ezc_prod -ppasswd <<EOF  select * from INFORMATION_SCHEMA.`KEY_COLUMN_USAGE`; EOF