MySQL中导入 导出CSV

来自：http://blog.csdn.net/sara_yhl/article/details/6850107

导出

select * from test_info   
into outfile '/tmp/test.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n'; 

导入

load data infile '/tmp/test.csv'   
into table test_info    
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n';   

其中关键参数是

fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n' 

这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式，其要点包括：

(1)字段之间以逗号分隔，数据行之间以\r\n分隔；

(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

 

shell 例子

#!/bin/sh  
  
. /opt/shtools/commons/mysql.sh  
  
# MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"  
echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"  
  
rm /tmp/test.csv  
  
mysql -p --default-character-set=gbk -t --verbose test <<EOF  
  
use test;  
  
create table if not exists test_info (  
    id  integer not null,  
    content varchar(64) not null,  
    primary key (id)  
);  
  
delete from test_info;  
  
insert into test_info values (2010, 'hello, line  
suped  
seped  
"  
end'  
);  
  
select * from test_info;  
  
-- select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
select * from test_info into outfile '/tmp/test.csv' $MYSQL_CSV_FORMAT;  
  
delete from test_info;  
  
-- load data infile '/tmp/test.csv' into table test_info fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
load data infile '/tmp/test.csv' into table test_info $MYSQL_CSV_FORMAT;  
  
select * from test_info;  
  
  
EOF  
  
echo "===== content in /tmp/test.csv ====="  
cat /tmp/test.csv