blocks|key|332797|text|使用python对转储文件进行后处理。您可能比perl或sed更快乐。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|332798|如果您在Linux上运行，那么您已经安装了它。如果您在Windows上运行，安装程序将不再费力。|332799|但是，在此之前，请学习使用SQL+UPDATE和SQL+ALTER。用正确的方式做事情，你会感到最快乐。|332800|entityMap|0|LINK|mutability|MUTABLE|url|http://www.python.org/^0|2|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

Post-process the dump file with <a href="http://www.python.org/" rel="nofollow noreferrer">python</a>. You may be happier than perl or sed.

If you're running on Linux, you already have it installed. If you're running on Windows, the installer is painless.

Before that, however, learn to use SQL UPDATE and SQL ALTER. You'll be happiest doing things the right way.

blocks|key|3095461|text|默认情况下，mysqldump只为每个表生成一个INSERT命令，从而为每个被转储的表生成一行(非常长)的插入数据。这本质上是因为“批处理”插入比为每个表中的每条记录生成单独的INSERT查询要快得多。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3095462|所以，这并不是说mysqldump创建了任意长的行，您可以设置一些其他的截止长度。排队很长是有原因的。|3095463|如果将INSERT分解到多行中真的很重要，您可以使用以下命令来指明：|3095464|mysqldump+--extended-insert=FALSE+--complete-insert=TRUE+...|code-block|syntax|javascript|3095465|但是，请注意，在这种格式下，恢复表会花费更长的时间。|3095466|entityMap^0|6|9|O|6|2G|6|0|8|9|0|3|6|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|R|$]]

By default, <code>mysqldump</code> generates only one <code>INSERT</code> command per table, resulting in one (very long) line of inserted data for each table that got dumped. This is essentially because the "batch" inserts are much faster than if it generated a separate <code>INSERT</code> query for every record in every table.

So, it's not that <code>mysqldump</code> has created arbitrarily long lines, and you can just impose some other cutoff length. The lines are long for a reason.

If it's really important to get the <code>INSERT</code>s broken down onto multiple lines, you can indicate that with:

<pre><code>mysqldump --extended-insert=FALSE --complete-insert=TRUE ...
</code></pre>

Note, however, that restoring tables will take longer in this format.

blocks|key|3095478|text|今天我浏览了MySQL源代码，寻找这个问题的解决方案。最大行长度由变量opt_net_buffer_length强制执行，该变量应与MySQL服务器的缓冲区大小相匹配。它大得滑稽可笑。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3095479|但不管怎样，这是一种选择，所以只需这样做：|3095480|mysqldump+--net_buffer_length=5000+...|code-block|syntax|javascript|3095481|最小值为4096。|3095482|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I was browsing the MySQL source code looking for a solution to this problem today. The maximum line length is enforced by the variable opt_net_buffer_length which is supposed to match the MySQL server's buffer size. It is comically large.

But anyway, it's an option, so just do this:

<pre><code>mysqldump --net_buffer_length=5000 ...
</code></pre>

The minimum value is 4096.

blocks|key|3095555|text|我在MySQL论坛上看到了一个答案，它最终显示在每个插入组后添加'\n‘不可能单独使用mysqldump，而不修改源代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3095556|3095557|无法根据逗号或括号100%25正确解析扩展格式，您需要对字段进行计数。最好的解决方案是，在输出时将mysqldump修复为linebreak。|blockquote|3095558|3095559|非常小的变化:在第3506行，您可以看到行结束逗号的输出位置：|3095560|fputc(',',md_result_file);++++++++++++/*+Always+row+break+*/|offset|length|style|CODE|3095561|只需在紧跟第3506行之后插入此行：|3095562|fputc('\n',md_result_file);+++++/*+Lon+Binder+says+wrap+that+line!+*/|3095563|重新编译并完成。|3095564|3095565|@查看http://forums.mysql.com/read.php?28,420002,426110#msg-426110|3095566|谢谢Lon+B！|3095567|(我已经包含了MySQL论坛的内容，以防论坛消失。)|3095568|entityMap|0|LINK|mutability|MUTABLE|url|http://forums.mysql.com/read.php?28,420002,426110#msg-426110^0|0|0|0|0|0|0|1O|0|0|0|1X|0|0|0|3|1O|0|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1B|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1D|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1E|8|@$K|1F|L|1G|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1I|8|@$K|1J|L|1K|M|N]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1L|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1N|8|@]|9|@$K|1O|L|1P|1|1Q]]|A|$]]|$1|X|3|Y|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1S|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]]]

