MySQL - 我可以在一个 INSERT 语句中插入多少行？
它取决于值集的数量吗？它取决于 INSERT 语句中的字节数吗？
Does it depend on the number of values sets? Does it depend on the number of bytes in the INSERT statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
您可以使用 INSERT ... SELECT 模式插入无限多的记录，前提是您在其他表中拥有这些记录或部分记录。
但是，如果您使用
INSERT ... VALUES模式对值进行硬编码，则语句的大小/长度会受到限制：max_allowed_packet 它限制客户端发送到数据库服务器的 SQL 语句的长度，并且它会影响任何类型的查询而不仅仅是 INSERT 语句。You can insert infinitely large number of records using
INSERT ... SELECTpattern, provided you have those records, or part of, in other tables.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_packet which 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.理想情况下，Mysql 允许在单个插入中（一次）创建无限数量的行，但是当
MySQL 客户端或 mysqld 服务器收到大于 max_allowed_packet 字节的数据包时，它会发出数据包太大错误并关闭连接。
要查看 max_allowed_packet 变量的默认值，请在 MySQL 中执行以下命令：
show variables like 'max_allowed_packet';标准 MySQL 安装的默认值为 1048576 字节 (1MB)。可以通过将会话或连接的值设置为更高的值来增加此值。
这会将每个人的值设置为 500MB（这就是 GLOBAL 的含义）：
SET GLOBAL max_allowed_packet=524288000;使用新连接检查新终端中的更改：
显示诸如“max_allowed_packet”之类的变量；code>现在它应该可以在无限记录插入时没有任何错误。谢谢
Ideally, Mysql allow infinite number of rows creation in single insert (at once) but when a
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.
To view what the default value is for max_allowed_packet variable, execute the following command in in MySQL:
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.
This sets the value to 500MB for everyone (that's what GLOBAL means):
SET GLOBAL max_allowed_packet=524288000;check your change in new terminal with new connection:
show variables like 'max_allowed_packet';Now it should work without any error for infinite records insert. Thanks
一般来说，查询受到
max_allowed_packet的限制。Query is limited by
max_allowed_packetin general.您将达到 max_allowed_packet 限制并
出现错误：1390 准备好的语句包含太多占位符。
您可以在一条 sql 中放入 65535 个占位符。因此，如果一行中有两列，则可以在一条 sql 中插入 32767 行。
在 MySQL 中导入 50K+ 记录给出一般错误：1390 准备好的语句包含太多占位符
You will hit the max_allowed_packet limit and
error: 1390 Prepared statement contains too many placeholders.
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.
Import of 50K+ Records in MySQL Gives General error: 1390 Prepared statement contains too many placeholders
参考http://forums.mysql.com/read.php?20,161869，它是相关的与您的 mysql 配置：
max_allowed_packet、bulk_insert_buffer_size、key_buffer_size。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.您可以使用一个 INSERT 语句插入无限数量的行。例如，您可以执行一个存储过程，其中循环执行一千次，每次运行一个 INSERT 查询。
或者您的 INSERT 可能会触发本身执行 INSERT 的触发器。这会触发另一个触发器。等等。
不，它不取决于值集的数量。它也不取决于字节数。
括号的嵌套深度是有限制的，整个语句的长度也是有限制的。讽刺的是，thedailywtf.com 上引用了这两个内容。然而，我上面提到的两种方法都绕过了这些限制。
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.
Or your INSERT could trip a trigger which itself performs an INSERT. Which trips another trigger. And so on.
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.
我相信每次 INSERT 插入的行数没有定义，但一般来说查询可能有某种最大大小。
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.
它受到 max_allowed_packet 的限制。
您可以使用以下方式指定：
mysqld --max_allowed_packet=32M默认为16M。
也可以在/etc/mysql/中的my.cnf中指定
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/