数据库迁移：F_mysql端导出脚本与sqlserver端导入脚本

huangxifeng607

#!/bin/bash
if [ -d /tmp/mysql_out ]
then
  rm -rf /tmp/mysql_out;
  mkdir /tmp/mysql_out;
  chown mysql.mysql /tmp/mysql_out;
else
  mkdir /tmp/mysql_out;
  chown mysql.mysql /tmp/mysql_out;
fi
/export/mysql6/bin/mysql hxf_tuan -e "

update ask set content=replace(content,'\t',' ')  where content like '%\t%';
update ask set comment=replace(comment,'\t',' ')  where comment like '%\t%';
select id , user_id , team_id , city_id , ifnull(content,'null'), ifnull(comment,'null'),create_time , ifnull(admin_id,'null') , ifnull(answer_time,'null') from ask  into outfile '/tmp/mysql_out/ask.txt';


select id,service,return_ip ,notice_type,notice_time,nofity_type,nofity_id,nofity_time,sign,sign_type,ifnull(trade_no,'null'), ifnull(out_trade_no,'null'),ifnull(discount,'null'),ifnull(payment_type,'null'),ifnull(subject,'null'),ifnull(body,'null') ,ifnull(price,'null'),ifnull(quantity,'null'),ifnull(total_fee,'null'),ifnull(trade_status,'null') ,ifnull(refund_status,'null'),ifnull(seller_email,'null') ,ifnull(seller_id,'null'),ifnull(buyer_id ,'null'),ifnull(buyer_email,'null'),ifnull(gmt_create,'null'),ifnull(is_total_fee_adjust,'null'),ifnull(gmt_payment ,'null'),ifnull(gmt_close,'null'), ifnull(gmt_refund,'null'),ifnull(use_coupon,'null')  
from alipay_pay_record into outfile '/tmp/mysql_out/alipay_pay_record.txt';

update category set czone='NULL' where czone='';
select id , ifnull(zone,'null') , ifnull(czone,'null') , ifnull(name,'null') , ifnull(ename,'null')   , ifnull(letter,'null') , ifnull(is_open,'null') , ifnull(is_city,'null') from category into outfile '/tmp/mysql_out/category.txt';


SELECT * FROM country into outfile '/tmp/mysql_out/country.txt';

select id, user_id, partner_id, team_id, order_id, type , credit, ifnull(secret,'null'), consume,ifnull( ip,'null'), sms, expire_time, consume_time, create_time, consume_number
from coupon into outfile '/tmp/mysql_out/coupon.txt';


select id,coupon_id,user_id,partner_id,team_id,order_id,type,credit,ifnull(secret,'null'),expire_time,consume_time,create_time,consume_number,ifnull(partner_name,'null')
from coupon_consume_history into outfile '/tmp/mysql_out/coupon_consume_history.txt';


update feedback set content=replace(content,'\t',' ')  where content like '%\t%';
select id,city_id,user_id,category,ifnull(title,'null'),ifnull(contact,'null'),content,create_time,ifnull(mobile,'null')
from feedback into outfile '/tmp/mysql_out/feedback.txt';

select id,user_id,admin_id,ifnull(detail_id,'null'),direction,money,action,create_time,recharge
from flow into outfile '/tmp/mysql_out/flow.txt';

select id,user_id,admin_id,ifnull(user_ip,'null'),other_user_id,ifnull(other_user_ip,'null'),team_id,pay,credit,buy_time,create_time
from invite into outfile '/tmp/mysql_out/invite.txt';

select * from lotteny into outfile '/tmp/mysql_out/lotteny.txt';


