mysql - 从列值中提取年和月值并插入MYSQL

我有一个包含四列的文件：

local_timestamp,    utc_timestamp,       pointname,      pointvalue
7/7/2017 12:29:00 PM,7/7/2017 6:59:00 AM,STS.PresentValue,1.0
7/7/2017 12:30:00 PM,7/7/2017 7:00:00 AM,STS.PresentValue,1.0

我创建了一个表，如下所示：

CREATE TABLE  sample(
  `local_timestamp` DATETIME  NOT NULL,
  `utc_timestamp` DATETIME  NOT NULL,
  `pointname` VARCHAR(50) NOT NULL,
  `pointvalue` FLOAT NOT NULL DEFAULT 0.0,
  `year_id` INT NOT NULL,
  `month_id` INT NOT NULL,
  UNIQUE KEY (year_id,month_id))
  PARTITION BY KEY() ;

现在，由于文件仅包含4个字段，而表包含6个字段，我该如何使用LOAD DATA INFILE将数据批量插入表中。

year_id和month_id字段应具有local_timestamp字段中的年和月值。

我正在尝试以下操作，但会引发错误。

LOAD DATA INFILE 'D:/data/*'
INTO TABLE sample
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(@var1, @var2, pointname, pointvalue)
SET local_timestamp = STR_TO_DATE(@var1, '%d/%m/%Y %h:%i:%s %p'), utc_timestamp = STR_TO_DATE(@var2, '%d/%m/%Y %h:%i:%s %p'), year_id = YEAR(@var1),month_id = MONTH(@var1);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'utc_timestamp = STR_TO_DATE(@utc_timestamp, '%d/%m/%Y %h:%i:%s %p'), pointname =' at line 6

最佳答案

好吧，我有可行的解决方案。背tick使它起作用。

LOAD DATA LOCAL INFILE 'D:/data/file'
INTO TABLE sample
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(@var1, @var2, @pointname, @pointvalue)
SET `local_timestamp` = STR_TO_DATE(@var1, '%d/%m/%Y %h:%i:%s %p'), `utc_timestamp` = STR_TO_DATE(@var2, '%d/%m/%Y %h:%i:%s %p'), `pointname` = @pointname, `pointvalue` = @pointvalue, `year_id` = SUBSTRING(@var1,5,4) ,`month_id` = SUBSTRING_INDEX(@var1,'/',1);

关于mysql - 从列值中提取年和月值并插入MYSQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45461413/