使用CI中的加载数据InFile上载500000条记录，但有时不会插入所有记录

Question

I am uploading 500 000 records using load data InFile in CI but sometime all record inserted or sometime thousand records not inserted in same csv file.

Controller

public function upload_csv_test()
{
    $adminid = $this->session->userdata('aid');
    if(empty($adminid))
    {
        redirect('admin/index');
    }
    else
    { 
        $file_path = "/var/www/html/asset/test_csv/5Lac.csv";
        $sub = $this->Admin_model->add_csv_data($file_path);      
    } 
}

Model

public function add_csv_data($file_path)
{   

    $this->db->query("LOAD DATA LOCAL INFILE '".$file_path."' 
    INTO TABLE test_csv     
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\
' 
    IGNORE 1 LINES
    (cid,latitude,longitude,Email,Personal_FirstName)
    ");

}

Answer 1

That functionality doesn't insert any data if the database would throw an error. Usually it is when a column has the wrong format like a date or decimal numbers.

If you can detect one file on which it fails, take it, split it in smaller batches and look at the values. You could even transform it into a normal insert-command and test it with the failing data. Good luck!