duanmeng2842 2017-10-03 11:40
浏览 74
已采纳

如何更快地使用nullif插入Mysql?

i am loading a CSV file through PHP and inserting into a temporary table, this is what my loading PHP script and MySQL query.

    $i=0;
    while (($data = fgetcsv($source, 1000, ",")) !== FALSE)
        {   
            if($i!=0) {
                $column1=$data[0];
                $column2=$data[1];
                $column3=$data[2];
                $column4=$data[3];
                $column5=$data[4];
                $column6=$data[5];
                $query= "INSERT INTO temp_table(column1,column2,column3,column4,column5,column6,load_datetime)
    VALUES (nullif('$column1',''), nullif('$column2',''), nullif('$column3',''), nullif('$column4',''), nullif('$column5',''), nullif('$column6',''),now())";
                mysql_query($query) or die(mysql_error());
            }
            $i++;
        }

and my CSV file has 25000 records, this is a sample row from my CSV

        column1,column2,column3,column4,column5,column6
        ,,,13:57:18,,23:00:19

the problem i face is, it takes more than 10 minutes when i load the CSV and in between the page goes blank. My PHP settings: upload_max_filesize: 100M post_max_size:100M max_execution_time:1000 max_input_time:1000.

this my table detail

   CREATE TABLE temp_table (
       id int(11) NOT NULL,
       column1 time DEFAULT NULL,
       column2 time DEFAULT NULL,
       column3 time DEFAULT NULL,
       column4 time DEFAULT NULL,
       column5 time DEFAULT NULL,
       column6 time DEFAULT NULL,
       load_datetime datetime DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and when i check the temp_table it has inserted around 8000 records. please help me with an approach to insert faster.

  • 写回答

2条回答 默认 最新

  • dongyong2063 2017-10-03 12:01
    关注

    I would be recommended to you stop use mysql extension (mysql_*)
    and start use mysqli or PDO.

    Try to group data of a few rowns into one query.

    Example:

    $i  = 0;
    $i2 = 0;
    $sql = "INSERT INTO temp_table(column1,column2,column3,column4,column5,column6,load_datetime) VALUES ";
    $values = [];
    while (($data = fgetcsv($source, 1000, ",")) !== FALSE) {
        if(++$i == 1)
            continue;
        else {
            $data = array_map(function($item) {
                return $item == '' ? 'NULL' : "'$item'";
            }, $data);
            $values[] = "(" . implode(", ", $data) . ', now()' . ")";
            if (++$i2 == 500) {
                mysql_query ($sql . implode(', ', $values)) or die(mysql_error());
                $values = [];
                $i2 = 0;
            }
        }
    }
    if (!empty($values)) {
        mysql_query ($sql . implode(', ', $values)) or die(mysql_error());
        $values = [];
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥30 用arduino开发esp32控制ps2手柄一直报错
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题