mysql命令行导入和导出数据

 首先打开命令窗口,输入命令:mysql -h localhost -u selffabu -p

连接成功后,进行下面的操作

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';

MySQL中导入CSV格式数据的SQL语句样本如下,要导入的文件编码格式是UTF-8：

Sql代码

load data local infile '/tmp/test.csv' 
into table test_info  
fields terminated by ','  optionally enclosed by '"' escaped by '"' 
lines terminated by '\n';

里面最关键的部分就是格式参数

[sql]

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;

 

文件：test.csv

[Text]

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: [e-mail]
# 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'