下面來(lái)看兩個(gè)mysql 導(dǎo)入導(dǎo)出CSV數(shù)據(jù)的例子,這此都用到了mysql自帶的命令了,如 LOAD DATA LOCAL INFILE 或select * INTO outfile命令了.

cvs文件導(dǎo)入MySql數(shù)據(jù)庫(kù)命令,代碼如下:

1. set names utf8;  

2. LOAD DATA LOCAL INFILE 'C:＼＼resource.csv'  

3. INTO TABLE resource  

4. FIELDS TERMINATED BY '  '  

5. LINES TERMINATED BY '＼r＼n'  

6. (title,singer); 

7. --phpfensi.com 

8. FIELDS TERMINATED BY ---- 字段終止字符  

9. OPTIONALLY ENCLOSED BY ---- 封套符  

10. LINES TERMINATED BY ---- 行終止符 

導(dǎo)出一段時(shí)間的數(shù)據(jù)到 cvs,代碼如下:

fileName="data"`date --date=$date "+%Y%m%d"`".csv";

#mysql里先導(dǎo)出數(shù)據(jù)到 xml, 用 select into outfile 導(dǎo)出,設(shè)定字段分隔符和記錄分隔符.

/usr/local/webserver/mysql/bin/mysql -h "$mysql_host" -u "$mysql_username" --password="$mysql_password"  -e "select *  INTO outfile '$data_dir$fileName' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '＼"'  LINES TERMINATED BY '＼＼n'  from $table where InTime>=$minTime && InTime<$maxTime;"

#這一步也很重要， 需?將編碼轉(zhuǎn)換為GBK

rm -rf $final_dir$fileName && touch $final_dir$fileName

iconv -c  -f UTF-8 -t GBK  $data_dir$fileName >  $final_dir$fileName

先用select into outfile 把數(shù)據(jù)從mysql 里邊倒出來(lái),其中字段分隔符 ＼t,記錄分隔符為 ＼n,然后轉(zhuǎn)一下字符的編碼就OK了.