为什么MySQL不支持毫秒/微秒精度？ [英] Why doesn't MySQL support millisecond / microsecond precision?

问题描述

所以我刚刚发现了 MySQL中最令人沮丧的bug 。

显然， TIMESTAMP 字段和支持功能不支持任何更高的精度超过秒！

Apparently the TIMESTAMP field, and supporting functions do not support any greater precision than seconds!?

所以我使用PHP和Doctrine，我真的需要这些微秒（我正在使用 actAs：[Timestampable] 属性）。

So I am using PHP and Doctrine, and I really need those microseconds (I am using the actAs: [Timestampable] property).

我发现我可以使用 BIGINT 字段来存储值。但是，教义会添加毫秒吗？我认为它只是分配NOW（）到字段。我也很担心日期操作功能（在SQL中）通过代码散开会中断。

I found a that I can use a BIGINT field 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.

我还看到有关编译UDF扩展的内容。这不是可以接受的，因为我或者将来的维护者会升级和变化，改变了。

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.

有没有人找到合适的解决方法？

Has anyone found a suitable workaround?

推荐答案

我找到了一个解决方法！它非常干净，不需要任何应用程序代码更改。这适用于教义，也可以应用于其他ORM。

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.

基本上，将时间戳记存储为字符串。

Basically, store the timestamp as a string.

如果日期字符串的格式正确，则比较和排序将起作用。 MySQL时间函数会在传递日期字符串时截断微秒部分。如果 date_diff 等不需要微秒精度，则可以。

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_diff etc.

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 

我最后写了一个 MicroTimestampable 这将实现这一点。我只是将我的字段注释为 actAs：MicroTimestampable ，瞧，MySQL和Doctrine的微时精度。

I ended up writing a MicroTimestampable class that will implement this. I just annotate my fields as actAs:MicroTimestampable and voila, microtime precision with MySQL and Doctrine.

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_MicroTimestableable

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();
            }
        }
    }
}

这篇关于为什么MySQL不支持毫秒/微秒精度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！