Menu
I have a pretty simple question. I am inserting a lot of records at once in a MySQL table. It works for about 2000 records (actually a bit more). But say I want to insert 3000 records, than it doesn't do anything.
I'm working through AS3 sending an array containing all the records via AMFPHP to a simple PHP script to parse and insert the array.
Is this normal, or should I look into it?
Currently I'm slicing my array in parts of 2000 records, and sending a couple AMFPHP requests instead of just 1.
786
How can insert 1000 records at a time in MySQL? MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name.
Inserting Multiple Rows into a Table. One can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.
A table can store upto 1000 rows in one insert statement. If a user want to insert multiple rows at a time, the following syntax has to written.
The number of rows that you can insert at a time is 1,000 rows using this form of the INSERT statement. If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table.
PHP's queries are limited by the "max_allowed_packet" configuration option. It defines the absolute length limit, in characters, that a query string can be. Note that this isn't just the total size of the data being inserted, it's the entire query string. SQL commands, punctuation, spaces, etc...
Check how long your 3000 record version is vs. the 2000 one, and then get your server's packet length limit:
SHOW VARIABLES WHERE Variable_name LIKE '%max_allowed_packet%'
If your 3000-record version is longer than this limit, the query will defnitely fail because it'll be chopped off somewhere part-way
138
I don't think there is really a limit in the number of inserts in one query.
Instead, there is a limit in the size of the query you can send to MySQL
See :
max_allowed_packet
So, basically, this depends on the amount of data you have in each insert.
21
I would ensure max_allowed_packet is larger than your PHP SQL query.
http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
35
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
