使用LOAD DATA LOCAL INFILE时添加额外的数据列

问题描述

我正在使用以下LOAD DATA LOCAL INFILE查询来从文件中读取数据并将其插入表中.一切都很好.但是，我需要修改查询以支持一项附加要求.

I'm using the following LOAD DATA LOCAL INFILE query to read data from a file and insert it into a table. Everything works great as is. However I need to modify the query to support one additional requirement.

我需要在数据中添加一个额外的列.本质上，我的原始数据不包括日期字段，我需要在其中添加可以添加数据导入日期的列.该文件将每天自动导入一次，我需要将该日期添加到数据中.

I need to add an extra column to the data. Essentially my raw data does not include a date field and I need to include a column where I can add the date the data was imported. The file will be automatically imported once a day and I need to add that date to the data.

使用LOAD DATA LOCAL INFILE时是否可以添加一列数据?我可以轻松捕获今天的日期，但是我不知道如何将其添加到下面的查询中.

Is there a way to add a column of data when using LOAD DATA LOCAL INFILE? I can easily capture today's date, but I don't know how to add it to the query below.

$insert_query = LOAD DATA LOCAL INFILE 'C:/mydata.csv'
INSERT INTO TABLE myTable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Column1,Column2,Column3,Column4);

$db->setQuery($insert_query);
$res = $db->query();

任何建议将不胜感激！！！谢谢！

Any suggestions will be greatly appreciated!!! Thanks!

推荐答案

您可以在查询的末尾添加SET子句，例如

You can add a SET clause to the end of the query, e.g.

LOAD DATA LOCAL INFILE 'C:/mydata.csv'
INSERT INTO TABLE myTable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Column1,Column2,Column3,Column4)
SET datetime = NOW();

这篇关于使用LOAD DATA LOCAL INFILE时添加额外的数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！