加载数据文件默认值未插入表中 [英] Load data infile default value not inserting in table

问题描述

我正在使用LOAD DATAINFILE导入csv文件

I'm importing a csv file using LOAD DATAINFILE

csv列为NAME，TYPE，STATUS

The csv columns are NAME,TYPE,STATUS

我的表结构是

Name  : varchar
TYPE  : Varchar
Status: Tiny int default value 1

我使用此stmt:

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

当我使用上面的语句时，必须为每行插入状态值1，但不会插入相同的值. 请告知.

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.

推荐答案

在加载文件时，MySQL期望它具有与目标表相同的列数，除非您另外指定，即使缺少的列具有默认值价值.因此，为您的LOAD语句提供一个列列表，并为STATUS的值提供一个立即数1:

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)

您也可以使用SET子句来做到这一点:

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

这篇关于加载数据文件默认值未插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！