MySQL在单个插入中插入2万行 [英] MySQL Insert 20K rows in single insert

问题描述

在我的表格中，每次加载时我都会插入大约20,000行.现在，我正在一张一张地做.从mysql网站，我知道用单个插入查询插入多行速度更快.

In my table I insert around 20,000 rows on each load. Right now I am doing it one-by-one. From mysql website I came to know inserting multiple rows with single insert query is faster.

我可以在单个查询中插入所有20000吗?

Can I insert all 20000 in single query?

如果此20000行中有错误，将会发生什么? mysql将如何处理?

What will happen if there are errors within this 20000 rows? how will mysql handle that?

推荐答案

如果要插入其他表中的行，则可以使用

If you are inserting the rows from some other table then you can use the INSERT ... SELECT pattern to insert the rows.

但是，如果要使用INSERT ... VALUES模式插入值，则将具有max_allowed_pa​​cket的限制.

However if you are inserting the values using INSERT ... VALUES pattern then you have the limit of max_allowed_packet.

也来自文档:-

要优化插入速度，请将许多小的操作组合为一个 大手术.理想情况下，您进行单个连接，发送数据 一次处理许多新行，并延迟所有索引更新和一致性 检查直到最后.

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

示例:-

INSERT INTO `table1` (`column1`, `column2`) VALUES ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2"),
                                                 ("d1", "d2");

如果这20000行中有错误，将会发生什么?

What will happen if there are errors within this 20000 rows?

如果在插入记录时出现错误，则该操作将被中止.

If there are errors while inserting the records then the operation will be aborted.

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