MySQL插入限制? [英] MySQL insert limit?

问题描述

我有一个使用csv并将其导入到mysql表中的php脚本.我做了几次测试，发现该脚本一次只插入999行.有这样的限制吗?如果有，在哪里? MySQL的或PHP的?例如，如果我使用了2500行的csv文件，则只会插入前999个文件.我很困惑.我已经将以下php参数设置为一个相当大的值:

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

我不确定是什么原因导致了这种行为.即使将post_max_size设置为巨大的数字，我仍然感觉到html表单被截断了；我之所以这样说，是因为当我在长格式的末尾放置一个单选按钮时，下一步不会在$ _POST数组中传递该单选按钮.

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.

已解决

好的，谢谢您的帮助.我在浏览日志时发现了问题.正如我所怀疑的那样，$ _ POST数组已被切断.我查看了php日志并找到了以下条目:

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')

即使我已经在php中增加了post_max_size，但这还不够.因此，我不得不在/etc/php5/conf.d/suhosin.ini

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

我提高了价值，并且奏效了. 〜3000行全部导入.

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

推荐答案

我想CSV导入没有这种限制，但是我会使用类似的方式从CSV导入数据

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';

这篇关于MySQL插入限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！