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

2023-09-16 23:07:06 168

在项目中，经常会碰到往数据库中导入大量数据，以便利用sql进行数据分析。在导入数据的过程中会碰到一些需要解决的问题，这里结合导入一个大约4G的txt数据的实践，把碰到的问题以及解决方法展现出来，一方面自己做个总结记录，另一方面希望对那些碰到相同问题的朋友有个参考。

我导入的数据是百科的txt文件，文件大小有4G多，数据有6500万余条，每条数据通过换行符分隔。每条数据包含三个字段，字段之间通过Tab分隔。将数据取出来的方法我采用的是用一个TripleData类来存放这三个字段，字段都用String，然后将多条数据存到List中，再将List存入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，会报错。类似于

EstablishingSSLconnectionwithoutserver'sidentityverificationisnotrecommended.AccordingtoMySQL5.5.45+,5.6.26+and5.7.6+requirementsSSLconnectionmustbeestablishedbydefaultifexplicitoptionisn'tset.ForcompliancewithexistingapplicationsnotusingSSLtheverifyServerCertificatepropertyissetto'false'.YouneedeithertoexplicitlydisableSSLbysettinguseSSL=false,orsetuseSSL=trueandprovidetruststoreforservercertificateverification.

这样的错误信息。主要是mysql版本比较高，JDBC版本比较低，需要兼容。

2utf8mb4编码问题

在导入数据的过程中，还会碰到类似于

SQLException:Incorrectstringvalue:'\xF0\xA1\x8B\xBE\xE5\xA2...'forcolumn'name'

这样的错误信息，这是由于mysql中设置的utf-8是默认3个字节的，对于一般的数据是没有问题的，如果是大的数据量，里面难免会包含一些微信表情，或者特殊字符，它们占了4个字节，utf-8不能处理，所以报错。解决的办法就是mysql在5.5.3以后的版本引入了4个字节的utf-8编码，也就是utf8mb4，需要对mysql的编码重新设置。

可以按照以下步骤进行操作，一是对要修改的数据库进行备份，虽然utf8mb4是向下兼容utf8的，但为了以防操作不当，还是需要防患于未然，做好备份工作。二是要修改数据库的字符集编码为utf8mb4—UTF-8Unicode，排序规则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中，然后批量导入。大概的思路是采用“insertintotb(...)values(...),(...)...;”的方法，用insert一次性插入，这样时间会节约很多时间。示例方法如下。

publicstaticvoidinsertSQL(Stringsql,ListtripleObjectList)throwsSQLException{
Connectionconn=null;
PreparedStatementpsts=null;
try{
conn=DriverManager.getConnection(Common.URL,Common.DB_USERNAME,Common.DB_PASSWORD);
conn.setAutoCommit(false);//设置手动提交
//保存sql后缀
StringBuffersuffix=newStringBuffer();
intcount=0;
psts=conn.prepareStatement("");
Strings="";
Stringp="";
Stringo="";
while(count
这种方法的优点是导入数据花费的时间会很少，6500万条数据，用了正好1个小时。缺点是如果数据中有一大段的句子，需要对里面的逗号，括号，反斜线等进行处理，这里需要进行衡量，是否使用该方法。
如果正常插入，也就是使用“insertintotb(...)values(...);insertintotb(...)values(...);……”的形式，则不用处理特殊的符号，但花费的时间会很长，我测试了一下，11万条数据大约需要12分钟左右，导入6500万条数据大概要100个小时。
我们采用的是第一种方法，数据大概可以查看就可以，对数据要求没有那么严格，节约了时间。
以上是我在往mysql中导入大批量数据时碰到的问题，以及所想到的解决方法，如果大家有更好的解决方法，或者碰到其他的问题，希望一起讨论。

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

热门推荐

随机推荐