如何在MySQL中插入大量行? [英] How do I insert a large number of rows in MySQL?

问题描述

如何通过单个查询将10万行插入到MySQL表中?

How do I insert for example 100 000 rows into MySQL table with a single query?

推荐答案

insert into $table values (1, a, b), (2, c, d), (3, e, f);

这将插入3行.根据需要继续达到100,000.在进行ETL工作时，我会以这种方式处理大约1,000个数据块.

That will perform an insertion of 3 rows. Continue as needed to reach 100,000. I do blocks of ~1,000 that way when doing ETL work.

如果您的数据静态存储在文件中，则将其转换并使用在文件中加载数据将是最好的方法，但我猜您是在问这个问题，因为您做了类似的事情.

If your data is statically in a file, transforming it and using load data infile will be the best method, but I'm guessing you're asking this because you do something similar.

还要注意其他人对max_allowed_pa​​cket大小的限制，该大小限制了查询的长度.

Also note what somebody else said about the max_allowed_packet size limiting the length of your query.

这篇关于如何在MySQL中插入大量行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！