Mysql大批量插入数据报Packet for query is too large
项目中需要实时解析聚类的结果文件,然后入库,突然最近这几天系统日志经常报错:### Error updating database. Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (49049746 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.,具体错误日志截图如下:
然后查了一下资料是MySQL的一个系统参数问题:max_allowed_packet,我这里当时设置值为4194304(4M),一般默认是1M,查询:show VARIABLES like '%max_allowed_packet%';
修改此变量的值:MySQL安装目录下的my.ini文件中的[mysqld]段中的"max_allowed_packet = 1M",如更改为100M(或更大,如果没有这行内容,增加一行),保存,重起MySQL服务。现在可以load大于100M的文件了。
还有一种方法,如果服务器配置受限,无法改成更大,那我们可以分批次来批量入库,而不是一次性入库(这样会插入不成功,报错),我们可以1000条作为一次批量插入,多插几次,这也是一种办法!代码如下:
Integer docCount = relateDocList.size(); log.info("评论观点对应的文档数量为:" + docCount); if(docCount >= COMMENT_DOC_COUNT) { Integer insertTimes = (docCount % COMMENT_DOC_COUNT == 0 ? docCount / COMMENT_DOC_COUNT : docCount / COMMENT_DOC_COUNT + 1); for(int i = 1; i <= insertTimes; i++) { List<EventCommentsRelateDoc> subDocList = null; if(i == insertTimes) { subDocList = relateDocList.subList((i - 1) * COMMENT_DOC_COUNT, docCount); } else { subDocList = relateDocList.subList((i - 1) * COMMENT_DOC_COUNT, i * COMMENT_DOC_COUNT); } for(EventCommentsRelateDoc relateDoc : subDocList) { for(Asset asset : assetIdsList) { if(relateDoc.getAssetId().equals(asset.getAssetId())) { relateDoc.setCreateDate(asset.getCreateDate()); break; } } } try { count = eventCommentsRelateDocMapper.addNetizenCommentRelateDoc(relateDocList); log.info("插入评论观点对应文档成功,文档数:" + relateDocList.size()); } catch (Exception e) { log.info("插入评论观点对应文档失败...,文档数:" + relateDocList.size(), e); } } } else { for(EventCommentsRelateDoc relateDoc : relateDocList) { for(Asset asset : assetIdsList) { if(relateDoc.getAssetId().equals(asset.getAssetId())) { relateDoc.setCreateDate(asset.getCreateDate()); break; } } } try { count = eventCommentsRelateDocMapper.addNetizenCommentRelateDoc(relateDocList); log.info("插入评论观点对应文档成功,文档数:" + relateDocList.size()); } catch (Exception e) { log.info("插入评论观点对应文档失败...,文档数:" + relateDocList.size(), e); } }
如有不对的地方,还希望大神们多多指教!!!