LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE `table` CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';
\t
tab键,制表符 插入\t避免长数值显示为"科学计数"
导入
流程逻辑
读取文件
处理数据
写入新的数据文件
导入数据文件到数据库
先生成新的数据文件,然后使用mysql的LOAD DATA INFILE语句,直接导入文件,这样对比一遍读取文件,一边插入sql.效率高很多,特别是针对于几万几十万的大数据处理,对比尤其强烈
详细代码
require'config.php';/*读取文件*/// 读取的文件$fp =fopen('E:/a.csv','r');// 写入的新文件$new_fp =fopen('E:/data.csv','a');// 循环读取数据$data =array();// 表头标记$ishead =true;// 开始循环while($da =fgetcsv($fp,1000)) {// 跳过表头if ($ishead) { $ishead =false;continue; }// 处理数据并写入新文件foreach ($da as&$d) { $d =trim($d); }fputcsv($new_fp, $da);}// 关闭文件fclose($fp);fclose($new_fp);// mysql导入csv文件到数据库$conn =@mysql_connect($config['db_host'],$config['db_user'],$config['db_pwd'])ordie("Connection occur error");mysql_select_db($config['db_name'],$conn)ordie("Connection occur error");mysql_query("LOAD DATA LOCAL INFILE 'E:/data.csv' INTO TABLE `test2` CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n';") or die ('Error: '.mysql_error());
// 删除文件// unlink('E:/a.csv');unlink('E:/data.csv');