mssql与mysql 数据迁移
概要:
mssql向mysql迁移的实例,所要用到的工具bcp和load data local infile。
由于订单记录的数据是存放在mssql服务器上的,而项目需求把数据迁移到mysql server,存在的问题mssql和mysql订单表结构不同,所以不能直接导入到mysql中的订单表里面。
解决思路,首先,在mysql数据库新建一张与mssql中订单表结构一样的表,在mssql上利用bcp工具导出数据为csv,然后把导出csv数据上传到mysql服务器上用load data命令行导入表;最后通过存储过程查询循环insert到目标表中。
导出表结构并且修改后,在mysql服务器上创建同样结构的表:(以下图为:mssql导出表结构图)
以下是导出csv相关sql语句
1 use data_trade; 2 Go 3 4 SP_CONFIGURE'show advanced options',1 5 RECONFIGURE 6 Go 7 SP_CONFIGURE 'xp_cmdshell',1 8 RECONFIGURE 9 Go 10 11 EXEC master..xp_cmdshell 'BCP data_trade.do.t_trade OUT D:\trade.csv -c -t "jxfield" -T -r "jxrowend"'
由于订单表中的字段比较复杂,字段中包含了逗号,换行和回车等符号,所以从定义的了字段分隔符和行的终止符,分别用 -t -r两个参数定义。
去掉字段中的空号逗号换行符和回车;
$ cat a.csv | tr -d ' ,^' | sed 's/jxrowend/$^/g' | tr '^' '\n' >b.csv
将导出的csv文件上传到mysql服务器上的/tmp目录下并c,然后执行
load data local infile '/tmp/trade.csv' into table `t_trade` character set gbk fields terminated by 'jxfield' optionally enclosed by '"' escaped by '"' lines terminated by 'jxrowend';
查询循环插入到目标表中代码如下:
1 DELIMITER ;; 2 3 DROP PROCEDURE IF EXISTS `update_trade`;; 4 CREATE PROCEDURE `update_trade` () 5 BEGIN 6 -- 需要定义接收游标数据的变量 7 DECLARE astorage_id varchar(40); 8 DECLARE atid varchar(40); 9 DECLARE apro_detail_code varchar(40); 10 DECLARE apro_name varchar(255); 11 DECLARE aspecification varchar(255); 12 DECLARE abarcode varchar(40); 13 DECLARE acombine_barcode varchar(40); 14 DECLARE aiscancel varchar(40); 15 DECLARE aisscheduled varchar(40); 16 DECLARE astock_situation varchar(40); 17 DECLARE aisbook_pro varchar(40); 18 DECLARE aiscombination varchar(40); 19 DECLARE aisgifts varchar(40); 20 DECLARE agift_num int(20); 21 DECLARE abook_storage varchar(40); 22 DECLARE apro_num varchar(40); 23 DECLARE asend_num varchar(40); 24 DECLARE arefund_num varchar(40); 25 DECLARE arefund_renum varchar(40); 26 DECLARE ainspection_num varchar(40); 27 DECLARE atimeinventory varchar(40); 28 DECLARE acost_price decimal(20,2); 29 DECLARE asell_price decimal(20,2); 30 DECLARE aaverage_price decimal(20,2); 31 DECLARE aoriginal_price decimal(20,2); 32 DECLARE asys_price decimal(20,2); 33 DECLARE aferght decimal(20,2); 34 DECLARE aitem_discountfee decimal(20,2); 35 DECLARE ainspection_time varchar(40); 36 DECLARE aweight varchar(40); 37 DECLARE ashopid varchar(40); 38 DECLARE aout_tid varchar(40); 39 DECLARE aout_proid varchar(40); 40 DECLARE aout_prosku varchar(40); 41 DECLARE aproexplain varchar(40); 42 DECLARE abuyer_memo varchar(40); 43 DECLARE aseller_remark varchar(255); 44 DECLARE adistributer varchar(40); 45 DECLARE adistribut_time varchar(40); 46 DECLARE asecond_barcode varchar(40); 47 DECLARE aproduct_no varchar(40); 48 DECLARE abrand_number varchar(40); 49 DECLARE abrand_name varchar(40); 50 DECLARE abook_inventory int(40); 51 DECLARE aproduct_specification varchar(255); 52 DECLARE adiscount_amount decimal(20,2); 53 DECLARE acredit_amount decimal(20,2); 54 DECLARE aMD5_encryption varchar(40); 55 56 57 58 -- 遍历数据结束标志 59 DECLARE done INT DEFAULT FALSE; 60 -- 游标 61 DECLARE cur CURSOR FOR SELECT `AC0018`,`TC0013`,`EC0155`,`EC0123`,`ac2037`,`TC0087`,`AI0033`,`EI0734`,`AI0021`,`AM0024`,`TM0030`,`AM0100`,`EQ0147`,`AD0001`,`TC0108`,`EC0155`,`AI0032` FROM `edb_item` order by `AQ2520` asc where `AQ2520`<'2014-04-09 11:31:24' order by `AQ2520` asc; 62 63 -- 将结束标志绑定到游标 64 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 65 -- 打开游标 66 OPEN cur; 67 68 -- 开始循环 69 read_loop: LOOP 70 -- 提取游标里的数据,这里只有一个,多个的话也一样; 71 FETCH cur INTO atid,apro_detail_code,apro_name,aspecification,abarcode,astock_situation,agift_num,apro_num,arefund_renum,asell_price,aoriginal_price,aitem_discountfee,ainspection_time,aweight,aout_tid,aproexplain,ainspection_num; 72 -- 声明结束的时候 73 IF done THEN 74 LEAVE read_loop; 75 END IF; 76 -- 这里做你想做的循环的事件 77 -- IF ainspection_num is null THEN SET ainspection_num=0;END IF; 78 SET astorage_id=null; 79 SET acombine_barcode=null; 80 SET aiscancel=null; 81 SET aisscheduled=null; 82 SET aisbook_pro=null; 83 SET aiscombination=null; 84 SET aisgifts=null; 85 SET abook_storage=null; 86 SET asend_num=null; 87 SET arefund_num=null; 88 SET atimeinventory=null; 89 SET acost_price=null; 90 SET aaverage_price=null; 91 SET asys_price=null; 92 SET aferght=null; 93 SET ashopid=null; 94 SET aout_proid=null; 95 SET aout_prosku=null; 96 SET abuyer_memo=null; 97 SET aseller_remark=null; 98 SET adistributer=null; 99 SET adistribut_time=null; 100 SET asecond_barcode=null; 101 SET aproduct_no=apro_detail_code; 102 SET abrand_number=null; 103 SET abrand_name=null; 104 SET abook_inventory=null; 105 SET aproduct_specification=null; 106 SET adiscount_amount=null; 107 SET acredit_amount=null; 108 SET aMD5_encryption=null; 109 110 insert into `t_trade_array`(storage_id,tid,pro_detail_code,pro_name,specification,barcode,combine_barcode,iscancel,isscheduled,stock_situation,isbook_pro,iscombination,isgifts,gift_num,book_storage,pro_num,send_num,refund_num,refund_renum,inspection_num,timeinventory,cost_price,sell_price,average_price,original_price,sys_price,ferght,item_discountfee,inspection_time,weight,shopid,out_tid,out_proid,out_prosku,proexplain,buyer_memo,seller_remark,distributer,distribut_time,second_barcode,product_no,brand_number,brand_name,book_inventory,product_specification,discount_amount,credit_amount,MD5_encryption) values (astorage_id,atid,apro_detail_code,apro_name,aspecification,abarcode,acombine_barcode,aiscancel,aisscheduled,astock_situation,aisbook_pro,aiscombination,aisgifts,agift_num,abook_storage,apro_num,asend_num,arefund_num,arefund_renum,ainspection_num,atimeinventory,acost_price,asell_price,aaverage_price,aoriginal_price,asys_price,aferght,aitem_discountfee,ainspection_time,aweight,ashopid,aout_tid,aout_proid,aout_prosku,aproexplain,abuyer_memo,aseller_remark,adistributer,adistribut_time,asecond_barcode,aproduct_no,abrand_number,abrand_name,abook_inventory,aproduct_specification,adiscount_amount,acredit_amount,aMD5_encryption); 111 112 END LOOP; 113 -- 关闭游标 114 CLOSE cur; 115 116 END;; 117 DELIMITER ;
若在存储过程出现错误,相关处理可以参考continue handler;
相关参考博文:
dcp使用心得:http://www.cnblogs.com/puke/archive/2013/06/05/3119378.html
mysql错误处理 :http://blog.chinaunix.net/uid-20304312-id-1707822.html