分析Mysql大量数据导入遇到的问题以及解决方案

在项目中,经常会碰到往数据库中导入大量数据,以便利用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的,但为了以防操作不当,还是需要防患于未然,做好备份工作。二是要修改数据库的字符集编码为utf8mb4—UTF-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中导入大批量数据时碰到的问题,以及所想到的解决方法,如果大家有更好的解决方法,或者碰到其他的问题,希望一起讨论。

您可能感兴趣的文章:

  • 将sqlite3中数据导入到mysql中的实战教程
  • php基于Fleaphp框架实现cvs数据导入MySQL的方法
  • MySQL中数据导入恢复的简单教程
  • PHP把MSSQL数据导入到MYSQL的方法
  • MySQL中使用load data命令实现数据导入的方法
  • 解析csv数据导入mysql的方法
  • phpMyAdmin下将Excel中的数据导入MySql的图文方法
  • 3步搞定纯真IP数据导入到MySQL的方法详解
  • Excel数据导入Mysql数据库的实现代码
  • 如何把ACCESS的数据导入到Mysql中
  • MYSQL大数据导入
时间: 2018-02-01

将sqlite3中数据导入到mysql中的实战教程

前言 sqlite3只小巧轻便,但是并不支持并发访问,当网站并发量较大时候,数据库请求队列边长,有可能导致队列末尾去数据库操作超时,从而操作失败.因此需要切换到支持并发访问的数据库.切换数据库需要将老的数据导出,再导入到新的数据库中,但是sqlite3和mysql的数据库并不完全兼容,需要做部分调整才能正常导入到mysql中.我最近工作中就遇到了这个问题. 最近一个项目中使用magenetico抓取磁力链接,由于它使用的是sqlite3, 文件会越来越大,而且不支持分布式:所以需要将其改造成My

MYSQL大数据导入

感谢XP提供的代码!  在这里记录一下,因为以后学要用:作用资料查询之用!  第一步:mysql -h localhost -uroot   第二步:show databases;  第三步:use changchunmap;  第四步:show tables;  第五步:load data local infile "d:/c.txt" replace into table changchunmap fields terminated by ' ';  没有了:  load data

MySQL中使用load data命令实现数据导入的方法

