MySQL中怎么導(dǎo)出CSV格式數(shù)據(jù)，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

成都一家集口碑和實(shí)力的網(wǎng)站建設(shè)服務(wù)商，擁有專業(yè)的企業(yè)建站團(tuán)隊(duì)和靠譜的建站技術(shù)，10余年企業(yè)及個(gè)人網(wǎng)站建設(shè)經(jīng)驗(yàn) ,為成都數(shù)千家客戶提供網(wǎng)頁設(shè)計(jì)制作,網(wǎng)站開發(fā),企業(yè)網(wǎng)站制作建設(shè)等服務(wù),包括成都營銷型網(wǎng)站建設(shè)，品牌網(wǎng)站建設(shè)，同時(shí)也為不同行業(yè)的客戶提供成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作的服務(wù),包括成都電商型網(wǎng)站制作建設(shè),裝修行業(yè)網(wǎng)站制作建設(shè)，傳統(tǒng)機(jī)械行業(yè)網(wǎng)站建設(shè),傳統(tǒng)農(nóng)業(yè)行業(yè)網(wǎng)站制作建設(shè)。在成都做網(wǎng)站,選網(wǎng)站制作建設(shè)服務(wù)商就選創(chuàng)新互聯(lián)公司。

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

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語句樣本如下：

Sql代碼 
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 
fields terminated by ','  optionally enclosed by '"' escaped by '"'
lines terminated by '\r\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é)合起來，為了避免每次都要寫格式參數(shù)，可以把這個(gè)串保存在變量中，如下所示：(文件mysql.sh)

Bash代碼 
#!/bin/sh  
 
 
# Copyright (c) 2010 codingstandards. All rights reserved.  
# file: mysql.sh  
# description: Bash中操作MySQL數(shù)據(jù)庫  
# license: LGPL  
# author: codingstandards  
# email:   
# 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ù)庫
# license: LGPL
# author: codingstandards
# email:
# 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'"

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

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

看完上述內(nèi)容，你們掌握MySQL中怎么導(dǎo)出CSV格式數(shù)據(jù)的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

分享題目：MySQL中怎么導(dǎo)出CSV格式數(shù)據(jù)
瀏覽路徑：http://chinadenli.net/article34/gsgjse.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營銷、網(wǎng)站改版、搜索引擎優(yōu)化、定制網(wǎng)站、網(wǎng)頁設(shè)計(jì)公司、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)