WD1X.COM - 问答一下,轻松解决,电脑应用解决专家
主板显卡CPU内存显示器
硬盘维修显卡维修显示器维修
注册表系统命令DOS命令Win8
存储光存储鼠标键盘
内存维修打印机维修
WinXPWin7Win11Linux
硬件综合机箱电源散热器手机数码
主板维修CPU维修键盘鼠标维修
Word教程Excel教程PowerPointWPS
网络工具系统工具图像工具
数据库javascript服务器
PHP教程CSS教程XML教程

mongodb里的集合数据迁移到mysql库

更新时间:2021-06-03 14:41 作者:selectgo点击:
1,在mongodb上导出数据,写个脚步expmongo.sh,shell脚本如下
 
#!/bin/sh
datestr=`date '+%Y-%m-%d'`
/usr/local/mongodb/mongodb-linux-x86_64-2.4.4/bin/mongoexport -im_user -pxxxeedd -d im -c message -f txnType,issNo,sn,merchantorder,paymentType,paymentTime,authNo,expDate,cardNo,amount,batchNo,refNo,merchantName,voucher,acqNo --csv -o /tmp/im_$datestr.txt && sz /tmp/im_$datestr.txt
 
导出来的im数据,/tmp/im_$datestr.txt ,大概有6000多条记录。
 
 
类似这样的数据:
txnType,issNo,sn,merchantorder,paymentType,paymentTime,authNo,expDate,cardNo,amount,batchNo,refNo,merchantName,voucher,acqNo
“0”,"",“WP14521000000342”,"",“银行卡”,“1440050366667”,"","",“6216615625”,“10”,“2”,“891094135928”,“厚味香味馆”,“000235”,""
“1”,“WP14521000000342”,“10”,
“0”,"",“WP14521000000422”,"",“现金”,“1440050722137”,"","","",“0.01”,"","",“老裁缝家纺”,"",""
 
2,在mysql库里面,建一张存储mongodb的数据的表:
 
建表语句如下:
 
CREATE TABLE sap_im (
txnType varchar(8) DEFAULT NULL,
issNo varchar(128) DEFAULT NULL,
sn varchar(128) DEFAULT NULL,
merchantorder varchar(128) DEFAULT NULL,
paymentType varchar(128) DEFAULT NULL,
paymentTime varchar(128) DEFAULT NULL,
authNo varchar(128) DEFAULT NULL,
expDate varchar(128) DEFAULT NULL,
cardNo varchar(128) DEFAULT NULL,
amount varchar(128) DEFAULT NULL,
batchNo varchar(128) DEFAULT NULL,
refNo varchar(128) DEFAULT NULL,
merchantName varchar(128) DEFAULT NULL,
voucher varchar(128) DEFAULT NULL,
acqNo varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 
3,导入到mysql里面
 
上传im_$datestr.txt到mysql服务器,先然后把导出文件改成表名字
mv /tmp/im_detail_2015-11-12.txt /tmp/sap_im.csv
 
开始导入:
mysql> LOAD DATA INFILE “/tmp/sap_im.csv” REPLACE INTO TABLE sap_im FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\r\n’;
Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
 
mysql>
奇怪,这里只导入了第一行记录,估计是换行符的问题,去掉命令行的\r,将’\r\n’换成’\n’即可,如下所示:
 
mysql>
mysql>
mysql> LOAD DATA INFILE “/tmp/sap_im.csv” REPLACE INTO TABLE sap_im FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\n’;
Query OK, 6555 rows affected (1.81 sec)
Records: 6555 Deleted: 0 Skipped: 0 Warnings: 0
 
mysql>
最终正常导入了6000多条记录出来。
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
你可能感兴趣的内容