mysql - MySQL 导入文件超过 60 秒或分钟

Question

我目前正在将 CSV 文件导入 MySQL 表，其中包含以下行:

2/28/2015 8:46:30 PM,1:40,2,1234567,1435.6071
2/28/2015 8:45:58 PM,0:19,1,1234568,1435.6436

它基本上是来自 PBX 的 CDR。但是，我无法控制信息源，所以我只按原样接收此文件。到目前为止，列是:

DATE,DURATION,MINUTES,PHONE,BALANCE

我使用 LOAD DATA INFILE 来导入我的数据，如下所示:

LOAD DATA INFILE '10811.csv'
INTO TABLE cdr
fields terminated by ','
lines terminated by '\n'
(@col1, @col2, MINUTES, PHONE, BALANCE)
set DATE = STR_TO_DATE(@col1,'%m/%d/%Y %h:%i:%s %p'),
DURATION = STR_TO_DATE(@col2,'%i:%s'),
CARDID = 10811;

CARDID 只是一个存储我的源文件名的字段

问题是:DURATION 给我带来了问题。看:有些行是这样的:

2/28/2015 8:46:30 PM,140:28,141,1234569,1435.6071

也就是说，时长是140分28秒；但是，如果我存储该值，它会给我一个错误。这是因为:

> SELECT str_to_date('140:28','%i:%s') AS DATE;
+------+
| DATE |
+------+
| NULL |
+------+

因为 %i 期望分钟数在 00 到 59 之间。

我试过使用 SEC_TO_TIME，但也失败了(它只需要第一个数字):

> SELECT sec_to_time('140:28') AS DATE;
+----------+
| DATE     |
+----------+
| 00:02:20 |
+----------+

我如何修改我的查询(拜托，只是 SQL)以正确的格式存储那个时间？

Answer 1

与接受大于 0-23 范围的值的小时格式说明符“%H”和“%k”不同，没有接受大于 0-59 范围的值的分钟格式说明符。

您可以使用MYSQL提供的字符串函数将DURATION字段转换成合适的格式(如140:28 -> 2:20:28)。

但更好的做法是使用其他工具/语言/脚本进行字符串转换，这比 MYSQL 的字符串函数在字符串操作方面更高效、更容易。