如何在 MySQL 中插入大量行?

如何通过单个查询将例如 100 000 行插入 MySQL 表?

解决方案

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

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

如果您的数据静态保存在文件中，则对其进行转换并使用 load data infile 将是最好的方法，但我猜您之所以问这个问题是因为您做了类似的事情.p>

还要注意其他人所说的关于限制查询长度的 max_allowed_pa​​cket 大小.

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);

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.

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