InnoDB:使用事务批量插入还是结合多个查询? [英] InnoDB: Bulk insert using transaction OR combine multiple queries?

问题描述

在InnoDB中进行批量INSERT时，我应该使用事务

When doing a bulk INSERT in InnoDB, should I use a transaction

START TRANSACTION;
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);
INSERT INTO tbl_name (a,b,c) VALUES(4,5,6);
INSERT INTO tbl_name (a,b,c) VALUES(7,8,9);
COMMIT TRANSACTION;

还是结合多个查询?

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

如果有关系，我正在使用PHP，而MySQL数据库在同一台计算机上.

If it matters, I'm using PHP and the MySQL database is on the same machine.

推荐答案

我建议像在底部示例中那样组合多个查询.

I'd recommend combining multiple queries like you have in the bottom example.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

如果任何一个值对失败，则不会插入任何数据.此方法还向DB发送较少的字符和往返.较少字符的含义可能没有那么吸引人，但它仍然具有轻微优势.

If either of the value-pair fails, none of the data will be inserted. This method also sends less characters and round-trip to the DB. The implication of less characters may not be that attractive but it still holds slight advantage.

蒂姆有一个很大的问题.让我包括来自 MySQL文档

Tim has a great question. Let me include information from MySQL doc

如果要同时从同一客户端插入许多行，请使用具有多个VALUES列表的INSERT语句一次插入几行.这比使用单独的单行INSERT语句要快得多(某些情况下要快很多倍).如果要将数据添加到非空表，则可以调整bulk_insert_buffer_size变量以使数据插入更快.

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

这篇关于InnoDB:使用事务批量插入还是结合多个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！