在项目中, 经常会碰到往数据库中导入大量数据, 以便利用 sql 进行数据分析在导入数据的过程中会碰到一些需要解决的问题, 这里结合导入一个大约 4G 的 txt 数据的实践, 把碰到的问题以及解决方法展现出来, 一方面自己做个总结记录, 另一方面希望对那些碰到相同问题的朋友有个参考
我导入的数据是百科的 txt 文件, 文件大小有 4G 多, 数据有 6500 万余条, 每条数据通过换行符分隔每条数据包含三个字段, 字段之间通过 Tab 分隔将数据取出来的方法我采用的是用一个 TripleData 类来存放这三个字段, 字段都用 String, 然后将多条数据存到 List<TripleData> 中, 再将 List<TripleData > 存入 mysql 数据库, 分批将所有数据存到 mysql 数据库中
以上是一个大概的思路, 下面是具体导入过程中碰到的问题
1 数据库连接的乱码及兼容问题
数据中如果有中文的话, 一定要把链接数据库的 url 设置编码的参数, url 设置为如下的形式
URL="jdbc:mysql://"+IP+":"+PORT+"/"+DB_NAME+"?useSSL=false&useUnicode=true&characterEncoding=utf-8";
把编码设置为 UTF-8 是解决乱码问题, 设置 useSSL 是解决 JDBC 与 mysql 的兼容问题如果不设置 useSSL, 会报错类似于
Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
这样的错误信息主要是 mysql 版本比较高, JDBC 版本比较低, 需要兼容
2 utf8mb4 编码问题
在导入数据的过程中, 还会碰到类似于
SQLException :Incorrect string value: '\xF0\xA1\x8B\xBE\xE5\xA2...' for column 'name'
这样的错误信息, 这是由于 mysql 中设置的 utf-8 是默认 3 个字节的, 对于一般的数据是没有问题的, 如果是大的数据量, 里面难免会包含一些微信表情, 或者特殊字符, 它们占了 4 个字节, utf-8 不能处理, 所以报错解决的办法就是 mysql 在 5.5.3 以后的版本引入了 4 个字节的 utf-8 编码, 也就是 utf8mb4, 需要对 mysql 的编码重新设置
可以按照以下步骤进行操作, 一是对要修改的数据库进行备份, 虽然 utf8mb4 是向下兼容 utf8 的, 但为了以防操作不当, 还是需要防患于未然, 做好备份工作二是要修改数据库的字符集编码为 utf8mb4UTF-8 Unicode, 排序规则 utf8mb4_general_ci 以上修改我是使用 navicat 进行修改的, 如何用命令行修改, 大家可以自行查找三是要修改配置文件 my.ini, 在 mysql 安装的根目录下加入以下设置
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[mysql]
default-character-set = utf8mb4
修改完成后, 需要重新启动 mysql, 使修改生效
然后再进行数据的导入工作, 应该就可以正常导入了
3 大批量导入的时间效率问题
由于我们的数据量比较大, 我们把数据进行了分割, 我把 6500 万条数据分为 500 个文件, 每个文件大约 11 万条数据, 将这 11 万条数据放到 ArrayList<TripleObject > 中, 然后批量导入大概的思路是采用 insert into tb (...) values(...),(...)...; 的方法, 用 insert 一次性插入, 这样时间会节约很多时间示例方法如下
public static void insertSQL(String sql,List<TripleObject> tripleObjectList) throws SQLException{
Connection conn=null;
PreparedStatement psts=null;
try {
conn=DriverManager.getConnection(Common.URL, Common.DB_USERNAME, Common.DB_PASSWORD);
conn.setAutoCommit(false); // 设置手动提交
// 保存 sql 后缀
StringBuffer suffix = new StringBuffer();
int count = 0;
psts=conn.prepareStatement("");
String s="";
String p="";
String o="";
while (count<tripleObjectList.size()) {
s=tripleObjectList.get(count).getSubject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)","").replaceAll("\'", "").replaceAll("\\\\","");
p=tripleObjectList.get(count).getPredicate().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)","").replaceAll("\'", "").replaceAll("\\\\","");
o=tripleObjectList.get(count).getObject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)","").replaceAll("\'", "").replaceAll("\\\\","");
suffix.append("('" +s +"','"+p+"','"+ o+"'),");
count++;
}
// 构建完整 SQL
String allsql = sql + suffix.substring(0, suffix.length() - 1);
// 添加执行 SQL
psts.addBatch(allsql);
psts.executeBatch(); // 执行批量处理
conn.commit(); // 提交
} catch (Exception e) {
e.printStackTrace();
}finally{
if(psts!=null){
psts.close();
}
if(conn!=null){
conn.close();
}
}
}
这种方法的优点是导入数据花费的时间会很少, 6500 万条数据, 用了正好 1 个小时缺点是如果数据中有一大段的句子, 需要对里面的逗号, 括号, 反斜线等进行处理, 这里需要进行衡量, 是否使用该方法
如果正常插入, 也就是使用 insert into tb (...) values(...);insert into tb (...) values(...); 的形式, 则不用处理特殊的符号, 但花费的时间会很长, 我测试了一下, 11 万条数据大约需要 12 分钟左右, 导入 6500 万条数据大概要 100 个小时
我们采用的是第一种方法, 数据大概可以查看就可以, 对数据要求没有那么严格, 节约了时间
以上是我在往 mysql 中导入大批量数据时碰到的问题, 以及所想到的解决方法, 如果大家有更好的解决方法, 或者碰到其他的问题, 希望一起讨论
来源: https://www.cnblogs.com/coodream2009/p/cooldream2009.html