使用LOAD DATA时如何在MySQL中加载日期数据

本文介绍了使用LOAD DATA时如何在MySQL中加载日期数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 日期列的默认日期格式是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） 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 | + ------ + ------ + 是 输入日期栏（应为 07-Jun-12 或 07-Jun-12 ）或 使用格式字符串（％d-％b-％y ）或 其他什么？ 解决方案您的 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 NULLHere is the test I did using STR_TO_DATE option and it doesn't work.Test infile (test_temp.csv)c1, c207-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 datainfile '/var/lib/mysql/DataSet-1/temp_test.csv'ignoreinto table temp_testfields 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');OutputQuery OK, 2 rows affected, 2 warnings (0.00 sec)Records: 2 Deleted: 0 Skipped: 0 Warnings: 0MySQL> 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 withInput date column (Should it be 07-JUN-12 or 07-Jun-12) or With my format string (%d-%b-%y) orSomething 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:%sto%d-%b-%y %H:%i:%s ^^Your statement might look like thisLOAD 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中加载日期数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！