学习 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 'rn';

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

里面最关键的部分就是格式参数

Sql代码 

1. fields terminated by ',' optionally enclosed by '"' escaped by '"'
2. lines terminated by 'rn'

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

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

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

文件：test_csv.sql

Sql代码 

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

文件：test.csv

Text代码 

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

在Linux下如果经常要进行这样的导入导出操作，当然最好与Shell脚本结合起来，为了避免每次都要写格式参数，可以把这个串保存在变量中，如下所示：(文件mysql.sh)

Bash代码 

1. #!/bin/sh
2. # Copyright (c) 2010 codingstandards. All rights reserved.
3. # file: mysql.sh
4. # description: Bash中操作MySQL数据库
5. # license: LGPL
6. # author: codingstandards
7. # email: codingstandards@gmail.com
8. # version: 1.0
9. # date: 2010.02.28
10. # MySQL中导入导出数据时，使用CSV格式时的命令行参数
11. # 在导出数据时使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;
12. # 在导入数据时使用：load data infile '/tmp/data.csv' into table ... $MYSQL_CSV_FORMAT;
13. # CSV标准文档：RFC 4180
14. MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn'"

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

Bash代码 

1. #!/bin/sh
3. . /opt/shtools/commons/mysql.sh
5. # MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn'"
6. echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"
8. rm /tmp/test.csv
10. mysql -p --default-character-set=gbk -t --verbose test <<EOF
12. use test;
14. create table if not exists test_info (
15. id  integer not null,
16. content varchar(64) not null,
17. primary key (id)
18. );
20. delete from test_info;
22. insert into test_info values (2010, 'hello, line
23. suped
24. seped
25. "
26. end'
27. );
29. select * from test_info;
31. -- select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn';
32. select * from test_info into outfile '/tmp/test.csv' $MYSQL_CSV_FORMAT;
34. delete from test_info;
36. -- load data infile '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by 'rn';
37. load data infile '/tmp/test.csv' into table test_info $MYSQL_CSV_FORMAT;
39. select * from test_info;
42. EOF
44. echo "===== content in /tmp/test.csv ====="
45. cat /tmp/test.csv