MySQL - how many rows can I insert in one single I

Question

Does it depend on the number of values sets? Does it depend on the number of bytes in the INSERT statement?

Answer 1

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

Answer 2

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/

Answer 3

Query is limited by max_allowed_packet in general.

Answer 4

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.

Answer 5

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.

Answer 6

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.