如何导入大型SQL文件到MySQL表 [英] How to import LARGE sql files into mysql table

问题描述

我有一个PHP脚本解析XML文件，并创建一个看起来像这样一个大的SQL文件：

I have a php script that parses XML files and creates a large SQL file that looks something like this:

INSERT IGNORE INTO table(field1,field2,field3...)
VALUES ("value1","value2",int1...),
("value1","value2",int1)...etc

此文件加起来超过20GB（我已经在一个2.5GB的文件进行测试，但它也失败了）。

This file adds up to be over 20GB (I've tested on a 2.5GB file but it fails too).

我试过像命令：

的mysql -u根-p table_name的＆LT; /var/www/bigfile.sql

mysql -u root -p table_name < /var/www/bigfile.sql

这适用于较小的文件，说50MB左右。但它不与一个更大的文件的工作。

this works on smaller files, say around 50MB. but it doesn't work with a larger file.

我试过：

mysql> source /var/www/bigfile.sql

我也试过，但的mysqlimport这甚至不会正确地处理我的文件。

I also tried mysqlimport but that won't even properly process my file.

我不断收到写着

ERROR 2006 (HY000): MySQL server has gone away

大约发生的。 30秒后我开始执行。

Happens approx. 30 seconds after I start executing.

我设置allowed_max_packet到4GB但SHOW VARIABLES验证它时，它只是显示为1GB。

I set allowed_max_packet to 4GB but when verifying it with SHOW VARIABLES it only shows 1GB.

有没有办法做到这一点不浪费再过10个小时？

Is there a way to do this without wasting another 10 hours?

推荐答案

请尝试将文件分割成多个INSERT查询。

Try splitting the file into multiple INSERT queries.

这篇关于如何导入大型SQL文件到MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！