MySQL - how many rows can I insert in one single INSERT statement?

Solution 1:

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:

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:

Query is limited by max_allowed_packet in general.

Solution 4:

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

Solution 5:

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.