mysql命令行導入和導出數據

?首先打開命令窗口,輸入命令:mysql -h localhost -u selffabu -p

連接成功后,進行下面的操作

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

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：

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

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

里面最關鍵的部分就是格式參數

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

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"??

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:?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'"??

#!/bin/sh# Copyright (c) 2010 codingstandards. All rights reserved.
# 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'

?

轉自:http://blog.csdn.net/sara_yhl/article/details/6850107