Load data infile default value not inserting in table

mysql, php

Solution

When loading a file, MySQL expects that it has the same number of columns as the destination table, unless you specify otherwise, even if the missing column has a default value. So supply a column list to your `LOAD` statement, and a literal 1 for the value of `STATUS`:

LOAD DATA INFILE '/var/www/names.csv' 
INTO TABLE users 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  IGNORE 1 LINES 
  (`name`, `type`, 1)

You can also do it with a `SET` clause:

LOAD DATA INFILE '/var/www/names.csv' 
INTO TABLE users 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  IGNORE 1 LINES 
  (`name`, `type`)
  SET `status` = 1

Problem

I'm importing a csv file using LOAD DATAINFILE The csv columns are NAME,TYPE,STATUS my table structure is ``` Name : varchar TYPE : Varchar Status: Tiny int default value 1 ``` I use this stmt : ``` LOAD DATA INFILE '/var/www/names.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES ``` When I use the above statement it has to insert the status value as 1 for each row, but it is not inserting the same. Please advise.

Original source