Bash中应用MySQL导入导出CSV格式数据

Bash中使用mysql导入导出CSV格式数据

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

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

欢迎大家阅读《Bash中应用MySQL导入导出CSV格式数据》,跪求各位点评，by 搞代码

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

load data infile '/tmp/test.csv'  into table test_info   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

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

2010,"hello, line suped seped "" end" 

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

#!/bin/sh   # Copyright (c) 2010 codingstandards. All rights reserved. # file: mysql.sh # description: Bash中操作MySQL数据库 # license: LGPL # author: codingstandards # email: [email protected] # 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)

#!/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  

喜欢 (0)赏

[搞代码]

分享 (0)