mysql导出txt带标题_mysql导出文本文件，加分隔符

从mysql导出，再导入到oracle

#!/bin/sh

cd/u03/tools/machine_info

rm-f data/machine_info.txt

mysql-u用户名 -p密码 -h服务器IP -P端口 < select_machine_info.sql|awk -F"\t" '{printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19)}' > data/machine_info.txt

sqlldr userid=用户名/密码@oracledb control=machine_info.ctl data=machine_info.txt parallel=true direct=true

ctl文件模板

options(skip=1)

unrecoverable

load data

characterset utf8

append into table COLLECTION_LOG_tmp

FIELDS TERMINATED BY","TRAILING NULLCOLS

(

tdbank_imp_date ,

worldid ,

ip ,

tablenames ,

gamesvrid ,

dteventtime date"yyyy-mm-dd hh24:mi:ss",

vgameappid ,

platid ,

izoneareaid ,

vopenid ,

stageid ,

stagetype ,

stageindexid ,

doodadid ,

x ,

y ,

z ,

opendoodadresult ,

mindeyestate ,

toplevel ,

originalplayerlevel

)

mysql> select * into outfile 'D:/temp/20150410.txt' FIELDS TERMINATED BY ','from `2015-04-10`;

把命令存成sql，在shell里面执行

mysql -uroot -ppassword < update.sql

a.sql

select DATE_SUB(sysdate(), INTERVAL 1 DAY), * into outfile '\data\player\equip.log' FIELDS TERMINATED BY ';' from `equip` limit 1;

a.sh

cd /data/player

rm-rf *mysql-uroot -ppassword

MYDATE=$(date +%Y%m%d)

mv equip.log equip-1-$MYDATE.log

SELECT INTO…OUTFILE语法：

select * from Table into outfile '/路径/文件名'fields terminated by ','

enclosed by '"'

lines terminated by '\r\n'

(1)路径目录必须有读写权限777

(2)文件名必须唯一

(3)fields terminated by ','必须存在，否则打开的文件的列在同一的单元格中出现

(4)我验证的表结构为gbk的，否则出现乱码

●  fields子句：在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。

(1)TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志。

(2)ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

(3)ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。

(4) LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志。

导出mysql，并执行rsync

#

time=`date +%Y%m%d`

mysql-uroot -p'密码' -e "show databases;" >/tmp/database.txt

db=`cat /tmp/database.txt |grep _ws|awk -F'[ws_]+' '{print $2}'`

bak_path=/data/player_rsync/

for i in${db[@]}domkdir/data/player_rsync/$time/chmod777 /data/player_rsync/$time/#mysqldump-uroot -p'密码' _ws${i}_branch player_query_data > /data/player_rsync/$time/tw${i}.txt

mysqldump--tab='/data/player_rsync/'$time'/' --fields-terminated-by='|' -h 127.0.0.1 -u root -p'密码'_ws${i}_branch player_query_data

mv/data/player_rsync/$time/player_query_data.txt /data/player_rsync/$time/game_tw${i}.txt

sed-i 's/^/playerquerydata|100'$i'|&/g' /data/player_rsync/$time/game_tw${i}.txt

done

sleep20sed-i "s/\\\\|/\&/g" /data/player_rsync/$time/*.txt

rm -r /data/player_rsync/$time/player_query_data.sql

rsync -vzrtp --progress --password-file=/etc/rsync.passwd /data/player_rsync/ qysql@127.0.0.1::qy_sql

num=`ls /data/player_rsync/|wc -l`

if [[ $num > 5 ]];then

file=`ls /data/player_rsync/|grep $time`

for a in ${file[@]}

do

cd $bak_path

rm -rf $a

done

fi

exit