MySQL比较不同格式的时间

Question

我正在使用一个充满歌曲的数据库，其中包含标题和持续时间。

我需要返回持续时间大于 29:59 (MM:SS) 的所有歌曲。

数据以两种不同的方式格式化。

格式 1

表中的大部分数据格式为 MM:SS，部分歌曲长度超过 60 分钟，格式为 72:15。

格式 2

表中的其他歌曲格式为 HH:MM:SS，其中格式 1 给出的示例为 01:12:15。

---

我尝试了两种不同类型的查询来解决这个问题。

查询 1

以下查询返回我寻求为格式 1 返回的所有值，但我找不到获取格式 2 包含的值的方法。

select title, duration from songs where 
  time(cast(duration as time)) > 
  time(cast('29:59' as time)) 

查询 2

在下一个查询中，我希望使用 str_to_date 中的格式说明符来查找格式为 HH:MM:SS 的结果，但我却收到了诸如 3:50 之类的结果。解释器假设所有数据都是 HH:MM 形式，我不知道如何在不破坏结果的情况下告诉它。

select title, duration from songs where
      time(cast(str_to_date(duration, '%H:%i:%s') as time)) >
      time(cast(str_to_date('00:29:59', '%H:%i:%s') as time))

我尝试将第一次调用 str_to_date 中的说明符更改为 %i:%s，这给了我所有大于 29:59 的值，但没有一个大于 59:59。这比原始查询更糟糕。我还尝试过 00:%i:%s 和 '00:' ||持续时间，'%H:%i:%s'。无论如何，这两个尤其会破坏结果，但我现在只是在摆弄。

我完全被难住了，但我确信解决方案很简单。如有任何帮助，我们将不胜感激。

编辑:这是下面评论中请求的一些数据。

show create table 的结果:

CREATE TABLE `songs` (
 `song_id` int(11) NOT NULL,
 `title` varchar(100) NOT NULL,
 `duration` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`song_id`),
 UNIQUE KEY `songs_uq` (`title`,`duration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

请记住，列数比我上面描述的要多，但为了简单起见，我省略了一些列。我还将它们保留在示例数据中。

示例数据

title                       duration
(Allegro Moderato)              3:50
Agatha                          1:56
Antecessor Machine             06:16
Very Long Song              01:24:16
Also Very Long               2:35:22

Answer 1

您正在关系数据库中存储非结构化数据。这让你不开心。因此构建它。

添加 TIME 列，或将 Song_id 复制到您可以 JOIN 一侧的并行时间表中。选择所有两个冒号的持续时间并简单地更新 TIME。重复，在所有单冒号持续时间前面添加“00:”。现在您已经解析了所有行，并且可以安全地忽略持续时间列。

好吧，好吧，我想您可以构建一个提供这两个查询的 UNION ALL 的 VIEW，但这既慢又难看，最好修复磁盘上的数据。