MySQL中怎么导出CSV格式数据
MySQL中怎么导出CSV格式数据，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
成都一家集口碑和实力的网站建设服务商，拥有专业的企业建站团队和靠谱的建站技术，10余年企业及个人网站建设经验 ,为成都数千家客户提供网页设计制作,网站开发,企业网站制作建设等服务,包括成都营销型网站建设，品牌网站建设，同时也为不同行业的客户提供成都网站设计、网站制作的服务,包括成都电商型网站制作建设,装修行业网站制作建设，传统机械行业网站建设,传统农业行业网站制作建设。在成都做网站,选网站制作建设服务商就选创新互联公司。
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格式数据的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注创新互联行业资讯频道，感谢各位的阅读！
分享题目：MySQL中怎么导出CSV格式数据
浏览路径：https://www.cdcxhl.com/article34/gsgjse.html
成都网站建设公司_创新互联，为您提供网站营销、网站改版、搜索引擎优化、定制网站、网页设计公司、网站策划
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联
- 网站导航菜单教程网站顶部导航栏设计 2021-06-10
- 设计网站导航的基本原则 2022-05-26
- 网站导航的作用 2021-12-03
- 网站导航优化有哪些注意事项？ 2015-12-20
- 网站导航的类型都有哪些？ 2020-07-12
- 掌握以下7个知识点能让你的网站导航变得更加优秀 2021-04-03
- 如何改进网站导航？学习这7个必要的最佳实践！ 2016-08-02
- 谈网站导航的简洁设计 2016-11-03
- 如何为网站导航做SEO优化呢 2014-07-13
- 【SEO优化】网站导航系统的优化方法 2022-05-02
- 企业网站导航栏目有哪些 2021-05-19
- 从SEO角度来谈网站导航页设计 2022-09-01