使用LOAD DATA时如何在MySQL中加载日期数据 [英] How to load date data in MySQL when using LOAD DATA

问题描述

日期列的默认日期格式是MySQL中的 YYYY-MM-DD HH：MM：SS 。

我尝试加载的数据文件的日期字段的日期位于 DD-MON-YY HH：MM：SS 格式。当我使用 LOAD DATA 命令加载此文件时，数据库变得困惑，只是将所有日期输入到 0000-00-00 00:00:00 或NULL

以下是使用 STR_TO_DATE 选项进行的测试，

测试infile（test_temp.csv）

 codec1，c2 
 07-JUN-12 22:50:19abc
 07-JUN-13 22:50:19bcd
  

测试表（temp_test）

< p $ p> describe temp_test;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field |类型|空|关键|默认|额外|
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| c1 | datetime |是| | NULL | |
| c2 | varchar（10）|是| | NULL | |
+ ------- + ------------- + ------ + ----- + --------- + ------- +


数据加载命令： p>

 加载数据
 infile'/var/lib/mysql/DataSet-1/temp_test.csv'
 ignore 
转入表temp_test 
由'''
终止的'，'
由'\\\\'终止
忽略1行
（@ var_c1，c2）
 set c1 = STR_TO_DATE（@ var_c1，'％d-％b-％y％h：％i：％s'）; 
  

输出

 查询OK，2行受影响，2个警告（0.00秒）
记录：2已删除：0跳过：0警告：0 
 
 MySQL> show warnings; 
 + ----- + ------ + ------------------------------------- ------------------------------------ + 
 |级别|代码|留言| 
 + ------- + ------ + -------------------------------- ----------------------------------------- + 
 |错误| 1411 |在正确的日期时间值：'07 -JUN-12 22:50:19'用于函数str_to_date | 
 |错误| 1411 |对于函数str_to_date |，日期时间值不正确：'07 -JUN-13 22:50:19' 
 + ------- + ------ + ------------------------------ ------------------------------------------- + 
 
 MySQL> select * from temp_test; 
 + ------ + ------ + 
 | c1 | c2 | 
 + ------ + ------ + 
 | NULL | abc | 
 | NULL | bcd | 
 + ------ + ------ + 
  

是

1. 输入日期栏（应为 07-Jun-12 或 07-Jun-12 ）或


2. 使用格式字符串（％d-％b-％y ）或


3. 其他什么？

解决方案

您的 STR_TO_DATE（）的格式字符串无效。样本数据中的时间为24小时格式（％H 或％k ），而不是12小时（％h ）。您可以看到所有可能的日期格式说明符这里。

更改

 ％d-％b-％y％h ：％i：％s 
  

至

 ％d-％b-％y％H：％i：％s 
 ^^ 
  

您的陈述可能如下

  LOAD DATA INFILE'/ path / to / temp_test.csv'
 IGNORE INTO TABLE temp_test 
 FIELDS TERMINATED BY'，'OPTIONALLY ENCLOSED BY'''
 LINES TERMINATED BY'\r\\\
' - 或'\\\
 '
 IGNORE 1 LINES 
（@ c1，c2）
 SET c1 = STR_TO_DATE（@ c1，'％d-％b-％y％H：％i：％s'）; 
  

加载样本数据后

 
 mysql> select * from temp_test; 
 + --------------------- + ------ + 
 | c1 | c2 | 
 + --------------------- + ------ + 
 | 2012-06- 07 22:50:19 | abc | 
 | 2013-06-07 22:50:19 | bcd | 
 + ------------------ --- + ------ + 
 2行（0.00秒）
 

The default date format of a date column is YYYY-MM-DD HH:MM:SS in MySQL.

The data file that I am trying load from has a date field that has the date in DD-MON-YY HH:MM:SS format. When I load this file using LOAD DATA command, the database gets confused and just makes all date entries to 0000-00-00 00:00:00 or NULL

Here is the test I did using STR_TO_DATE option and it doesn't work.

Test infile (test_temp.csv)

c1, c2
07-JUN-12 22:50:19, "abc"
07-JUN-13 22:50:19, "bcd"

Test table (temp_test)

describe temp_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | datetime    | YES  |     | NULL    |       |
| c2    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Data Load command:

load data
infile '/var/lib/mysql/DataSet-1/temp_test.csv'
ignore
into table temp_test
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(@var_c1,c2)
set c1 = STR_TO_DATE(@var_c1,'%d-%b-%y %h:%i:%s');

Output

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MySQL> show warnings;
+-------+------+-------------------------------------------------------------------------+
| Level | Code | Message                                                                 |
+-------+------+-------------------------------------------------------------------------+
| Error | 1411 | Incorrect datetime value: '07-JUN-12 22:50:19' for function str_to_date |
| Error | 1411 | Incorrect datetime value: '07-JUN-13 22:50:19' for function str_to_date |
+-------+------+-------------------------------------------------------------------------+

MySQL> select * from temp_test;
+------+------+
| c1   | c2   |
+------+------+
| NULL | abc  |
| NULL | bcd  |
+------+------+

Is the problem with

1. Input date column (Should it be 07-JUN-12 or 07-Jun-12) or
2. With my format string (%d-%b-%y) or
3. Something else?

解决方案

Your format string for STR_TO_DATE() is invalid. Hours in your sample data have 24-hour format (%H or %k) instead of 12-hour (%h). You can see all possible date format specifiers here.

Change

%d-%b-%y %h:%i:%s

to

%d-%b-%y %H:%i:%s
         ^^

Your statement might look like this

LOAD DATA INFILE '/path/to/temp_test.csv'
IGNORE INTO TABLE temp_test
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n' -- or '\n'
  IGNORE 1 LINES
(@c1, c2)
SET c1 = STR_TO_DATE(@c1,'%d-%b-%y %H:%i:%s');

After loading with your sample data

mysql> select * from temp_test;
+---------------------+------+
| c1                  | c2   |
+---------------------+------+
| 2012-06-07 22:50:19 | abc  |
| 2013-06-07 22:50:19 | bcd  |
+---------------------+------+
2 rows in set (0.00 sec)

这篇关于使用LOAD DATA时如何在MySQL中加载日期数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！