php - 向mysql数据库添加数据时无法访问网站

Question

我正在笔记本电脑上使用 wamp 进行一个项目。我有大约 17000 行和 6 列数据要添加到数据库的 4 个不同表中。这是通过上传 excel 文件并单击导入按钮来完成的。注意!我正在使用 excel 到 mysql 插件，这对于项目来说是必需的，因为它是程序的功能之一。通常不会有那么多的数据插入到表中。尽管需要很长时间，但一切正常。我只是担心在上传过程中整个网站无法访问。下面是上传脚本。有什么技巧可以改进以下脚本以加快插入速度并在插入繁忙时使站点可访问吗？

<?php
$conn = mysqli_connect("localhost","root","","eftposcentral");
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');

    if (isset($_POST["import"]))
        {     
        $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

            if(in_array($_FILES["file"]["type"],$allowedFileType)){
                $targetPath = 'uploads/'.$_FILES['file']['name'];
                move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

                $Reader = new SpreadsheetReader($targetPath);

                $sheetCount = count($Reader->sheets());
                    for($i=0;$i<$sheetCount;$i++)
                        {                                        
                            $Reader->ChangeSheet($i);

                                foreach ($Reader as $Row)
                                    {                                    
                                        $po = "";
                                        if(isset($Row[0])) {
                                            $po = mysqli_real_escape_string($conn,$Row[0]);
                                        }
                                        $business = "";
                                        if(isset($Row[1])) {
                                            $business = mysqli_real_escape_string($conn,$Row[1]);                                                
                                        }                                            
                                        $model = "";
                                        if(isset($Row[2])) {
                                            $model = mysqli_real_escape_string($conn,$Row[2]);                                                
                                        }
                                        $serial = "";
                                        if(isset($Row[3])) {
                                            $serial = mysqli_real_escape_string($conn,$Row[3]);
                                        }
                                        $freight = "";
                                        if(isset($Row[4])) {
                                            $freight = mysqli_real_escape_string($conn,$Row[4]);
                                        }
                                        $depreciation = "";
                                        if(isset($Row[5])) {
                                            $depreciation = mysqli_real_escape_string($conn,$Row[5]);
                                        }
                                        $date_rec = "";
                                        if(isset($Row[6])) {
                                            $date_rec = mysqli_real_escape_string($conn,$Row[6]);
                                        }

                                        $raw_results = mysqli_query($conn,"SELECT serial FROM device_current_info
                                            WHERE (`serial` = $serial)");

                                            $results = mysqli_fetch_array($conn,$raw_results);

                                                if($results > 0){
                                                    $type = "error";
                                                    $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                }
                                                else{
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {
                                                            //Adds assets to the terminal_info table. 
                                                            $query = "insert IGNORE into device_info(po,business,model,serial,freight,depreciation,date_rec) values('".$po."','".$business."','".$model."'
                                                            ,'".$serial."','".$freight."','".$depreciation."','".$date_rec."')";
                                                            $result = mysqli_query($conn, $query)or die(mysqli_error($conn));

                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {


                                                            //Adds terminals to the terminal_current_info table. Terminals will be linked on this table and only current info will be stored.
                                                            $currenLocation ="Stores"; //Default location for all new assets.
                                                            $newComment ="New asset"; //Default comments for all new assets.
                                                            $currentStatus = "Available";//Default status for all new assets.
                                                            $query2 = "insert IGNORE into device_current_info(business,model,serial,current_status,current_location,comments) values('".$business."','".$model."'
                                                            ,'".$serial."','".$currentStatus."','".$currenLocation."','".$newComment."')";
                                                            $result2 = mysqli_query($conn, $query2) or die(mysqli_error($conn));

                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }
                                                        if (!empty($po) || !empty($model) || !empty($serial) || !empty($freight) || !empty($depreciation)|| !empty($date_rec) ) {


                                                            //Creates first terminal movement. Every time a terminal is moved it this table will be updated.
                                                            $user = $_SESSION['login_user'];
                                                            $previousLocation ="Vendor"; //Default previoius location for all new assets.
                                                            $movementLocation ="Stores"; //Default location for all new assets.
                                                            $movementComment ="New asset"; //Default comments for all new assets.
                                                            $movementStatus ="Available"; //Default status for all new assets.
                                                            $query3 = "insert IGNORE into device_movements(serial,previous_location,movement_location,user,status,comments) values(
                                                            '".$serial."','".$previousLocation."','".$movementLocation."','".$user."','".$movementStatus."','".$movementComment."')";
                                                            $result3 = mysqli_query($conn, $query3) or die(mysqli_error($conn));

                                                            $query4 = "insert into activity_log(user_name,activity,old_value,new_value) values(
                                                                '".$user."','Added','','".$serial."')";
                                                                $result4 = mysqli_query($conn, $query4) or die(mysqli_error($conn));


                                                            if (! empty($result)) {
                                                                $type = "success";
                                                                $message = "Assets added into the Database";
                                                            } else {
                                                                $type = "error";
                                                                $message = "Problem in Importing assets into the Database" .mysqli_error($conn);
                                                            }
                                                        }

                                                    }


                                            }

                                        }


                                    }


                        }
                        else
                        { 
                                $type = "error";
                                $message = "Invalid File Type. Upload Excel File.";
                        }

?>

Answer 1

如果您使用 SpreadSheet-Plugin 将数据传输到 csv 文件，您可能会提高速度。然后，您可以使用带有 LOAD DATA LOCAL INFILE 的 mysql 导入 csv 文件。 (参见 MySQL Doku)

这个 MySQL 函数非常非常(是的，非常)快，因此其余部分将取决于您的电子表格插件的速度。

当然，这与其说是一种解决方案，不如说是一种新方法。