MySQL insert limit?

问题:

I've a php script that takes a csv and imports it into a mysql table. I've done few tests and I found out that the script is only inserting 999 rows at a time. Is there such a limit and if so, where? Mysql or php? For example, if I used a csv file with 2500 rows, only the first 999 would get inserted. I'm baffled. I've set the following php parameters to a pretty generous value:

php_value upload_max_filesize 999M
php_value post_max_size 999M
php_value max_execution_time 900
php_value max_input_time 900

I'm not sure what's causing this behaviour. I've a feeling the html form is being cut off even though I've set the post_max_size to huge number; I say this because when I put a radio button at the end of the long form, that radio button isn't passed in the $_POST array in the next step.

---

SOLVED

Ok guys, thanks for trying to help. I was digging through logs and found the problem. As I suspected, the $_POST array was being cut off. I looked at php logs and found this entry:

Feb 17 17:12:54 workpc suhosin[3895]: ALERT - configured POST variable limit exceeded - dropped variable 'selected[]' (attacker '192.168.0.175', file '/var/www/html/sandbox/index.php')

Even though I had increased the post_max_size in php, it was not enough. Thus, I had to changed these two parameters in /etc/php5/conf.d/suhosin.ini

suhosin.post.max_vars = 1000
suhosin.request.max_vars = 1000

I increased the value and it worked. All ~3000 rows got imported.

回答1:

I guess there is no such limit on CSV imports but I would use something like this for importing data from CSVs

LOAD DATA INFILE 'mydata.csv' INTO TABLE YOUR_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';