bash mysql 导出csv_Bash中使用MySQL导入导出CSV格式数据

最新推荐文章于 2021-08-22 18:36:27 发布

Alex Cui

最新推荐文章于 2021-08-22 18:36:27 发布

阅读量169

点赞数

文章标签： bash mysql 导出csv

本文链接：https://blog.csdn.net/weixin_29442195/article/details/113716033

版权

本文介绍了如何在MySQL中使用SQL语句导出和导入CSV格式的数据，并给出了相应的示例代码。同时，展示了如何在Bash Shell脚本中创建变量以简化操作，以及提供了一个用于操作MySQL数据库的Bash脚本模板，方便进行CSV格式数据的导入导出操作。

摘要由CSDN通过智能技术生成

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

Java代码

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代码

loaddata infile'/tmp/test.csv'intotabletest_info fields terminatedby','optionally enclosedby'"'escapedby'"'lines terminatedby'\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 terminatedby','optionally enclosedby'"'escapedby'"'lines terminatedby'\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;

createtabletest_info (

idintegernotnull,

contentvarchar(64)notnull,

primarykey(id)

);

deletefromtest_info;

insertintotest_infovalues(2010, 'hello, line

suped

seped

end'

);

select*fromtest_info;

select*fromtest_infointooutfile'/tmp/test.csv'fields terminatedby','optionally enclosedby'"'escapedby'"'lines terminatedby'\r\n';

deletefromtest_info;

loaddata infile'/tmp/test.csv'intotabletest_info fields terminatedby','optionally enclosedby'"'escapedby'"'lines terminatedby'\r\n';

select*fromtest_info;

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, 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

# 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标准文档：RFC4180

MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"

#!/bin/sh

# 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 4180

MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"

使用示例如下：(文件test_mysql_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 <

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

分享到：

2010-09-17 00:52

分类:数据库