I came across an answer on the MySQL forums, which conclusively shows adding '\n' after each INSERT group is not possible using mysqldump alone, without modifying the source:

<blockquote>
 The extended format cannot be 100% properly parsed based on the comma
 or parenthesis, you would to count fields. The best solution, fix
 mysqldump to linebreak on output. 
 
 Very minor change: On line 3506, you can see where the row-ending
 comma is output: 
 <code>fputc(',',md_result_file); /* Always row break */</code>
 
 Simply insert this line immediately after line 3506: 
 <code>fputc('\n',md_result_file); /* Lon Binder says wrap that line! */</code>
 
 re-compile and done.
</blockquote>

@see <a href="http://forums.mysql.com/read.php?28,420002,426110#msg-426110">http://forums.mysql.com/read.php?28,420002,426110#msg-426110</a>

Thanks Lon B!

(I've included the content from the MySQL forum just in case the forum disappears.)

blocks|key|334123|text|使用正则表达式来拆分行是不够的，您需要一个能够正确理解引号和转义字符的解析器。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334124|我只是写了一个解析器，因为我找不到一个：http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html|offset|length|334125|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html^0|0|K|1V|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Using a regex to split lines is not enough, you need a parser that will properly understand quotes and escaping characters.

I just wrote a parser since I couldn’t find one: <a href="http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html" rel="noreferrer">http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html</a>

blocks|key|3308432|text|这个标志也是有效的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3308433|mysqldump+--skip-extended-insert+|code-block|syntax|javascript|3308434|就像--extended-insert=FALSE一样。|offset|length|style|CODE|3308435|entityMap^0|0|0|2|N|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This flag is also worked:

<pre><code>mysqldump --skip-extended-insert 
</code></pre>

Just same as <code>--extended-insert=FALSE</code>.

blocks|key|334130|text|如果您已经有一个包含非常长的行的SQL转储，并且想要轻松地阅读它，您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334131|cat+dump.sql++%7C+sed+'s$VALUES+($VALUES\n($g'+%7C+sed+'s$),($),\n($g'+%7C+less

cat+dump.sql++%7C+sed+'s$VALUES+($VALUES\n($g'+%7C+sed+'s$),($),\n($g'+%7C+grep+something|code-block|syntax|javascript|334132|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'm using mysqldump in a cron job to backup a database with over 2 million rows.

It creates a text file which can be used to restore the datalog from the command line.

I thought it would be useful to edit the dump before a restore as a quick way of changing values and table or column names - at least until I learn more and become confident about doing it with ALTER and UPDATE.

Editing large text files does not bother me, but I was surprised to find that in a 250 megabyte dump of my database, there were only about 300 lines. Each line was something like 800k characters long.

Is there another way of generating dumps with more control over line length?

Or should I post-process the dump with tools like sed or Perl?

How to deal with enormous line lengths created by mysqldump

数据库

服务器

Windows

MySQL

Linux

Perl

我在一个cron作业中使用mysqldump来备份一个超过200万行的数据库。它创建一个文本文件，该文件可用于从命令行恢复数据记录。我认为在恢复之前编辑转储文件作为更改值和表名或列名的一种快速方法会很有用-至少在我了解更多信息并对使用ALTER和UPDATE完成这项操作有信心之前是这样。编辑大型文本文件对我来说并不麻烦...

问如何处理mysqldump产生的巨大行长度
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何处理mysqldump产生的巨大行长度EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何处理mysqldump产生的巨大行长度
EN