使用方式如下: 复制代码 代码如下: mysql>load data local infile "D:/ab.txt" into table mytbl(name,age); 使用上述的命令就可以将D:/ab.txt文件的内容导入到表mytbl中,其中name和age是表mytbl的字段,对应ab.txt文件中每行的数据.如果编译安装mysql时没有指定 –enable-local-infile,那么在使用上述命令时会报如下错误: 复制代码 代码如下: ERROR 1148 (4

如何把ACCESS的数据导入到Mysql中

如何把ACCESS的数据导入到Mysql中  www.Alltips.Com 2001-10-6  极限技术网 在建设网站的过程中,经常要处理一些数据的导入及导出.在Mysql数据库中,有两种方法来处理数据的导出(一般).   1. 使用select * from table_name into outfile "file_name";   2. 使用mysqldump实用程序   下面我们来举例说明:   假设我们的数据库中有一个库为samp_db,一个表为samp_table.现在

phpMyAdmin下将Excel中的数据导入MySql的图文方法

一开始导入失败了. 生成的SQL语句不正确. SQL 查询: INSERT INTO `cc_present`.`c_city` (`A`, `B`, `C`) VALUES (NULL, 1, '市辖区')... 像这样列名称变为A,B,C了. 问题出在两个地方. 1.需要在Excel文件中加入列名称 另外注意Excel的Sheet名应该为表的名称,像下面这样. 2.需要勾选忽略首行选项 当然表名和字段名要和mySQL的定义一致,同时数据的类型和长度要没有问题,才能导入成功.

3步搞定纯真IP数据导入到MySQL的方法详解

前提: 你会用vi. 很明显,本文是在Linux下测试通过的.本文用的是MySQL命令行工具,如果你不会,用phpMyAdmin应该也可以. 第1步: 下载纯真IP数据,解压存成ip.txt. 详解:这一步不用详解了吧.如果你这一步都不会,下面就不用看了. 第2步: 用vi 编辑 ip.txt. # vi ip.txt 在vi界面下输入如下命令: :%s/\s\+/;/ 一共重复输入3次. 存盘退出: :wq 详解:ip.txt有4列.分别是起始ip,结束ip,地区,说明.列之间用不等数量的空格

Excel数据导入Mysql数据库的实现代码

首先做一下说明,为什么我要用Navicat,第一个原因,因为它是个不错的Mysql GUI工具,更重要的是,它可以将一些外部数据源导入Mysql数据库中.因为我的数据源是excel数据,所以想借助Navicat将其导入Mysql. 第一次运行,首先创建连接,主机名填写:localhost,端口为3306,然后填写用户名密码,OK.顺利的话,大家就可以看到名为localhost的连接图标了.双击点开它,一般Mysql默认有两个数据库,分别为mysql与test. 不用管它们,右键localhost

解析csv数据导入mysql的方法

mysql自己有个csv引擎,可以通过这个引擎来实现将csv中的数据导入到mysql数据库中,并且速度比通过php或是python写的批处理程序快的多.具体的实现代码示例: 复制代码 代码如下: load data infile '/tmp/file.csv' into table _tablename (set character utf8) fields terminated by ','enclosed by '"'lines terminated by '\r\n'; 这段代码中涉及的一

php基于Fleaphp框架实现cvs数据导入MySQL的方法

本文实例讲述了php基于Fleaphp框架实现cvs数据导入MySQL的方法.分享给大家供大家参考,具体如下: <?php /* * To change this template, choose Tools | Templates * and open the template in the editor. */ class Controller_KaoqinUpload extends FLEA_Controller_Action { var $uploaddir = "./uploa

MySQL中数据导入恢复的简单教程

有两个简单的方法MySQL中的数据加载到MySQL数据库从先前备份的文件. LOAD DATA导入数据: MySQL提供了LOAD DATA语句,作为一个大容量数据加载.下面是一个例子声明中,读取一个文件dump.txt,,从当前目录加载到当前数据库中的表mytbl: mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl; 如果本地的关键字是不存在的,MySQL的外观使用绝对路径名寻找到完全指定位置的文件在服务器主机上的数据文件,从文

PHP把MSSQL数据导入到MYSQL的方法

本文实例讲述了PHP把MSSQL数据导入到MYSQL的方法.分享给大家供大家参考.具体分析如下: 最近需要把一个以前的asp网站转换成php的,但php是与mysql而我的asp与mssql的,结果就需要把mssql数据导入到mysql数据库了,下面我自己写了一个实例还抄了一个实例都不错. 实例一,代码如下: 复制代码 代码如下: <?php  //国内的PNR码连接 $hostname="127.0.0.1"; //MSSQL服务器的IP地址 或 服务器的名字  $dbuser

Linux中将txt导入到mysql的方法教程

前言 昨天写小项目的时候遇到了一个需求:把txt文档的数据导入到mysql数据库中,开始本来想直接用Mysql Workbench导入TXT文件,但是最后发现不支持TXT导入,结果我吧嗒吧嗒的去把TXT转了Excel,拿到Linux上导入的时候又发现了各种乱码问题. 抱着没有什么是程序员干不了的原则,我手写了一个Python代码直接操作文件进行导入了.结果大概一万多条的文件,导入时间大概两分钟. 下面是具体的代码: mysqlpython.py文件:  自定义的连接mysql数据库的类 impo

asp.net中EXCEL数据导入到数据库的方法

本文实例讲述了asp.net中EXCEL数据导入到数据库的方法.分享给大家供大家参考.具体分析如下: excel是办公中非常常用的一个办公表格了,但我们在开发中通常会需要直接把excel数据快速导入到数据库中了,这里整理了一个asp.net中EXCEL数据导入到数据库的例子供各位参考学习. 注意:EXCEL中的第一行不能导入. 下面是源码:IntoExcel.aspx: 复制代码 代码如下: <%@ Page  AutoEventWireup="true" CodeFile=&q

Java将excel中的数据导入到mysql中

我们在实际工作中的一些时候会需要将excel中的数据导入数据库,如果你的数据量成百上千甚至更多,相信一点点ctrlc.ctrlv也不是办法,这里我们以mysql数据库为例,将excel中的数据存入数据库. 我的思路是:先将excel中的数据取出来,再把数据传入数据库,操作excel需要jxl.jar,操作数据库可以用最基本的jdbc,需要mysql-connector-java-5.0.8-bin.jar这个jar包. 下面我们先看一下excel截图: 再来看最后的效果图: 下面贴出整个代码:

Java 使用poi把数据库中数据导入Excel的解决方法

Java 利用poi把数据库中数据导入Excel 效果: 使用时先把poi包导入工程的path,注意只需要导入poi包即可,下载后有三个jar包 核心代码: 连接数据库:DBConnection.java 复制代码 代码如下: package org.xg.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;i

Bootstrap富文本组件wysiwyg数据保存到mysql的方法

Bootstrap提供了一个叫wysiwyg的富文本组件,用来显示和编辑富文本数据,但如何将编辑后的数据保存到MySQL数据库,却不得而知.另外,如何将mysql数据库中的数据显示到wysiwyg也不得而知,对于这两个问题,让我来告诉你解决方案! 一.效果展示 首先,我们先来看看效果如何: 富文本中有一张图片,还有一个数字列表 我们可以看到编辑后的数据保存成功,以及保存后对应的展示. 二.富文本 度娘对于富文本的解释如下: 富文本格式(Rich Text Format, 一般简称为RTF)是由微

文本、Excel、Access数据导入SQL Server2000的方法

如下所示: 复制代码 代码如下: "AWU","102300","ZX240-3","2609997000","2609997000","3016924000","","3091775000","","","QCR0000285","" "AYE",