使用PHP将大量数据插入MySQL(Inserting a lot of data into MySQL using PHP)

 所以我的情况如下。 我每24小时通过PHP从某个来源获取数据。 现在，在此期间，此数据会发生变化，添加新数据或更新某些数据。 现在，当我运行查询以每24小时插入或更新此数据时，查询执行需要很长时间。 如果执行时间适中，这不会成为问题，因为我计划将其作为一个cron作业，但是现在执行时间太长了。 所以当我获得它时，我正考虑将这些数据从PHP写入CSV文件。 然后将此CSV文件与MySQL LOAD DATA函数一起使用，该函数应该以非常快的方式从文件中插入大量数据。  
 所以问题是可以从PHP写入CSV文件，并以适合LOAD DATA INFILE功能的方式对其进行格式化，如何每24小时删除该CSV并使用新插入的数据创建新的CSV，以及如何在这个特定的CSV文件中正确使用LOAD DATA INFILE功能？ 哦，我可以从这一切中做出一份cron工作吗？ 谢谢。 

So my situation is the following. I obtain data from a certain source through PHP every 24 hours. Now during that time this data gets changed, new data is added or some is updated. Now when I run a query to insert or update this data each 24 hours it takes a long time for the query to execute. This would not be a problem if the execution time was moderate as I am planning on making this a cron job, however the execution time now is way too high. So I was thinking about writing this data from PHP to a CSV file, when I obtain it. And then using this CSV file together with the MySQL LOAD DATA function which supposedly inserts a lot of data from a file in a very quick manner. 
So the question is it possible to write to a CSV file from PHP and have it formatted in a way that suits the LOAD DATA INFILE function, and how can I each 24 hours delete that CSV and create a new one with newly inserted data, and how would I go about properly using the LOAD DATA INFILE function with this particular CSV file? Oh and can I make a cron job out of all of this ? Thanks.

原文：https://stackoverflow.com/questions/16831090

更新时间：2022-03-21 14:03

最满意答案

 假设您从源接收数据并准备此数组：  
$x=array(1=>array('1','2','3','4'), 2=>array('5','6','7','8'));
 
 创建一个这样的csv文件：  
    $file=fopen(<DIRECTORY_PATH>."file.csv","w");
    if(!$file){
       //error
    }

    $csv_data="";

    foreach($x as $row){
     foreach($row as $element){
        $csv_data.=$element.", ";
     }
    //remove the last comma
    $csv_data.="\n";

    }

    fwrite($file,$csv_data);

$query="load data infile '".<DIRECTORY_PATH>."file.csv"."' into table your_table";
if(!mysqli->query($query))
 printf("Error: %s\n", $mysqli->error);

assume you receive data from your source and prepare this array: 
$x=array(1=>array('1','2','3','4'), 2=>array('5','6','7','8'));
 
create a csv file like this: 
    $file=fopen(<DIRECTORY_PATH>."file.csv","w");
    if(!$file){
       //error
    }

    $csv_data="";

    foreach($x as $row){
     foreach($row as $element){
        $csv_data.=$element.", ";
     }
    //remove the last comma
    $csv_data.="\n";

    }

    fwrite($file,$csv_data);

$query="load data infile '".<DIRECTORY_PATH>."file.csv"."' into table your_table";
if(!mysqli->query($query))
 printf("Error: %s\n", $mysqli->error);