MySQL - 我可以在一个 INSERT 语句中插入多少行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3536103/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - how many rows can I insert in one single INSERT statement?
提问by Luistar15
Does it depend on the number of values sets? Does it depend on the number of bytes in the INSERT statement?
它是否取决于值集的数量？它是否取决于 INSERT 语句中的字节数？
回答by Lukman
You can insert infinitely large number of records using INSERT ... SELECTpattern, provided you have those records, or part of, in other tables.
您可以使用INSERT ... SELECT模式插入无限多的记录，前提是您在其他表中拥有这些记录或其中的一部分。
But if you are hard-coding the values using INSERT ... VALUESpattern, then there is a limit on how large/long your statement is: max_allowed_packetwhich limits the length of SQL statements sent by the client to the database server, and it affects any types of queries and not only for INSERT statement.
但是，如果您使用INSERT ... VALUES模式对值进行硬编码，那么您的语句的大小/长度会有一个限制：max_allowed_packet限制了客户端发送到数据库服务器的 SQL 语句的长度，它会影响任何类型的查询不仅适用于 INSERT 语句。
回答by Raju akula
Ideally, Mysql allow infinite number of rows creation in single insert (at once) but when a
理想情况下，Mysql 允许在单个插入（一次）中创建无限数量的行，但是当
MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.
MySQL 客户端或 mysqld 服务器收到大于 max_allowed_packet 字节的数据包，它发出数据包太大错误并关闭连接。
To view what the default value is for max_allowed_packet variable, execute the following command in in MySQL:
要查看 max_allowed_packet 变量的默认值，请在 MySQL 中执行以下命令：
show variables like 'max_allowed_packet';
show variables like 'max_allowed_packet';
Standard MySQL installation has a default value of 1048576 bytes (1MB). This can be increased by setting it to a higher value for a session or connection.
标准 MySQL 安装的默认值为 1048576 字节 (1MB)。这可以通过为会话或连接将其设置为更高的值来增加。
This sets the value to 500MB for everyone (that's what GLOBAL means):
这会将每个人的值设置为 500MB（这就是 GLOBAL 的意思）：
SET GLOBAL max_allowed_packet=524288000;
SET GLOBAL max_allowed_packet=524288000;
check your change in new terminal with new connection:
使用新连接检查新终端中的更改：
show variables like 'max_allowed_packet';
show variables like 'max_allowed_packet';
Now it should work without any error for infinite records insert. Thanks
现在，对于无限记录插入，它应该可以正常工作而不会出现任何错误。谢谢
回答by Dmytro Lytovchenko
Query is limited by max_allowed_packetin general.
查询受限max_allowed_packet于一般。
回答by clark yu
refer to http://forums.mysql.com/read.php?20,161869, it's related with your mysql's configuration: max_allowed_packet, bulk_insert_buffer_size, key_buffer_size.
参考http://forums.mysql.com/read.php?20,161869，和你mysql的配置有关：max_allowed_packet, bulk_insert_buffer_size, key_buffer_size。
回答by Borealid
You can insert an infinite number of rows with one INSERT statement. For example, you could execute a stored procedure that has a loop executed a thousand times, each time running an INSERT query.
您可以使用一个 INSERT 语句插入无限多的行。例如，您可以执行一个循环执行一千次的存储过程，每次运行一个 INSERT 查询。
Or your INSERT could trip a trigger which itself performs an INSERT. Which trips another trigger. And so on.
或者您的 INSERT 可能会触发本身执行 INSERT 的触发器。这会触发另一个触发器。等等。
No, it does not depend on the number of value sets. Nor does it depend on the number of bytes.
不，它不取决于值集的数量。它也不取决于字节数。
There is a limit to how deeply nested your parentheses may be, and a limit to how long your total statement is. Both of these are referenced, ironically, on thedailywtf.com . However, both of the means I mentioned above get around these limits.
括号嵌套的深度有限制，整个语句的长度也有限制。具有讽刺意味的是，thedailywtf.com 上引用了这两者。但是，我上面提到的两种方法都绕过了这些限制。
回答by bronze man
You will hit the max_allowed_packet limit and
您将达到 max_allowed_packet 限制并且
error: 1390 Prepared statement contains too many placeholders.
错误：1390 准备好的语句包含太多占位符。
You can put 65535 placeholders in one sql.So if you have two columns in one row,you can insert 32767 rows in one sql.
您可以在一个sql中放置65535个占位符。因此如果您在一行中有两列，则可以在一个sql中插入32767行。
在 MySQL 中导入 50K+ 记录给出一般错误：1390 Prepared statement contains too many placeholders
回答by linehrr
It is limited by max_allowed_packet.
You can specify by using:
mysqld --max_allowed_packet=32MIt is by default 16M.
You can also specify in my.cnf in /etc/mysql/
它受 max_allowed_packet 的限制。
您可以使用以下方式指定：
mysqld --max_allowed_packet=32M默认为 16M。
也可以在/etc/mysql/中的my.cnf中指定
回答by DMags
I believe there's no defined number of rows you're limited to inserting per INSERT, but there may be some sort of maximum size for queries in general.
我相信没有定义的行数限制在每个 INSERT 中插入，但通常查询可能有某种最大大小。
