MongoDB数据插入到MySQL缺少某列的解决方案

先备份MySQL对应表的数据

用Navicat连接MongoDB数据库

执行以下语句，双击Document直到子Document。复制所有列。

-- 排除掉为空的值
db.blaze_rsp_log.find( {"data.T091_Output.application_id":{$exists:true}},{"data.T091_Output.application_id":1,"data.T091_Output.out_fraud_count":1})

替换成SQL语句

将行的开头替换

将行的开头替换为

^
UPDATE xd_apply_risk_audit_result SET fraud_count = '

将行中的制表符替换为SQL

\t
' WHERE application_id = '

将行尾替换为SQL

$
';

给运维人员执行SQL

UPDATE xd_apply_risk_audit_result SET fraud_count = '-99999' WHERE application_id = '37215253508211755617';
UPDATE xd_apply_risk_audit_result SET fraud_count = '10' WHERE application_id = '37215253508211755617';
UPDATE xd_apply_risk_audit_result SET fraud_count = '-99999' WHERE application_id = '52411184460795615781';
UPDATE xd_apply_risk_audit_result SET fraud_count = '10' WHERE application_id = '52411184460795615781';

以上是理想的方案

真实情况是导出来的数据不是JSON格式

/* 1 */
{
    "_id" : ObjectId("5f2821572104b42a80d8be80"),
    "data" : {
        "T091_Output" : {
            "out_fraud_count" : "-99999",
            "application_id" : "91851074291069224520"
        }
    }
}

/* 2 */
{
    "_id" : ObjectId("5f2828ac1332f132a97778e8"),
    "data" : {
        "T091_Output" : {
            "out_fraud_count" : "-99999",
            "application_id" : "48770335265720549054"
        }
    }
}

/* 3 */
{
    "_id" : ObjectId("5f28ce4a2104b4314419fa8a"),
    "data" : {
        "T091_Output" : {
            "out_fraud_count" : "-99999",
            "application_id" : "84108684172507123581"
        }
    }
}

而是用下面代码处理的

private static final String fileName = "D:\\json\\riskmg.txt";

    public static void main(String[] args) {

        //读取文件
        BufferedReader br = null;
        StringBuffer sb = null;
        try {
            br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), "GBK")); //这里可以控制编码
            sb = new StringBuffer();
            String line = null;
            while ((line = br.readLine()) != null) {
                line = line.trim().replaceAll("\\s*", "");
                if(line.contains("out_fraud_count")) {
                    System.out.print("UPDATE xd_apply_risk_audit_result SET fraud_count = ");
                    System.out.print(line.substring("\"out_fraud_count\":".length(), line.length() -1));
                    System.out.print("\t\t");
                }
                if(line.contains("application_id")) {
                    System.out.print(" WHERE application_id =");
                    System.out.print(line.substring("\"application_id\":".length()) );
                    System.out.print(";");
                    System.out.print("\n");
                }

                sb.append(line);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                br.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        /*String s = new String(sb); //StringBuffer ==> String
        System.out.println("addressBook.txt内容为==> " + s);
        System.out.println();
        //1、匹配子串
        String regex = "1[378][0-9]{9}";

        //2、获取匹配器
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(s);

        //3、使用匹配器的group()方法来获取:（find方法是判断是否具有匹配子串）、
        System.out.println("”" + s + "符合要求的电话号码有：");
        while (m.find()) {
            System.out.println(m.group());
        }*/

    }