mysql load data 日期_使用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)

c1, c2

07-JUN-12 22:50:19, "abc"

07-JUN-13 22:50:19, "bcd"

测试表(temp_test)

describe temp_test;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| c1 | datetime | YES | | NULL | |

| c2 | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

数据加载命令：

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');

产量

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 |

+------+------+

是问题所在

>输入日期栏(应该是07-JUN-12或07-Jun-12)或

>使用我的格式字符串(％d-％b-％y)或

>还有别的吗？