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

連接成功后,進(jìn)行下面的操作

MySQL中導(dǎo)出CSV格式數(shù)據(jù)的SQL語(yǔ)句樣本如下：

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中導(dǎo)入CSV格式數(shù)據(jù)的SQL語(yǔ)句樣本如下,要導(dǎo)入的文件編碼格式是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';

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

里面最關(guān)鍵的部分就是格式參數(shù)

[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'?

這個(gè)參數(shù)是根據(jù)RFC4180文檔設(shè)置的，該文檔全稱Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中詳細(xì)描述了CSV格式，其要點(diǎn)包括：

(1)字段之間以逗號(hào)分隔，數(shù)據(jù)行之間以\r\n分隔；

(2)字符串以半角雙引號(hào)包圍，字符串本身的雙引號(hào)用兩個(gè)雙引號(hào)表示。

?

文件：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下如果經(jīng)常要進(jìn)行這樣的導(dǎo)入導(dǎo)出操作，當(dāng)然最好與Shell腳本結(jié)合起來(lái)，為了避免每次都要寫(xiě)格式參數(shù)，可以把這個(gè)串保存在變量中，如下所示：(文件mysql.sh)

Bash代碼

#!/bin/sh??

??

??

#?Copyright?(c)?2010?codingstandards.?All?rights?reserved.??

#?file:?mysql.sh??

#?description:?Bash中操作MySQL數(shù)據(jù)庫(kù)??

#?license:?LGPL??

#?author:?codingstandards??

#?email:?codingstandards@gmail.com??

#?version:?1.0??

#?date:?2010.02.28??

??

??

#?MySQL中導(dǎo)入導(dǎo)出數(shù)據(jù)時(shí)，使用CSV格式時(shí)的命令行參數(shù)??

#?在導(dǎo)出數(shù)據(jù)時(shí)使用：select?...?from?...?[where?...]?into?outfile?'/tmp/data.csv'?$MYSQL_CSV_FORMAT;??

#?在導(dǎo)入數(shù)據(jù)時(shí)使用：load?data?infile?'/tmp/data.csv'?into?table?...?$MYSQL_CSV_FORMAT;??

#?CSV標(biāo)準(zhǔn)文檔：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數(shù)據(jù)庫(kù) # license: LGPL # author: codingstandards # email: codingstandards@gmail.com # version: 1.0 # date: 2010.02.28# MySQL中導(dǎo)入導(dǎo)出數(shù)據(jù)時(shí)，使用CSV格式時(shí)的命令行參數(shù) # 在導(dǎo)出數(shù)據(jù)時(shí)使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT; # 在導(dǎo)入數(shù)據(jù)時(shí)使用：load data infile '/tmp/data.csv' into table ... $MYSQL_CSV_FORMAT; # CSV標(biāo)準(zhǔn)文檔：RFC 4180 MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'

?

轉(zhuǎn)自:http://blog.csdn.net/sara_yhl/article/details/6850107

總結(jié)

以上是生活随笔為你收集整理的mysql命令行导入和导出数据的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。