如何在MySQL中使用LOAD DATA加载日期数据
P粉738046172
P粉738046172 2023-08-22 22:12:49
[MySQL讨论组]
<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&gt; 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&gt; 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>
P粉738046172
P粉738046172

全部回复(1)
P粉852578075

您的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)
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号