由于扩展插入导致 MySQL 导入数据库错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/680571/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Import Database Error because of Extended Inserts
提问by Nick Woodhams
I'm importing a 400MB(uncompressed) MySQL database. I'm using BIGDUMP, and I am getting this error:
我正在导入一个 400MB（未压缩）的 MySQL 数据库。我正在使用 BIGDUMP，但出现此错误：
Stopped at the line 387.
At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn't place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos.
I believe the file does contain Extended Inserts, however I have no way to regenerate the database as it has been deleted from the old server. How can I import this database or convert it to be imported?
我相信该文件确实包含扩展插入，但是我无法重新生成数据库，因为它已从旧服务器中删除。如何导入此数据库或将其转换为导入？
Thanks for any help.
谢谢你的帮助。
Best Nick
最好的尼克
EDIT:It appears the only viable answer is to separate the extended inserts, but I still need help figuring out how to split the file as the answer below suggests. Please help. Thank you.
编辑：似乎唯一可行的答案是分离扩展插入，但我仍然需要帮助弄清楚如何拆分文件，如下面的答案所示。请帮忙。谢谢你。
采纳答案by Nick Woodhams
I was able to import the database successfully after increasing the memory buffer and doing an ordinary mysql import via SSH.
在增加内存缓冲区并通过 SSH 进行普通的 mysql 导入后，我能够成功导入数据库。
I used the following command with the absolute path:
我将以下命令与绝对路径一起使用：
mysql -u <username> -p < /home/dir/dumpfile.sql
Best Nick
最好的尼克
回答by Eng. khalid Ezz Eldin
You don't need to make any changes to .sql file: just open bigdump.php in any text editor, find the line
您不需要对 .sql 文件进行任何更改：只需在任何文本编辑器中打开 bigdump.php，找到该行
define ('MAX_QUERY_LINES',300);
and replace 300 with anything you want.
并用你想要的任何东西替换 300。
回答by nickf
Just edit the dump file so that after 300 inserts you start a new statement:
只需编辑转储文件，以便在 300 次插入后开始一个新语句：
INSERT INTO `myTable` (`id`, `field1`, `field2`) VALUES
(1, 'a', 'b'),
(2, 'c', 'd'),
(3, 'e', 'f'),
-- snip --
(300, 'w', 'x');
INSERT INTO `myTable` (`id`, `field1`, `field2`) VALUES
(301, 'y', 'z'),
(302, ... etc
You can do this by just opening your <backup>.sqlfile in any text editor, it's just plain text. Sometimes a backup might be a .gzor .bzfile, but they're just zips: if you decompress these gzipped or bzipped files, you'll end up with a single .sqlfile which you can edit easy.
您只需<backup>.sql在任何文本编辑器中打开文件即可完成此操作，它只是纯文本。有时备份可能是一个.gz或.bz文件，但它们只是 zip：如果您解压缩这些 gzip 或 bzipped 文件，您最终会得到一个.sql可以轻松编辑的文件。
It probably wouldn't be too hard to write a script to split each statement after an arbitrary number of lines. The basic program logic would look like this, perhaps:
编写一个脚本来在任意数量的行之后拆分每个语句可能不会太难。基本的程序逻辑可能如下所示：
- find a line that starts with "INSERT".
- copy the line up until the end of the word "VALUES"
- step through all the following lines, counting up as you go.
- if your counter reaches 300, then add a semicolon to the end of the last line and paste in the "INSERT ... VALUES" statement from before. Reset your counter to 1 and go to step 3.
- if you reach a line that starts with "INSERT", reset the counter to 1 and go to step 2.
- 找到以“INSERT”开头的行。
- 复制该行直到单词“VALUES”的结尾
- 逐步完成以下所有行，边走边数。
- 如果您的计数器达到 300，则在最后一行的末尾添加一个分号并粘贴之前的“INSERT ... VALUES”语句。将计数器重置为 1，然后转到步骤 3。
- 如果到达以“INSERT”开头的行，请将计数器重置为 1 并转到步骤 2。
Though, you could just do this one manually, and then make sure that all future backups don't use extended inserts. Also, phpMyAdmin lets you set a maximal statement size when creating an export, so it'll do the above for you, basically.
不过，您可以手动执行此操作，然后确保所有未来的备份都不会使用扩展插入。此外，phpMyAdmin 允许您在创建导出时设置最大语句大小，因此基本上它会为您完成上述操作。
Though, what you did with the memory limit is probably a much easier solution. For anyone else in a similar situation who can't change those limits, try the above.
不过，您对内存限制所做的可能是一个更简单的解决方案。对于处于类似情况但无法更改这些限制的其他任何人，请尝试上述操作。
回答by Khaan
For me it worked as follows:
对我来说，它的工作方式如下：
Search for
搜索
$max_query_lines = 300;
then simply replaced to
然后简单地替换为
$max_query_lines = 3000;
回答by cometfish
Old question, but I found it via internet search, and it doesn't yet have the answer I was looking for, as it looks like phpMyAdmin has renamed the setting.
老问题，但我通过互联网搜索找到了它，但它还没有我正在寻找的答案，因为看起来 phpMyAdmin 已经重命名了该设置。
To disable 'extended inserts' in a phpMyAdmin export, you need to set the 'Syntax to use when inserting data' setting to the first option:
要在 phpMyAdmin 导出中禁用“扩展插入”，您需要将“插入数据时使用的语法”设置设置为第一个选项：
include column names in every INSERT statement
Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)
回答by skysolution
Open bigdump.phpand change the following line:
打开bigdump.php并更改以下行：
$max_query_lines = 300;
to this:
对此：
$max_query_lines = 300000;
回答by Codo Ekran
Thanks for the 'max_query_lines' and 'INSERT' solutions!
感谢“max_query_lines”和“INSERT”解决方案！
"Both solutions have some drawbacks. Without extended insert, output dump will be many times larger than the dump with extended inserts. Increasing max_query_lines will increase the memory usage of bigdump script and your server might run out of the memory."
“这两种解决方案都有一些缺点。如果没有扩展插入，输出转储将比扩展插入的转储大很多倍。增加 max_query_lines 将增加 bigdump 脚本的内存使用量，您的服务器可能会耗尽内存。”
Source: http://www.etctips.com/error-at-this-place-the-current-query-includes-more-than-xxx-dump-lines/
来源：http: //www.etctips.com/error-at-this-place-the-current-query-includes-more-than-xxx-dump-lines/
