MySQL中导出CSV格式数据的SQL_MySQL

MySQL中导出CSV格式数据的SQL语句样本如下：

Sql代码
select * from test_info
into outfile &＃39;/tmp/test.csv&＃39;
fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39;;

select * from test_info
into outfile &＃39;/tmp/test.csv&＃39;
fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39;; MySQL中导入CSV格式数据的SQL语句样本如下：

Sql代码
load data infile &＃39;/tmp/test.csv&＃39;
into table test_info
fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39;;

load data infile &＃39;/tmp/test.csv&＃39;
into table test_info
fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39;; 里面最关键的部分就是格式参数

Sql代码
fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39;

fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39;
lines terminated by &＃39;/r/n&＃39; 这个参数是根据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, &＃39;hello, line
suped
seped
"
end&＃39;
);

select * from test_info;

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

delete from test_info;

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

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, &＃39;hello, line
suped
seped
"
end&＃39;
);

select * from test_info;

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

delete from test_info;

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

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 &＃39;/tmp/data.csv&＃39; $MYSQL_CSV_FORMAT;
# 在导入数据时使用：load data infile &＃39;/tmp/data.csv&＃39; into table ... $MYSQL_CSV_FORMAT;
# CSV标准文档：RFC 4180
MYSQL_CSV_FORMAT="fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;/"&＃39; escaped by &＃39;/"&＃39; lines terminated by &＃39;/r/n&＃39;"

#!/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 &＃39;/tmp/data.csv&＃39; $MYSQL_CSV_FORMAT;
# 在导入数据时使用：load data infile &＃39;/tmp/data.csv&＃39; into table ... $MYSQL_CSV_FORMAT;
# CSV标准文档：RFC 4180
MYSQL_CSV_FORMAT="fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;/"&＃39; escaped by &＃39;/"&＃39; lines terminated by &＃39;/r/n&＃39;"

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

Bash代码
#!/bin/sh

. /opt/shtools/commons/mysql.sh

# MYSQL_CSV_FORMAT="fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;/"&＃39; escaped by &＃39;/"&＃39; lines terminated by &＃39;/r/n&＃39;"
echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"

rm /tmp/test.csv

mysql -p --default-character-set=gbk -t --verbose test <
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, &＃39;hello, line
suped
seped
"
end&＃39;
);

select * from test_info;

-- select * from test_info into outfile &＃39;/tmp/test.csv&＃39; fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39; lines terminated by &＃39;/r/n&＃39;;
select * from test_info into outfile &＃39;/tmp/test.csv&＃39; $MYSQL_CSV_FORMAT;

delete from test_info;

-- load data infile &＃39;/tmp/test.csv&＃39; into table test_info fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39; lines terminated by &＃39;/r/n&＃39;;
load data infile &＃39;/tmp/test.csv&＃39; 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 &＃39;,&＃39; optionally enclosed by &＃39;/"&＃39; escaped by &＃39;/"&＃39; lines terminated by &＃39;/r/n&＃39;"
echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"

rm /tmp/test.csv

mysql -p --default-character-set=gbk -t --verbose test <

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, &＃39;hello, line
suped
seped
"
end&＃39;
);

select * from test_info;

-- select * from test_info into outfile &＃39;/tmp/test.csv&＃39; fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39; lines terminated by &＃39;/r/n&＃39;;
select * from test_info into outfile &＃39;/tmp/test.csv&＃39; $MYSQL_CSV_FORMAT;

delete from test_info;

-- load data infile &＃39;/tmp/test.csv&＃39; into table test_info fields terminated by &＃39;,&＃39; optionally enclosed by &＃39;"&＃39; escaped by &＃39;"&＃39; lines terminated by &＃39;/r/n&＃39;;
load data infile &＃39;/tmp/test.csv&＃39; into table test_info $MYSQL_CSV_FORMAT;

select * from test_info;

EOF

echo "===== content in /tmp/test.csv ====="
cat /tmp/test.csv

作者“wangqiaowqo”