mysql - 加载数据到文件中所有跳过行

Question

我正在尝试使用 MySQL 上的“加载数据文件”功能，但我的所有行都被跳过。我已经尝试了其他线程的所有解决方案，但找不到解决我的问题的解决方案。 MySQL 继续跳过我的所有行，我做错了什么？

查询

LOAD DATA LOCAL INFILE 'C:\\Stockton\\DataFiles\\emaimport.csv' INTO TABLE moving_average
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@col1, @col2, @col3) set ma_symbol=@col1,ma_date=@col2,ma15=@col3;

表结构(仅插入ma15列)

CREATE TABLE `moving_average` (
  `ma_symbol` varchar(50) NOT NULL,
  `ma_date` date NOT NULL,
  `ma5` decimal(12,6) DEFAULT NULL,
  `ma10` decimal(12,6) DEFAULT NULL,
  `ma15` decimal(12,6) DEFAULT NULL,
  `ma20` decimal(12,6) DEFAULT NULL,
  `ma25` decimal(12,6) DEFAULT NULL,
  `ma30` decimal(12,6) DEFAULT NULL,
  `ma35` decimal(12,6) DEFAULT NULL,
  `ma40` decimal(12,6) DEFAULT NULL,
  `ma45` decimal(12,6) DEFAULT NULL,
  `ma50` decimal(12,6) DEFAULT NULL,
  `ma100` decimal(12,6) DEFAULT NULL,
  `ma200` decimal(12,6) DEFAULT NULL,
  PRIMARY KEY (`ma_symbol`,`ma_date`),
  CONSTRAINT `fk_symbol_moving_average` FOREIGN KEY (`ma_symbol`) REFERENCES `company` (`symbol`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

输入数据的示例

TR,2016-02-18,29.919117000
TR,2016-02-19,30.045367375
TR,2016-02-22,29.994117375
TR,2016-02-23,30.015367125
TRC,2011-02-15,26.705000000
TRC,2011-02-16,26.777500125
TRC,2011-02-17,26.757499750
TRC,2011-02-18,26.758750000

Answer 1

您的查询需要 STR_TO_DATE date 字段的函数。

像这样:

LOAD DATA LOCAL INFILE 'C:\\Stockton\\DataFiles\\emaimport.csv' INTO TABLE moving_average
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@col1, @col2, @col3) set ma_symbol=@col1,ma_date=STR_TO_DATE(@col2,'%Y-%m-%d'),ma15=@col3;