最快的方式导入csv文件到MYSQL - Fastest way to import csv file into MYSQL

Fastest way to import csv file into MYSQL

本文关键字：文件 MYSQL csv 导入方式 | 更新日期: 2023-09-26

这个方法上传csv文件到mysql，但是在csv文件中有成千上万的数据，上传数据需要花费很多时间，这很烦人。

        $deleterecords = "TRUNCATE TABLE discount"; //empty the table of its current records
        mysql_query($deleterecords);
        //readfile($name);
        //Import uploaded file to Database
        $handle = fopen($name, "r");
        $i=0;
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if($i>0){
            $import="INSERT into discount(id,title,expired_date,amount,block)values('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."')";
        //imports data serially to the allocated columns.
            mysql_query($import) or die(mysql_error());//query
        }
        $i=1;
        }
        fclose($handle);
       //closing the handle
       // print "Import done ";
       ?>
Can anyone suggest faster method for uploading data ?

您可以直接将MYSQL链接到CSV文件并使用以下SQL语法上传信息，而不是编写脚本从CSV文件中提取信息。

要将Excel文件导入MySQL，首先将其导出为CSV文件。从生成的CSV文件中删除CSV头，以及Excel可能放在CSV文件末尾的空数据。

可以通过以下命令导入到MySQL表中:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by ''n'
    (uniqName, uniqCity, uniqComments)

as read on:将CSV文件直接导入MySQL

使用LOAD DATA INFILE语句。https://dev.mysql.com/doc/refman/5.1/en/load-data.html

将数据加载到临时表中，并使用该临时表插入到主表中。

可以用这种方式插入数据。这是在表中插入行的默认方式。

    $deleterecords = "TRUNCATE TABLE discount"; //empty the table of its current records
    mysql_query($deleterecords);
    //readfile($name);
    //Import uploaded file to Database
    $handle = fopen($name, "r");
    $i=0;
    $ins = "INSERT into discount(id,title,expired_date,amount,block) values ";
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    if($i>0){
    $import .= $ins."('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."'),";
    //imports data serially to the allocated columns.
    }
    $import = rtrim($import,',');
    mysql_query($import) or die(mysql_error());//query
    $i=1;
    }
    fclose($handle);
   //closing the handle
   // print "Import done ";
   ?>

与其进行多次插入，不如构建一个大查询并执行一次插入。

<?php
$deleterecords = "TRUNCATE TABLE discount"; //empty the table of its current records
mysql_query($deleterecords);
//readfile($name);
//Import uploaded file to Database
$handle = fopen($name, "r");
$i=0;
$what_to_insert = array();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    if($i>0){
        array_push($what_to_insert, "('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."')");
    }
    $i=1;
}
fclose($handle);
if (count($what_to_insert)>0){
    $import="INSERT into discount(id,title,expired_date,amount,block) values " . implode(",", $what_to_insert);
    mysql_query($import) or die(mysql_error());//query
}
?>

如果phpMyAdmin可用，则可以使用CSV导入功能。