本文正在参加「技术专题19期漫谈数据库技术」活动

前言

编程道路千万条,数据安全第一条,批量的数据导入在日常生产环境非常常见,那么怎么保证每一次的大批量的数据导入的安全,就成了开发人员需要解决的问题.

实际项目中,我遇到的问题是,数据源平台A在4A平台上,数据量大,平台上的数据只允许通过http访问请求的方式获取,其余方式都会被强制中断,B服务器的业务数据需要从平台A上获取,所以就有如下的情况:

因业务原因数据需要放在Mysql和Oracle两个不同的数据库中,所以两个数据库的基本操作都是通过返回的数据生成csv文件然后执行导入命令

Mysql批量数据导入

SQL语句

Mysql的导入比较简单,在Navicat中都能运行导入,下面我就用具体sql来解释一下

LOAD DATA LOCAL INFILE 'filePath' 
replace INTO TABLE tableName 
CHARACTER SET 'utf8' 
FIELDS TERMINATED BY ',' line.separator (fieldName)

SQL详解

filePath为文件的绝对路径,注意在这里生成的文件为csv,其他格式各位大佬可以另行研究!

tableName为要导入的表名,只允许插入单个表.

第三行代码设置编码格式,在实际开发的时候,不同的Java的mysql驱动包可能会有不同,有些驱动包能正常执行,有些不行,大家可以试试看.

FIELDS TERMINATED BY ',' line.separator (fieldName)设置插入的字段,用英文逗号分割.

小结

LOAD DATA虽然可以快速大量的导入数据,但是在InnoDB表中会因为事务而导致失败而回滚!

在SQL详解中说到了驱动包的问题,这里提示一个巨坑,在Java的pom文件中引入mysql一般都是runtime版本,这时候mysql驱动包都是8.0+以上的版本,而mysql8.0+的版本默认是关闭LOAD DATA的SQL操作,这时再执行SQL会抛出SQL异常的错误,为解决该错误,可以在JDBC的链接上加上allowLoadLocalInfile=true即刻解决问题.

Oracle批量数据导入

Oracle的导入较为复杂,可以看成如下步骤,为更好的结合,下面引入Java代码来展示!

graph TD
开始 --> 生成ctl文件 --> 用数据生成csv文件 --> 执行Oracle客户端命令 --> 结束

生成ctl文件

ctl文件内容就是如下代码getLoadSQL函数生成的SQL,这个SQL与Mysql中的SQL基本上一样,其SQL详解请看SQL详解,与Mysql唯一不同的地方就是他的SQL不能直接执行,必须包裹在ctl文件中!

/**
 * 将SQL写入.ctl文件
 *
 * @param dataFilePath 数据文件路径
 * @param dataFileName 数据文件名
 * @param tableName    表名
 * @param fieldName    要写入的字段
 * @param ctlName      控制文件名
 */
public void setFile(String dataFilePath, String dataFileName, String tableName, String fieldName, String ctlName) {
    FileWriter fw = null;
    try {
        fw = new FileWriter(dataFilePath + "/" + ctlName);
        fw.write(getLoadSQL(dataFilePath, dataFileName, tableName, fieldName));

    } catch (Exception e) {
        log.error(e.toString());
    } finally {
        try {
            fw.flush();
            fw.close();
        } catch (Exception e2) {
            log.error(e2.toString());
        }
    }
}


/**
 * 创建Load data SQL语句
 *
 * @param dataFilePath 数据文件路径
 * @param dataFileName 数据文件名
 * @param tableName    表名
 * @param fieldName    要写入的字段
 * @return
 */
public static String getLoadSQL(String dataFilePath, String dataFileName, String tableName, String fieldName) {
        String str = new StringBuffer()
                .append(" load data ")
                .append(" characterset 'UTF8' ")
                .append("infile  "")
                .append(dataFilePath + "/" + dataFileName)
                .append("" append into table ")
                .append(tableName)
                .append(" fields terminated by  ',' ")
//                .append(" enclosed by '"' ")
                .append(fieldName)
                .toString();
        return str;
    }

生成csv文件

生成csv文件较为简单,这里就不再描述,大部分的Java工具类如hutool就有生成csv的函数,开箱即用!

入库命令

sqlldr userid=admin/123456@//localhost:1521/orcl control=C:\Users\xxy\Desktop\insert.ctl

入库命令是重头戏,首先要执行该命令,就需要安装Oracle的客户端(sql plus),否则执行会报命令不存在的哦!其命令结构如下.这样命令可以分为两部分来看,一个基本的数据库链接,一个ctl文件路径.

但是这个画个重点,也是一个很坑的地方,数据库密码千万不要带@,别问为什么,问就是血的和泪的教训,@会导致该命令执行失败,会报密码错误!!!

小结

Oracle的入库驱动方面基本上使用11.0+版本就可以了。Oracle如果在执行csv文件导入失败的时候是不会给出很明显的报错，反而是生成一个log文件需要开发者自己打开查看，里面会有详细原因说明入库失败的原因。

总结

Mysql和Oracle入库的SQL语句基本一致,其不同点就在其运行环境,Mysql基本上安装好就无需顾虑,Oracle需要安装对应的客户端.两者基本上大同小异,大致流程综上所述,具体还有没提到的细节,可能是我没碰到的!

大批量数据导入的新姿势

前言