mysql导入csv文件

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

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

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

2、登录mysql执行下面命令

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一些方便语句

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