今天一朋友遇到将excel数据导入到mysql数据库, 20多万条的数据导入到mysql需要两分多钟,老板认为这个速度太慢了,于是我们就一起想办法,最终把时间缩短到13秒左右, 这里把解决问题的经过给大家做个分享.
两分钟是这样的: 用phpexcel读取文件,然后通过foreach循环使用tp的插入数据库方法
测试1,读取phpexcel, 然后做个循环,不插入数据库就需要 1分多钟,于是就意识到读取 excel太花时间,那么有没有更好的文件可以读取呢
测试2, 通过搜索,可以通过PHP直接读取csv格式的文件,于是,换成 csv的文件读取,时间少了一半,但仍然觉得时间太久
测试3, tp插入数据库的方法比较花时间,然后换成原来 php连接插入数据库, 时间再次所短一半,这个时候还需要 30 多秒,能不能再减少了
测试4, 思考:既然循环需要时间,那么是否可以大批量插入呢,经过搜索,原来sql语句还可以这样写:
INSERT table_name (column1, column2, ..., columnN) VALUES (rec1_val1, rec1_val2, ..., rec1_valN), (rec2_val1, rec2_val2, ..., rec2_valN), ... ... (recM_val1, recM_val2, ..., recM_valN); 这里可以写多行哟,我写了1万行,没有问题
测试5, 把20多万分成20多次分批导入,时间再次缩短了很多,最终 13秒 搞定[本人电脑配置一般,电脑好应该会再快一些]
代码如下:
$filepath = EXTEND_PATH."ccc.csv";
$dsn = "mysql:host=localhost;dbname=aftts";
$db = new \PDO($dsn, 'root', 'root'); //PHP Data Object
$db->exec("SET names 'utf8'");//保证数据库添加不出现乱码
$start = time();
//csv 导入数据开始
$file = fopen($filepath,'r');
$data = fgetcsv($file);
$sql = "INSERT INTO tp_test (a, b,c,d,e,f,g) VALUES";
$s = "";
$i=1;
while ($data = fgetcsv($file)) {
//每次读取CSV里面的一行内容
$a = $data[0];
$b = $data[1];
$c = iconv('gb2312','utf-8',$data[2]);
//中文转码,否则乱码 或者 mb_convert_encoding($data[0],'utf-8','gbk');
$d = $data[3];
$e = iconv('gb2312','utf-8',$data[4]);
$f = $data[5];
$g = iconv('gb2312','utf-8',$data[6]);
if($<10000)//一次性给数据库过大,数据库受不了的
{ $s .= "('$a','$b','$c','$d','$e','$f','$g'),";}
else{
$dd[] = $sql . substr($s,0,strlen($s)-1);
$s = "('$a','$b','$c','$d','$e','$f','$g'),";
$i = 1;
}
$i++;
}
if($i<10000){
$dd[] = $sql . substr($s,0,strlen($s)-1);
}
foreach ($dd as $sql) {
$db->exec($sql);
}
注意, 如果遇到下面的格式, 导入可能会有问题, 这个时候需要先在csv文件里面转换一下格式