MySQL - how many rows can I insert in one single INSERT statement?
MysqlMultiple InsertMysql Problem Overview
Does it depend on the number of values sets? Does it depend on the number of bytes in the INSERT statement?
Mysql Solutions
Solution 1 - Mysql
You can insert infinitely large number of records using INSERT ... SELECT pattern, provided you have those records, or part of, in other tables.
But if you are hard-coding the values using INSERT ... VALUES pattern, 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.
Solution 2 - Mysql
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
Solution 3 - Mysql
Query is limited by max_allowed_packet in general.
Solution 4 - Mysql
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.
Solution 5 - Mysql
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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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.
Solution 8 - Mysql
It is limited by max_allowed_packet.
You can specify by using:
mysqld --max_allowed_packet=32M
It is by default 16M.
You can also specify in my.cnf in /etc/mysql/