mysql导入一个csv_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 <

select * from INFORMATION_SCHEMA.`KEY_COLUMN_USAGE`;

EOF