學習 MySQL中匯入 匯出CSV

 

MySQL中匯出CSV格式資料的SQL語句樣本如下：

Sql代碼  

1. select * from test_info   
2. into outfile '/tmp/test.csv'   
3. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
4. 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代碼  

1. load data infile '/tmp/test.csv'   
2. into table test_info    
3. fields terminated by ','  optionally enclosed by '"' escaped by '"'   
4. 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代碼  

1. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
2. 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代碼  

1. use test;  
2.   
3. create table test_info (  
4.     id  integer not null,  
5.     content varchar(64) not null,  
6.     primary key (id)  
7. );  
8.   
9. delete from test_info;  
10.   
11. insert into test_info values (2010, 'hello, line  
12. suped  
13. seped  
14. "  
15. end'  
16. );  
17.   
18. select * from test_info;  
19.   
20. select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
21.   
22. delete from test_info;  
23.   
24. load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
25.   
26. select * from test_info;  
27.   
28.    

use test;create table test_info (id integernot null,content varchar(64) not null,primary key (id));delete from test_info;insert into test_info values (2010, 'hello, linesupedseped"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代碼  

1. 2010,"hello, line  
2. suped  
3. seped  
4. ""  
5. end"  

2010,"hello, linesupedseped""end"

 

在Linux下如果經常要進行這樣的匯入匯出操作，當然最好與Shell指令碼結合起來，為了避免每次都要寫格式參數，可以把這個串儲存在變數中，如下所示：(檔案mysql.sh)

Bash代碼  

1. #!/bin/sh  
2.   
3.   
4. # Copyright (c) 2010 codingstandards. All rights reserved.  
5. # file: mysql.sh  
6. # description: Bash中操作MySQL資料庫  
7. # license: LGPL  
8. # author: codingstandards  
9. # email: codingstandards@gmail.com  
10. # version: 1.0  
11. # date: 2010.02.28  
12.   
13.   
14. # MySQL中匯入匯出資料時，使用CSV格式時的命令列參數  
15. # 在匯出資料時使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;  
16. # 在匯入資料時使用：load data infile '/tmp/data.csv' into table ... $MYSQL_CSV_FORMAT;  
17. # CSV標準文檔：RFC 4180  
18. 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 4180MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"

 

使用樣本如下：(檔案test_mysql_csv.sh)

Bash代碼  

1. #!/bin/sh  
2.   
3. . /opt/shtools/commons/mysql.sh  
4.   
5. # MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"  
6. echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"  
7.   
8. rm /tmp/test.csv  
9.   
10. mysql -p --default-character-set=gbk -t --verbose test <<EOF  
11.   
12. use test;  
13.   
14. create table if not exists test_info (  
15.     id  integer not null,  
16.     content varchar(64) not null,  
17.     primary key (id)  
18. );  
19.   
20. delete from test_info;  
21.   
22. insert into test_info values (2010, 'hello, line  
23. suped  
24. seped  
25. "  
26. end'  
27. );  
28.   
29. select * from test_info;  
30.   
31. -- select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
32. select * from test_info into outfile '/tmp/test.csv' $MYSQL_CSV_FORMAT;  
33.   
34. delete from test_info;  
35.   
36. -- load data infile '/tmp/test.csv' into table test_info fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
37. load data infile '/tmp/test.csv' into table test_info $MYSQL_CSV_FORMAT;  
38.   
39. select * from test_info;  
40.   
41.   
42. EOF  
43.   
44. echo "===== content in /tmp/test.csv ====="  
45. 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.csvmysql -p --default-character-set=gbk -t --verbose test <<EOFuse test;create table if not exists test_info (id integernot null,content varchar(64) not null,primary key (id));delete from test_info;insert into test_info values (2010, 'hello, linesupedseped"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;EOFecho "===== content in /tmp/test.csv ====="cat /tmp/test.csv