Menu
Does it depend on the number of values sets? Does it depend on the number of bytes in the INSERT statement?
340
The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.
Answer. Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement. To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.
The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES... i.e. INSERT INTO TableName( Colum1) VALUES (1), (2), (3),...... upto 1000 rows.
To improve insert performance you should use batch inserts. insert into table my_table(col1, col2) VALUES (val1_1, val2_1), (val1_2, val2_2); Storing records to a file and using load data infile yields even better results (best in my case), but it requires more effort.
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.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
