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

Question

<p>一个日期列的默认日期格式在MySQL中是<code>YYYY-MM-DD HH:MM:SS</code>。</p> <p>我尝试加载的数据文件中有一个日期字段，日期格式是<code>DD-MON-YY HH:MM:SS</code>。当我使用<code>LOAD DATA</code>命令加载这个文件时，数据库会混淆，并将所有日期条目设置为<code>0000-00-00 00:00:00</code>或NULL。</p> <p>这是我使用<code>STR_TO_DATE</code>选项进行的测试，但它不起作用。</p> <p><strong>测试文件（test_temp.csv）</strong></p>

c1, c2
2012 年 6 月 7 日 22:50:19，“abc”
2013 年 6 月 7 日 22:50:19，“bcd”

<p><strong>测试表（temp_test）</strong></p>

描述 temp_test;
+--------+-------------+------+-----+---------+---- ---+
|领域 |类型 |空 |关键|默认 |额外 |
+--------+-------------+------+-----+---------+---- ---+
| c1 |日期时间 |是的 |     |空 |       |
| c2 | varchar(10) | varchar(10)是的 |     |空 |       |
+--------+-------------+------+-----+---------+---- ---+</pre>
<p><strong>数据加载命令：</strong></p>
加载数据
infile '/var/lib/mysql/DataSet-1/temp_test.csv'
忽略
进入表 temp_test
以“,”结尾的字段
用“”括起来
以“rn”结尾的行
忽略1行
（@var_c1，c2）
设置 c1 = STR_TO_DATE(@var_c1,'%d-%b-%y %h:%i:%s');</pre>
<p><strong>输出</strong></p>
查询正常，2行受影响，2个警告（0.00秒）
记录：2 删除：0 跳过：0 警告：0

MySQL>显示警告；
+--------+------+---------------------------------- ---------------------------------------+
|水平|代码|留言 |
+--------+------+---------------------------------- ---------------------------------------+
|错误 | 1411 | 1411函数 str_to_date 的日期时间值不正确：“07-JUN-12 22:50:19”
|错误 | 1411 | 1411函数 str_to_date 的日期时间值不正确：“07-JUN-13 22:50:19” |
+--------+------+---------------------------------- ---------------------------------------+

MySQL>从临时测试中选择*；
+------+------+
| c1 | c2 |
+------+------+
|空| ABC |
|空| BCD |
+------+------+</pre>
<p>问题出在：</p>
<ol>
<li>输入日期列（应该是<code>07-JUN-12</code>还是<code>07-Jun-12</code>）或</li>
<li>我的格式字符串（<code>%d-%b-%y</code>）或</li>
其他原因？
</ol><p><br />></p>

Answer 1

您的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\n' -- or '\n'
  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 rows in set (0.00 sec)