共计 4900 个字符，预计需要花费 13 分钟才能阅读完成。
MySQL 中怎么导出 CSV 格式数据，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
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:
# 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:
# 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__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
看完上述内容，你们掌握 MySQL 中怎么导出 CSV 格式数据的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
