MySql加载数据文件STR_TO_DATE返回空白？ [英] MySql load data infile STR_TO_DATE returning blank?

问题描述

我从csv文件导入1m +记录到我的表中。
使用加载数据局部infile方法非常棒。

但是，日期都是不同的格式。
Google快速引导我完成此功能：

STR_TO_DATE

，我什么都没有，一个空插入。这里是我的SQ下降包括一个日期（我有4相同的问题）和通用列名称：

 加载数据本地infile'myfile.csv'到表`mytable` 
由'\t'终止的字段
以'\\\
'终止的行
 IGNORE 1 LINES 
 
（`column name 1` 
，`my second column` 
，@temp_date 
，`final column`）
 
 SET`Get Date` = STR_TO_DATE @temp_date，'％c /％e /％Y'）
  

>

  SET`Get Date` = @temp_date 
  

以csv在文件中的格式捕获的日期。
但是当我尝试第一个方法，我的表列是空的。我已经改变列类型为varchar（255）从时间戳到captre不管发生什么，但最终，我想捕获ymd H：i：s（不知道如果STR_TO_DATE可以这样做？）

我也不确定为什么我需要@符号.. google失败了我。

所以，我的问题是：

1. 为什么需要@符号才能使用此功能？


2. '％c /％e /％Y'）是输入数据的格式还是我想要的输出？


3. 我可以用这种方式捕获时间吗？
$ b $




回到Google

$
b $ b

解决方案

为什么我需要@符号才能使用此功能？





@符号表示您正在使用一个变量，因此读取的字符串不会立即放入表中，而是放入一个内存块中，您可以在插入它之前对其进行操作。有关 http://dev.mysql.com/doc/的更多信息refman / 5.0 / en / user-variables.html





如果数据格式（'％c /％e /％Y'）输入数据的格式或所需输出？





输入数据的格式，更多信息在 http://dev.mysql.com/doc/refman/ 5.5 / en / date-and-time-functions.html＃function_str-to-date





我可以用这种方式捕获时间吗？





只要您选择了正确的格式，就应该可以了。

  STR_TO_DATE（@temp_date，'％c /％e /％Y％h：％i：％s'）; 
  




i'm importing 1m+ records into my table from a csv file. Works great using the load data local infile method.

However, the dates are all different formats. A quick google lead me to this function:

STR_TO_DATE

However, when I implement that, I get nothing, an empty insert. here's my SQ cut down to include one date (I've 4 with the same issue) and generic column names:

load data local infile 'myfile.csv' into table `mytable`
fields terminated by '\t'
lines terminated by '\n'
IGNORE 1 LINES

( `column name 1`
, `my second column`
, @temp_date
, `final column`)

SET `Get Date` = STR_TO_DATE(@temp_date, '%c/%e/%Y')

If I do:

SET `Get Date` = @temp_date

The date from the csv is captured in the the format it was in the file. However when I try the first method, my table column is empty. I've changed the column type to varchar (255) from timestamp to captre whatever is going in, but ultimatly, I want to capture y-m-d H:i:s (Not sure if STR_TO_DATE can do that?)

I'm also unsure as to why I need the @ symbol.. google failed me there.

So, my questions are:

1. Why do I need the @ symbol to use this function?
2. Should the data format ('%c/%e/%Y') be the format of the inputted data or my desired output?
3. Can I capture time in this way too?

sorry for the large post!

Back to Google for now...

解决方案

Why do I need the @ symbol to use this function?

The @ symbol means that you are using a variable, so the read string isnt put right away into the table but into a memory pice that lets you operate with it before inserting it. More info in http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Should the data format ('%c/%e/%Y') be the format of the inputted data or my desired output?

Its the format of the inputted data, more info in http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Can I capture time in this way too?

You should be able to as long as you chose the correct format, something like

STR_TO_DATE(@temp_date,'%c/%e/%Y %h:%i:%s');

这篇关于MySql加载数据文件STR_TO_DATE返回空白？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！