106

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

Michael McGowan
  • 6,528
  • 8
  • 42
  • 70
Luistar15
  • 1,699
  • 3
  • 14
  • 16

8 Answers8

103

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.

Lukman
  • 18,462
  • 6
  • 56
  • 66
60

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

Raju akula
  • 1,255
  • 1
  • 13
  • 18
  • 14
    500 MB isn't infinite. It's way much larger than default value, but still, not infinite. – Carlos2W Jun 20 '16 at 19:11
  • 1
    A question: what exactly is this limit for? The query itself? What about the data, bind when executing statement? Can I be safe, until `strlen($query_with_questionmarks) < $max_alloweed_packet`? – pilat Oct 12 '18 at 08:25
  • If you were trying to set it to the largest possible value, that is 1GB which would look like this: SET GLOBAL max_allowed_packet=1073741824 – PHP Guru Oct 15 '20 at 15:22
25

Query is limited by max_allowed_packet in general.

Simon
  • 31,675
  • 9
  • 80
  • 92
Dmytro Lytovchenko
  • 359
  • 3
  • 2
13

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

bronze man
  • 1,470
  • 2
  • 15
  • 28
9

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.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
clark yu
  • 109
  • 3
  • 3
6

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.

Borealid
  • 95,191
  • 9
  • 106
  • 122
  • Your examples are for running multiple INSERT, and it does not show about inserting multiple rows in **one** INSERT statement .. – Lukman Aug 21 '10 at 03:17
  • @Lukman: One INSERT query can result in multiple INSERTS hitting the database. It's just a matter of who's counting what. – Borealid Aug 21 '10 at 03:19
  • 2
    true that, but your 1st example show about running query in a loop with each loop running one INSERT statement, and it says nothing about that one INSERT statement inserting multiple rows. it's just a matter of emphasis. – Lukman Aug 21 '10 at 03:24
  • Nobody can do infinite anything. There are always resource limits. – Adrien May 17 '17 at 00:50
  • Does this apply to SELECT ... FROM statement too? I mean if data has been inserted in the first place with max_allowed_packet, shouldn't this be ale to be selected after that too?. I don't get it why it's applied to all other statements like the other poster said when it first has to be inserted before anything else anyway. – Thielicious Feb 18 '20 at 18:01
3

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/

linehrr
  • 1,668
  • 19
  • 24
3

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.

DMags
  • 1,621
  • 1
  • 13
  • 12