mysql匯出select結果到檔案

工程師WWW發表於2014-10-14
MySql
mysql -hxx -uxx -pxx -e "query statement" db > file 
mysql -hxx -uxx -pxx -e db "query statement" > file 
例如： 
mysql -h127.0.0.1 -uroot -p000000 -e"select * from a" test > 1.txt 
        host ip     user   password   query statement  database  filename 
這樣會輸出列名資訊，如果不想輸出列名資訊： 
 
mysql -h127.0.0.1 -uroot -p000000 -N-e"select * from a" test > 1.txt 
        host ip     user   password   query statement  database  filename 
或 
mysql -hxxx -uxx -pxx 
select * from table into outfile 'xxx.txt'; 
例如： 
mysql -h127.0.0.1 -uroot -p000000 
select * from a into outfile '1.txt'; 
 
兩種方法效果一樣的 
 
第二種方式的mysql文件： 
SELECT [select options go here] INTO {OUTFILE | DUMPFILE} filename 
EXPORT_OPTIONS 
FROM table_references [additional select options go here] 
 
例如： 
mysql -h127.0.0.1 -uroot -p000000 
select * from a into outfile "1.txt" fields terminated by '\t' lines terminated by '\r\n' 
 
第一種方法和第二種方法的結合：使用 mysql -e執行匯出到檔案的sql語句 
mysql -hxx -uxx -pxx -e "query statement" db 
例如: 
mysql -h127.0.0.1 -uroot -p000000 -e"select * from a into outfile '1.txt' fields terminated by ',' lines terminated by '\r\n'" test 
 
如果不想輸出列名資訊： 
mysql -h127.0.0.1 -uroot -p000000 -N -e"select * from a into outfile '1.txt' fields terminated by ',' lines terminated by '\r\n'" test 
 
預設情況下， mysql -e匯出的檔案，列是用"\t"分隔，行是用"\r\n"分隔(dos)，行是用"\n"分隔(unix 
 
追加一種方式： 
select col002,col005,col004,col008 into outfile 'e:/mysql/i0812.txt' fields terminated by '|' lines terminated by '\r\n' from a where col003 in (select col001 from qdbm) order by col005;

相關文章