如何在MySQL中使用LOAD DATA加载日期数据
<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>
<pre class="brush:php;toolbar:false;">c1, c2
07-JUN-12 22:50:19, "abc"
07-JUN-13 22:50:19, "bcd"</pre>
<p><strong>测试表(temp_test)</strong></p>
<pre class="brush:php;toolbar:false;">describe temp_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | datetime | YES | | NULL | |
| c2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+</pre>
<p><strong>数据加载命令:</strong></p>
<pre class="brush:php;toolbar:false;">load data
infile '/var/lib/mysql/DataSet-1/temp_test.csv'
ignore
into table temp_test
fields terminated by ','
enclosed by '"'
lines terminated by 'rn'
ignore 1 lines
(@var_c1,c2)
set c1 = STR_TO_DATE(@var_c1,'%d-%b-%y %h:%i:%s');</pre>
<p><strong>输出</strong></p>
<pre class="brush:php;toolbar:false;">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 |
+------+------+</pre>
<p>问题出在:</p>
<ol>
<li>输入日期列(应该是<code>07-JUN-12</code>还是<code>07-Jun-12</code>)或</li>
<li>我的格式字符串(<code>%d-%b-%y</code>)或</li>
<li>其他原因?</li>
</ol><p><br /></p>
您的
STR_TO_DATE()
的格式字符串无效。您的样本数据中的小时使用24小时制(%H
或%k
),而不是12小时制(%h
)。您可以在这里查看所有可能的日期格式说明符。将
修改为
您的语句可能如下所示
使用您的样本数据加载后