mysql 導入導出CSV數據的例子

下面來看兩個mysql 導入導出CSV數據的例子，這此都用到了mysql自帶的命令了，如 LOAD DATA LOCAL INFILE 或select * INTO OUTFILE命令了。

cvs文件導入MySql數據庫命令:

 代碼如下 復制代碼 set names utf8;
LOAD DATA LOCAL INFILE 'C:＼＼resource.csv'
INTO TABLE resource
FIELDS TERMINATED BY '  '
LINES TERMINATED BY '＼r＼n'
(title,singer);

FIELDS TERMINATED BY ---- 字段終止字符
OPTIONALLY ENCLOSED BY ---- 封套符
LINES TERMINATED BY ---- 行終止符

導出一段時間的數據到 cvs：

 代碼如下 復制代碼

fileName="data"`date --date=$date "+%Y%m%d"`".csv";
#mysql裡先導出數據到 xml, 用 select into outfile 導出， 設定字段分隔符和記錄分隔符
/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;"
#這一步也很重要， 需要將編碼轉換為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 把數據從mysql 裡邊倒出來，其中字段分隔符 ＼t ， 記錄分隔符為 ＼n, 然後轉一下字符的編碼就OK了！