如何在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)是 | |空 | |
 ------- ------------- ------ ----- --------- ------- 載入數據
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>從 temp_test 選擇*；
 ------ ------
| 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)