Mysql 'LOAD DATA LOCAL INFILE' 根据文件指定列的值

问题描述

我遇到了必须从 3 个独立的大型 CSV 文件中读取数据并将其存储在 MySQL 数据库中的情况.

I am stuck in a situation where I have to read data from 3 separate large CSV files and store it in MySQL database.

csv 文件列:

total_rev,monthly_rev,day_rev

数据库表的列:

total_rev、monthly_rev、day_rev，从

我可以使用以下查询在我的表中插入数据:

I am able to insert data in my table by using following query:

LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'
INTO TABLE revenue_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(total_rev,monthly_rev,day_rev);

但我想在Load Data Local"查询中添加硬编码参数以根据文件添加from的值.

But I am stuck in a situation where I want to add hard coded parameter in the "Load Data Local" query to add from's value depending upon the file.

所以最后我的表将包含如下记录:

so at the end my table will contain records like:

total_rev, monthly_rev, day_rev,  from
  11,       222,          333,    file1
  22,        32,          343,    file1
  11,        22,          333,    file1
  11,        22,           33,    file22
  11,        22,           33,    file22

如何在上述查询中指定此 file1、file22 值?

How can I specify this file1, file22 value's in above query ?

推荐答案

在 LOAD DATA 语句中添加一个 SET 子句，为(不幸命名的)from 赋值代码>列:

Add a SET clause to the LOAD DATA statement, to assign a value to the (unfortunately named) from column:

 LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'
 INTO TABLE revenue_table
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 (total_rev,monthly_rev,day_rev)

 SET `from` = 'file1'
 ;

请注意，也可以将文件中的字段加载到用户定义的变量中和/或在 SET 子句中引用用户定义的变量.

---

Note that it's also possible to load the fields from the file into user-defined variables and/or reference user-defined variables in the SET clause.

 SET @file_name = 'myfilename'
 ;

 LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'
 INTO TABLE revenue_table
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 ( @total_rev
 , @monthly_rev
 , @day_rev
 )
 SET `total_rev`   = @total_rev
   , `monthly_rev` = @monthly_rev
   , `day_rev`     = @day_rev
   , `from`        = @file_name
 ;

如果我们想要跳过文件中的字段，而不是加载到表中，我们可以使用用户定义的变量作为占位符.我们还可以在 SET 子句中使用表达式，这允许我们利用一些非常有用的 MySQL 函数进行一些操作...... IFNULL、NULLIF、STR_TO_DATE、CONCAT 等.

If we had fields in the file we want to skip, not load into the table, we can use a user-defined variable as a placeholder. We can also use expressions in the SET clause, which allows us to leverage some very useful MySQL functions for some manipulation... IFNULL, NULLIF, STR_TO_DATE, CONCAT, etc.

这篇关于Mysql 'LOAD DATA LOCAL INFILE' 根据文件指定列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！