mysql - 使用 str_to_date 函数从文件中将数据加载到 MySQL 中的日期时间值不正确
问题描述
这让我发疯,尝试了 2 天,查看这里和 mysql 文档但没有成功。
我有一个包含这些数据的文件:
Test,String 1,Completed,full,04/20/21 12:10:01,1618913401
Test,String 2,Completed,full,04/20/21 12:15:01,1618913701
Test,String 3,Completed,full,04/20/21 12:30:02,1618914602
Test,String 4,Completed,full,04/20/21 13:30:01,1618918201
Test,String 5,Completed,full,04/20/21 14:00:01,1618920001
并尝试使用以下命令将其插入 mysql:
LOAD DATA LOCAL INFILE 'output2.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(`Session Type`, `Specification`, `Status`, `Mode`, @StartTime, `Start Time_t`)
SET `Start Time` = STR_TO_DATE(@StartTime, '%d/%m/%y %T');
Result:
Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 10
MariaDB [data]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '04/20/21 12:10:01' for function str_to_date |
| Warning | 1048 | Column 'Start Time' cannot be null |
| Warning | 1411 | Incorrect datetime value: '04/20/21 12:15:01' for function str_to_date |
| Warning | 1048 | Column 'Start Time' cannot be null |
| Warning | 1411 | Incorrect datetime value: '04/20/21 12:30:02' for function str_to_date |
| Warning | 1048 | Column 'Start Time' cannot be null |
| Warning | 1411 | Incorrect datetime value: '04/20/21 13:30:01' for function str_to_date |
| Warning | 1048 | Column 'Start Time' cannot be null |
| Warning | 1411 | Incorrect datetime value: '04/20/21 14:00:01' for function str_to_date |
| Warning | 1048 | Column 'Start Time' cannot be null |
+---------+------+------------------------------------------------------------------------+
Start Time
是日期时间类型。
解决方案
如果您仔细观察,日期是令人困惑的美国格式,月份不能是 20 :) 所以只需将格式修复如下
STR_TO_DATE('04/20/21 14:00:01', '%m/%d/%y %T');
推荐阅读
- r - tibble 的交叉表变量并使输出可读
- android - 按钮错误地用drawable内部包装内容
- google-analytics - 我可以复制 GA4 属性吗?
- java - 以编程方式添加新的约束映射 HV000121:无法解析约束映射文件
- java - 在 Flutter 中实现 Firebase 原生广告
- heroku - Heroku PS:Exec with ENV Vars
- javascript - 使用 for 循环和间隔进行 JavaScript 优化
- java - antlr 词法分析器和解析器的转义字符
- python - 为什么张量板图奇怪地弯曲?
- django - Django 在表单字段中使用 setCustomValidity 方法清理