php 为什么 MySQL 不支持毫秒/微秒精度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2572209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why doesn't MySQL support millisecond / microsecond precision?
提问by Byron Whitlock
So I just found the most frustrating bug ever in MySQL.
所以我刚刚发现了 MySQL 中最令人沮丧的错误。
Apparently the TIMESTAMPfield, and supporting functionsdo not support any greater precision than seconds!?
显然TIMESTAMP字段和支持函数不支持比秒更高的精度！？
So I am using PHP and Doctrine, and I really need those microseconds (I am using the actAs: [Timestampable]property).
所以我正在使用 PHP 和 Doctrine，我真的需要那些微秒（我正在使用该actAs: [Timestampable]属性）。
I found a that I can use a BIGINTfield to store the values. But will doctrine add the milliseconds? I think it just assigns NOW() to the field. I am also worried the date manipulation functions (in SQL) sprinkled through the code will break.
我发现我可以使用一个BIGINT字段来存储值。但是学说会增加毫秒吗？我认为它只是将 NOW() 分配给该字段。我还担心通过代码散布的日期操作函数（在 SQL 中）会中断。
I also saw something about compiling a UDF extension. This is not an acceptable because I or a future maintainer will upgrade and poof, change gone.
我还看到了一些关于编译 UDF 扩展的内容。这是不可接受的，因为我或未来的维护者将升级和噗，更改消失了。
Has anyone found a suitable workaround?
有没有人找到合适的解决方法？
采纳答案by Byron Whitlock
I found a workaround! It is very clean and doesn't require any application code changes. This works for Doctrine, and can be applied to other ORM's as well.
我找到了解决方法！它非常干净，不需要任何应用程序代码更改。这适用于 Doctrine，也适用于其他 ORM。
Basically, store the timestamp as a string.
基本上，将时间戳存储为字符串。
Comparisons and sorting works if the date string is formatted correctly. MySQL time functions will truncate the microsecond portion when passed a date string. This is okay if microsecond precision isn't needed for date_diffetc.
如果日期字符串格式正确，则比较和排序有效。当传递日期字符串时，MySQL 时间函数将截断微秒部分。如果不需要微秒精度date_diff等，这是可以的。
SELECT DATEDIFF('2010-04-04 17:24:42.000000','2010-04-04 17:24:42.999999');
> 0
SELECT microsecond('2010-04-04 17:24:42.021343');
> 21343
I ended up writing a MicroTimestampableclass that will implement this. I just annotate my fields as actAs:MicroTimestampableand voila, microtime precision with MySQL and Doctrine.
我最终编写了一个MicroTimestampable将实现这一点的类。我只是actAs:MicroTimestampable用 MySQL 和 Doctrine注释我的字段，瞧，微时间精度。
Doctrine_Template_MicroTimestampable
Doctrine_Template_MicroTimestampable
class Doctrine_Template_MicroTimestampable extends Doctrine_Template_Timestampable
{
/**
* Array of Timestampable options
*
* @var string
*/
protected $_options = array('created' => array('name' => 'created_at',
'alias' => null,
'type' => 'string(30)',
'format' => 'Y-m-d H:i:s',
'disabled' => false,
'expression' => false,
'options' => array('notnull' => true)),
'updated' => array('name' => 'updated_at',
'alias' => null,
'type' => 'string(30)',
'format' => 'Y-m-d H:i:s',
'disabled' => false,
'expression' => false,
'onInsert' => true,
'options' => array('notnull' => true)));
/**
* Set table definition for Timestampable behavior
*
* @return void
*/
public function setTableDefinition()
{
if ( ! $this->_options['created']['disabled']) {
$name = $this->_options['created']['name'];
if ($this->_options['created']['alias']) {
$name .= ' as ' . $this->_options['created']['alias'];
}
$this->hasColumn($name, $this->_options['created']['type'], null, $this->_options['created']['options']);
}
if ( ! $this->_options['updated']['disabled']) {
$name = $this->_options['updated']['name'];
if ($this->_options['updated']['alias']) {
$name .= ' as ' . $this->_options['updated']['alias'];
}
$this->hasColumn($name, $this->_options['updated']['type'], null, $this->_options['updated']['options']);
}
$this->addListener(new Doctrine_Template_Listener_MicroTimestampable($this->_options));
}
}
Doctrine_Template_Listener_MicroTimestampable
Doctrine_Template_Listener_MicroTimestampable
class Doctrine_Template_Listener_MicroTimestampable extends Doctrine_Template_Listener_Timestampable
{
protected $_options = array();
/**
* __construct
*
* @param string $options
* @return void
*/
public function __construct(array $options)
{
$this->_options = $options;
}
/**
* Gets the timestamp in the correct format based on the way the behavior is configured
*
* @param string $type
* @return void
*/
public function getTimestamp($type, $conn = null)
{
$options = $this->_options[$type];
if ($options['expression'] !== false && is_string($options['expression'])) {
return new Doctrine_Expression($options['expression'], $conn);
} else {
if ($options['type'] == 'date') {
return date($options['format'], time().".".microtime());
} else if ($options['type'] == 'timestamp') {
return date($options['format'], time().".".microtime());
} else {
return time().".".microtime();
}
}
}
}
回答by Xavier Portebois
For information for the next readers, this bug has finally be corrected in version 5.6.4:
对于下一个读者的信息，这个错误终于在5.6.4版中得到纠正：
"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."
“MySQL 现在支持 TIME、DATETIME 和 TIMESTAMP 值的小数秒，精度高达微秒。”
回答by Michael Konietzka
From the SQL92-Standard:
从 SQL92 标准：
- TIMESTAMP - contains the datetime field's YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
- TIMESTAMP - 包含日期时间字段的 YEAR、MONTH、DAY、HOUR、MINUTE 和 SECOND。
A SQL92 compliant database does not need to support milli- or microseconds from my point of view. Therefore the Bug #8523 is correctly marked as "feature request".
从我的角度来看，符合 SQL92 的数据库不需要支持毫秒或微秒。因此，错误 #8523 被正确标记为“功能请求”。
How does Doctrine will handle microseconds et al? I just found the following: Doctrine#Timestamp:
Doctrine 将如何处理微秒等？我刚刚发现以下内容： Doctrine#Timestamp：
The timestamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS.
时间戳数据类型仅仅是日期和时间数据类型的组合。时间戳类型的值的表示是通过将日期和时间字符串值连接在由空格连接的单个字符串中来完成的。因此，格式模板为 YYYY-MM-DD HH:MI:SS。
So there are no microseconds mentioned either as in the SQL92-docs. But I am not to deep into doctrine, but it seems to be an ORM like hibernate in java for example. Therefore it could/should be possible to define your own models, where you can store the timeinformation in a BIGINT or STRING and your model is responsible to read/write it accordingly into your PHP-classes.
所以在 SQL92-docs 中也没有提到微秒。但我并没有深入研究学说，但它似乎是一个像 java 中的休眠一样的 ORM。因此，可以/应该可以定义您自己的模型，您可以在其中将时间信息存储在 BIGINT 或 STRING 中，并且您的模型负责将其相应地读/写到您的 PHP 类中。
BTW: I don't expect MySQL to support TIMESTAMP with milli/microseconds in the near future eg the next 5 years.
顺便说一句：我不希望 MySQL 在不久的将来（例如未来 5 年）支持毫秒/微秒的 TIMESTAMP。
回答by Guss
As you're using Doctrine to store data, and Doctrine does not support fractional seconds either, then the bottleneck is not MySQL.
由于您使用 Doctrine 存储数据，并且 Doctrine 也不支持小数秒，因此瓶颈不是 MySQL。
I suggest you define additional fields in your objects where you need the extra precision, and store in them the output of microtime(). You probably want to store it in two different fields - one for the epoch seconds timestamp and the other for the microseconds part. That way you can store standard 32bit integers and easily sort and filter on them using SQL.
我建议您在需要额外精度的对象中定义其他字段，并将microtime(). 您可能希望将其存储在两个不同的字段中 - 一个用于纪元秒时间戳，另一个用于微秒部分。这样您就可以存储标准的 32 位整数，并使用 SQL 轻松地对它们进行排序和过滤。
I often recommend storing epoch seconds instead of native timestamp types as they are usually easier to manipulate and avoid the whole time zone issue you keep getting into with native time types and providing service internationally.
我经常建议存储纪元秒而不是本机时间戳类型，因为它们通常更容易操作，并且可以避免您经常使用本机时间类型并在国际上提供服务的整个时区问题。
回答by Bijumon K N
Another workaround for Time in milliseconds. Created function "time_in_msec"
以毫秒为单位的另一种解决方法。创建函数“time_in_msec”
USAGE :
用法 ：
Difference between two dates in milliseconds.
以毫秒为单位的两个日期之间的差异。
mysql> SELECT time_in_msec('2010-07-12 23:14:36.233','2010-07-11 23:04:00.000') AS miliseconds;
+-------------+
| miliseconds |
+-------------+
| 87036233 |
+-------------+
1 row in set, 2 warnings (0.00 sec)
DELIMITER $$
DROP FUNCTION IF EXISTS `time_in_msec`$$
CREATE FUNCTION `time_in_msec`(ftime VARCHAR(23),stime VARCHAR(23)) RETURNS VARCHAR(30) CHARSET latin1
BEGIN
DECLARE msec INT DEFAULT 0;
DECLARE sftime,sstime VARCHAR(27);
SET ftime=CONCAT(ftime,'000');
SET stime=CONCAT(stime,'000');
SET msec=TIME_TO_SEC(TIMEDIFF(ftime,stime))*1000+TRUNCATE(MICROSECOND(TIMEDIFF(ftime,stime))/1000,0);
RETURN msec;
END$$
DELIMITER ;
回答by Rahul More
From Mysql version 5.6.4 onward ,It stores microsecond in a column.
从 Mysql 5.6.4 版本开始，它将微秒存储在列中。
"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."
“MySQL 现在支持 TIME、DATETIME 和 TIMESTAMP 值的小数秒，精度高达微秒。”
For example:
例如：
CREATE TABLE `test_table` (
`name` VARCHAR(1000) ,
`orderdate` DATETIME(6)
);
INSERT INTO test_table VALUES('A','2010-12-10 14:12:09.019473');
SELECT * FROM test_table;
Only needs to change datatype to datetime to datetime(6);
只需要将数据类型更改为日期时间到日期时间(6)；
For more information refer following: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
有关更多信息，请参阅以下内容：http: //dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
回答by DehuaYang
Now you can use micro seconds
现在您可以使用微秒
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.26 |
+-----------+
1 row in set (0.00 sec)
mysql> select now(6);
+----------------------------+
| now(6) |
+----------------------------+
| 2016-01-16 21:18:35.496021 |
+----------------------------+
1 row in set (0.00 sec)
回答by Drew
As mentioned microsecond support was added in version 5.6.4
如前所述，在 5.6.4 版中添加了微秒支持
Perhaps the following is of use for fractional seconds:
也许以下内容对小数秒有用：
drop procedure if exists doSomething123;
delimiter $$
create procedure doSomething123()
begin
DECLARE dtBEGIN,dtEnd DATETIME(6);
DECLARE theCount,slp INT;
set dtBegin=now(6); -- see http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
-- now do something to profile
select count(*) into theCount from questions_java where closeDate is null;
select sleep(2) into slp;
-- not the above but "something"
set dtEnd=now(6); -- see http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
select timediff(dtEnd,dtBegin) as timeDiff,timediff(dtEnd,dtBegin)+MICROSECOND(timediff(dtEnd,dtBegin))/1000000 seconds;
-- select dtEnd,dtBegin;
end$$
delimiter ;
Test:
测试：
call doSomething123();
+-----------------+----------+
| timeDiff | seconds |
+-----------------+----------+
| 00:00:02.008378 | 2.016756 |
+-----------------+----------+
Another view of it:
对它的另一种看法：
set @dt1=cast('2016-01-01 01:00:00.1111' as datetime(6));
set @dt2=cast('2016-01-01 01:00:00.8888' as datetime(6));
select @dt1,@dt2,MICROSECOND(timediff(@dt2,@dt1))/1000000 micros;
+----------------------------+----------------------------+--------+
| @dt1 | @dt2 | micros |
+----------------------------+----------------------------+--------+
| 2016-01-01 01:00:00.111100 | 2016-01-01 01:00:00.888800 | 0.7777 |
+----------------------------+----------------------------+--------+
See the MySQL Manual Page entitled Fractional Seconds in Time Values
请参阅题为时间值中的小数秒的 MySQL 手册页
