mysql - 字符串到时间戳 mysql 错误 #1411

Question

在将 csv 文件从字符串数据类型导入 mysql 时，我正在尝试即时转换时间戳。但我收到了 #1411 - Incorrect datetime value: '2007-03-30 16:01:15' for function str_to_date 错误。

SQL:

load data infile 'C:/ProgramData/MySQL/MySQL Server 5.5/data/testfile.csv'
into table test
fields terminated by ','
lines terminated by '\n'
(date, col1,col2,col3,col4)
SET
date = str_to_date(date,'%Y.%m.%d %H:%i:%s.%f');

.csv 中所有行的格式如下:

2007.03.30 16:01:15.901,117.53,117.55,35600000,43700000

I've applied

SELECT str_to_date(date,'%Y.%m.%d %H:%i:%s.%f') FROM test

对已经存储在 mysql 中的数据进行采样，它确实有效。

目标行 date 设置为 DATETIME。

Answer 1

您需要通过一个用户变量。作为the manual说:

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'  INTO TABLE t1  (column1, @var1)  SET column2 = @var1/100;

在你的情况下:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.5/data/testfile.csv'
  INTO TABLE test
  FIELDS TERMINATED BY ','
  LINES  TERMINATED BY '\n'
  (@date, col1, col2, col3, col4)
  SET date = STR_TO_DATE(@date, '%Y.%m.%d %H:%i:%s.%f');