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

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

MySQL中導出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中導入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'; 里面最關鍵的部分就是格式參數(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' 這個參數(shù)是根據(jù)RFC4180文檔設置的，該文檔全稱Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中詳細描述了CSV格式，其要點包括：

(1)字段之間以逗號分隔，數(shù)據(jù)行之間以\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;  
 
  

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)常要進行這樣的導入導出操作，當然最好與Shell腳本結合起來，為了避免每次都要寫格式參數(shù)，可以把這個串保存在變量中，如下所示：(文件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中導入導出數(shù)據(jù)時，使用CSV格式時的命令行參數(shù)  
# 在導出數(shù)據(jù)時使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;  
# 在導入數(shù)據(jù)時使用：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數(shù)據(jù)庫
# license: LGPL
# author: codingstandards
# email:
# version: 1.0
# date: 2010.02.28

# MySQL中導入導出數(shù)據(jù)時，使用CSV格式時的命令行參數(shù)
# 在導出數(shù)據(jù)時使用：select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;
# 在導入數(shù)據(jù)時使用：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__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

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

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

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

廣告

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