如何在MySQL中将持续时间值存储为TIME数据类型?

本文介绍了如何在MySQL中将持续时间值存储为TIME数据类型?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要以分钟/秒为单位存储歌曲持续时间，我需要使用TIME，但是在编写INSERT语句时如何指代某个持续时间?我在表中的数据类型已经是TIME，我应该STR_TO_DATE字符串值"4:29"吗?

I need to store song durations in minutes/seconds, I need to use TIME, but how would I refer to a certain duration when I am writing an INSERT statement? My datatype in the table is already TIME, should I just STR_TO_DATE the string value of "4:29"?

推荐答案

首先，在这里看看: dev.mysql/doc/refman/5.0/en/time.html

在为TIME列分配缩写值时要小心.的MySQL 用冒号将缩写的TIME值解释为一天中的时间. 也就是说，"11:12"表示"11:12:00"，而不是"00:11:12". MySQL解释 缩写值，不带冒号，使用两个假设 最右边的数字代表秒(即经过的时间，而不是 而不是一天中的时间).例如，您可能会想到"1112"和"1112" 意思是"11:12:00"(11点后12分钟)，但是MySQL 将其解释为"00:11:12"(11分12秒).相似地， '12'和12被解释为'00:00:12'.

Be careful about assigning abbreviated values to a TIME column. MySQL interprets abbreviated TIME values with colons as time of the day. That is, '11:12' means '11:12:00', not '00:11:12'. MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12' and 12 are interpreted as '00:00:12'.

因此，如果您想插入持续时间= 05:55的歌曲，只需编写以下内容:

So, if you wanna insert a song with duration = 05:55, just write this:

insert into songs (duration) values('0555');