萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql怎麼導出csv格式數據文件

mysql怎麼導出csv格式數據文件

在mysql中導出數據的命令幾乎都可以導出csv格式的數據文件哦,下面我來給大家舉幾個實例,希望些方法對各位有所幫助。

1.MySQL導出本地數據庫數據到本地文件

Sql代碼

 代碼如下 復制代碼

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

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

"'
lines terminated by 'rn'; 裡面最關鍵的部分就是格式參數


2.MySQL導出遠程數據庫數據到本地文件

 代碼如下 復制代碼

mysql -A service_db -h your_host -utest -ptest -ss -e "SELECT * from t_apps limit 300;" | sed 's/t/","/g;s/^/"/;s/$/"/;s/n//g' > apps.csv

(sed部分可略,尤其是處理包含漢字的數據時)

3. mysqldump導出csv格式的數據文件

 代碼如下 復制代碼

# 使用方法如下
mysqldump -uroot -ppassword -t -T/root testdb --fields-enclosed-by=" --fields-terminated-by=,

mysqldump -h your_host -utest -ptest -w "id<300" service_db t_apps > tt.sql

導出後格式如下:

 

 代碼如下 復制代碼

"1","m11401","2013-06-22 23:00:01"
"2","m11402","2013-06-22 23:00:02"
"3","m11403","2013-06-22 23:00:03"

copyright © 萬盛學電腦網 all rights reserved