MySQL中導出CSV格式數據的SQL

MySQL中導出CSV格式數據的SQL語句樣本如下:

Sql代碼 
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'
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'; MySQL中導入CSV格式數據的SQL語句樣本如下:

Sql代碼 
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 
fields terminated by ','  optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'; 裡面最關鍵的部分就是格式參數

Sql代碼 
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)字符串以半角雙引號包圍,字符串本身的雙引號用兩個雙引號表示。

 

文件:test_csv.sql

Sql代碼 
use test;  
 
create table 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';  
 
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';  
 
select * from test_info;  
 
  

use test;

create table 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';

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';

select * from test_info;

 


文件:test.csv

Text代碼 
2010,"hello, line  
suped  
seped  
"" 
end" 

2010,"hello, line
suped
seped
""
end"


在Linux下如果經常要進行這樣的導入導出操作,當然最好與Shell腳本結合起來,為瞭避免每次都要寫格式參數,可以把這個串保存在變量中,如下所示:(文件mysql.sh)

Bash代碼 
#!/bin/sh  
 
 
# Copyright (c) 2010 codingstandards. All rights reserved.  
# file: mysql.sh  
# description: Bash中操作MySQL數據庫  
# license: LGPL  
# author: codingstandards  
# email: codingstandards@gmail.com  
# version: 1.0 
# date: 2010.02.28 
 
 
# MySQL中導入導出數據時,使用CSV格式時的命令行參數  
# 在導出數據時使用:select … from … [where …] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;  
# 在導入數據時使用:load data infile '/tmp/data.csv' into table … $MYSQL_CSV_FORMAT;  
# CSV標準文檔:RFC 4180 
MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'" 

#!/bin/sh


# Copyright (c) 2010 codingstandards. All rights reserved.
# file: mysql.sh
# description: Bash中操作MySQL數據庫
# license: LGPL
# author: codingstandards
# email: codingstandards@gmail.com
# version: 1.0
# date: 2010.02.28


# MySQL中導入導出數據時,使用CSV格式時的命令行參數
# 在導出數據時使用:select … from … [where …] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;
# 在導入數據時使用:load data infile '/tmp/data.csv' into table … $MYSQL_CSV_FORMAT;
# CSV標準文檔:RFC 4180
MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"


 
使用示例如下:(文件test_mysql_csv.sh)

Bash代碼 
#!/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 

#!/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

作者“wangqiaowqo”
 

發佈留言