update \`order\` set remark=replace(remark,'\t',' ')  where remark like '%\t%';
update \`order\` set address=replace(address,'\t',' ')  where address like '%\t%';
update \`order\` set express_no=replace(express_no,'\t',' ')  where express_no like '%\t%';
select id,ifnull(pay_id,'null'),service,user_id,admin_id,team_id,city_id,ifnull(card_id,'null'),state,quantity,ifnull(realname,'null'),ifnull(mobile,'null'),ifnull(zipcode,'null'),ifnull(address,'null'),
express,ifnull(express_xx,'null'),express_id,ifnull(express_no,'null'),price,money,origin,credit,card,fare,ifnull(remark,'null'),create_time,ifnull(code,'null'),ifnull(extra,'null'),
ifnull(extra_password,'null'),sms ,source,ifnull(address_id,'null'),ifnull(pay_time,'null'),recharge
from \`order\` into outfile '/tmp/mysql_out/order.txt';


select id,ifnull(username,'null'),ifnull(password,'null'),ifnull(title,'null'),ifnull(homepage,'null'),city_id,ifnull(bank_name,'null'),ifnull(bank_no,'null'),
ifnull(bank_user,'null'),location,ifnull(contact,'null'),
ifnull(phone,'null'),ifnull(address,'null'),ifnull(addres,'null'),ifnull(other,'null'),ifnull(mobile,'null'),user_id,create_time,ifnull(map_url,'null'),ifnull(map_img,'null'),business_time,
short_title,ifnull(branch_name,'null')
from partner into outfile '/tmp/mysql_out/partner.txt';

select id,order_id,bank,ifnull(money,'null'),currency,service,create_time from pay  into outfile '/tmp/mysql_out/pay.txt';

select id,ifnull(email,'null'),city_id,ifnull(secret,'null'),ifnull(add_time,'null')
from subscribe into outfile '/tmp/mysql_out/subscribe.txt';

update sys_log set run_sql=replace(run_sql,'\t',' ')  where run_sql like '%\t%';
select id,user_id,ifnull(email,'null'),module,ifnull(operate,'null'),ifnull(order_id,'null'),ifnull(team_id,'null'),
ifnull(invite_id,'null'),ifnull(vouchers_id,'null'),ifnull(run_sql,'null'),ifnull(create_time,'null')
from sys_log into outfile '/tmp/mysql_out/sys_log.txt';



update team set detail=replace(detail,'\t',' ')  where detail like '%\t%';
select
id,user_id,ifnull(title,'null'),ifnull(summary,'null'),city_id,area_id,group_id,partner_id,system,
team_price,market_price,ifnull(product,'null'),per_number,min_number,max_number,
now_number,ifnull(image,'null'),ifnull(image1,'null'),ifnull(image2,'null'),ifnull(flv,'null'),mobile,credit,card,fare,ifnull(address,'null'),
ifnull(detail,'null'),ifnull(systemreview,'null'),ifnull(userreview,'null'),ifnull(notice,'null'),ifnull(express,'null'),delivery,state,expire_time,
begin_time,end_time,close_time,fake_num,features,token,is_top,name_short,
f_coin,union_resource,product_type,commission,price_cost,success_time,ifnull(bulletin,'null'),
ifnull(other_team_image,'null'),ifnull(other_team_link,'null'),ifnull(img_hao123,'null'),ifnull(img_360,'null'),use_number,remind_time,
ifnull(hot_images,'null'),ifnull(hot_product,'null'),
ifnull(other_team_name,'null'),bus_id
from team
into outfile '/tmp/mysql_out/team.txt';

select id,team_id,city_id,bus_id,ifnull(bus_name,'null'),recommend,ifnull(rec_time,'null') ,ifnull(sort,'null')
from team_bus into outfile '/tmp/mysql_out/team_bus.txt';

update user set username=replace(username,'\t',' ')  where username like '%\t%';
update user set address=replace(address,'\t',' ')  where address like '%\t%';
select id,ifnull(email,'null'),ifnull(username,'null'),ifnull(realname,'null'),ifnull(password,'null'),ifnull(avatar,'null'),gender,newbie,
ifnull(mobile,'null'),ifnull(qq,'null'),money,ifnull(zipcode,'null'),ifnull(address,'null'),city_id,enable,manager,
ifnull(secret,'null'),ifnull(recode,'null'),ifnull(ip,'null'),login_time,create_time,if_message,dingyue,
total_consumption,kx_id,ifnull(sina_id,'null'),recharge,ifnull(cooperate_name,'null'),ifnull(inviter_id,'null'),ifnull(source,'null')
from user into outfile '/tmp/mysql_out/user.txt';


select id,ifnull(username,'null'),user_id,ifnull(ip,'null'),addtime
from vote_feedback into outfile'/tmp/mysql_out/vote_feedback.txt';

select * from vote_feedback_input into outfile'/tmp/mysql_out/vote_feedback_input.txt';

select * from vote_feedback_question into outfile'/tmp/mysql_out/vote_feedback_question.txt';

select id,question_id,name,is_br,is_input,is_show,ifnull(\`order\`,'null')
from vote_options into outfile'/tmp/mysql_out/vote_options.txt';

select * from vote_question into outfile'/tmp/mysql_out/vote_question.txt';

select team_id,code,status,ifnull(user_id,'null'),ifnull(order_id,'null'),ifnull(partner_id,'null'),sms,ifnull(expire_time,'null'),ifnull(deliver_time,'null'),create_time
from vouchers into outfile'/tmp/mysql_out/vouchers.txt';"





dos2unix -u /tmp/mysql_out/*;
for i in `ls /tmp/mysql_out/*.txt`
do
 iconv -f utf8 -t GB18030 "$i" > "$i".txt;
done







#sqlserver端
bulk insert dbo.ask from 'C:\mysql_out\ask.txt.txt';
bulk insert dbo.alipay_pay_record from 'C:\mysql_out\alipay_pay_record.txt.txt';
bulk insert dbo.category from 'C:\mysql_out\category.txt.txt';
bulk insert dbo.country from 'C:\mysql_out\country.txt.txt';
bulk insert dbo.coupon from 'C:\mysql_out\coupon.txt.txt'
bulk insert dbo.coupon_consume_history from 'C:\mysql_out\coupon_consume_history.txt.txt';
bulk insert dbo.feedback  from 'C:\mysql_out\feedback.txt.txt'
bulk insert dbo.flow  from 'C:\mysql_out\flow.txt.txt'
bulk insert  dbo.invite from 'C:\mysql_out\invite.txt.txt'
bulk insert  dbo.lotteny from 'C:\mysql_out\lotteny.txt.txt'
bulk insert dbo.[order] from 'c:\mysql_out\order.txt.txt';
bulk insert dbo.partner from 'c:\mysql_out\partner.txt.txt'
bulk insert dbo.pay from 'c:\mysql_out\pay.txt.txt'
bulk insert dbo.subscribe from 'c:\mysql_out\subscribe.txt.txt'
bulk insert dbo.sys_log  from 'c:\mysql_out\sys_log.txt.txt'
bulk insert dbo.team  from 'c:\mysql_out\team.txt.txt'
bulk insert dbo.team_bus  from 'c:\mysql_out\team_bus.txt.txt'
bulk insert dbo.[user]  from 'c:\mysql_out\user.txt.txt'
bulk insert  dbo.vote_feedback from 'c:\mysql_out\vote_feedback.txt.txt'
bulk insert  dbo.vote_feedback_input from 'c:\mysql_out\vote_feedback_input.txt.txt'
bulk insert  dbo.vote_feedback_question from 'c:\mysql_out\vote_feedback_question.txt.txt'
bulk insert  dbo.vote_options from 'c:\mysql_out\vote_options.txt.txt'
bulk insert  dbo.vote_question from 'c:\mysql_out\vote_question.txt.txt'
bulk insert dbo.vouchers  from 'c:\mysql_out\vouchers.txt.txt'