MySQL 导入文件中超过 60 秒或分钟 [英] More than 60 seconds or minutes in MySQL import file

问题描述

我目前正在将一个 CSV 文件导入到 MySQL 表中，其中包含如下几行:

I'm currently importing a CSV file to a MySQL table, with lines like:

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.但是，我无法控制信息源，因此我只是按原样接收此文件.到目前为止，列是:

It's basically a CDR from a PBX. However, I don't have control of info source, so I just receive this file as is. So far, columns are:

DATE,DURATION,MINUTES,PHONE,BALANCE

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

I've used LOAD DATA INFILE to import my data, like this:

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 只是一个用于存储我的源文件名的字段

CARDID is just a field to store my source filename

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

The problem is: DURATION is giving me problems. See: Some lines are like this:

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

也就是说，持续时间是 140 分 28 秒；但是，如果我存储该值，则会出现错误.这是因为:

That means, the duration is 140 minutes, 28 seconds; however, if I store that value, it gives me an error. This because:

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

因为 %i 期望分钟介于 00 和 59 之间.

Because %i expects minutes between 00 and 59.

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

I've tried to use SEC_TO_TIME, but also fails (it just takes first number):

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

我如何修改我的查询(请仅使用 SQL)以正确格式存储该时间?

How could I modify my query (please, just SQL) to store that time in correct format?

推荐答案

我已经通过字符串操作解决了这个问题.这不是最优雅的解决方案，但是嘿！它有效！

I've managed to fix this with a string manipulation. It's not the most elegant solution, but hey! it works!

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 = SEC_TO_TIME(SUBSTRING_INDEX(@col2,':',1)*60+SUBSTRING_INDEX(@col2,':',-1))
CARDID = 10811;

这篇关于MySQL 导入文件中超过 60 秒或分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！