Почему MySQL не поддерживает точность в миллисекундах / микросекундах?
Так что я нашел самую неприятную ошибку в MySQL.
Видимо TIMESTAMP полевые и вспомогательные функции не поддерживают большую точность, чем секунды!?
Поэтому я использую PHP и Doctrine, и мне действительно нужны эти микросекунды (я использую actAs: [Timestampable] имущество).
Я нашел, что я могу использовать BIGINT поле для хранения значений. Но учение добавит миллисекунды? Я думаю, что это просто назначает NOW() на поле. Я также обеспокоен тем, что функции манипулирования датами (в SQL), разбросанные по коду, сломаются.
Я также видел кое-что о компиляции расширения UDF. Это неприемлемо, потому что я или будущий сопровождающий обновлюсь, а изменения исчезнут.
Кто-нибудь нашел подходящий обходной путь?
8 ответов
Я нашел обходной путь! Это очень чистый и не требует каких-либо изменений кода приложения. Это работает для Доктрины, и может быть применено к другим ORM.
В основном, храните метку времени в виде строки.
Сравнения и сортировка работают, если строка даты отформатирована правильно. Функции времени 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
Я закончил тем, что написал MicroTimestampable класс, который будет реализовывать это. Я просто аннотирую свои поля как actAs:MicroTimestampable и вуаля, точность микровремени с MySQL и 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_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();
}
}
}
}
Для информации для следующих читателей, эта ошибка наконец исправлена в версии 5.6.4:
"MySQL теперь поддерживает доли секунды для значений TIME, DATETIME и TIMESTAMP с точностью до микросекунды".
Из стандарта SQL92:
- TIMESTAMP - содержит поле даты и времени YEAR, MONTH, DAY, HOUR, MINUTE и SECOND.
С моей точки зрения, совместимая с SQL92 база данных не должна поддерживать миллисекунды или микросекунды. Поэтому ошибка № 8523 правильно помечена как "запрос функции".
Как Doctrine справится с микросекундами и др.? Я только что нашел следующее: Doctrine # Timestamp:
Тип данных timestamp - это простая комбинация типов данных date и time of day. Представление значений типа отметки времени выполняется путем объединения значений строки даты и времени в одну строку, соединенную пробелом. Следовательно, шаблон формата: ГГГГ-ММ-ДД ЧЧ: МИ: СС.
Таким образом, микросекунды не упоминаются, как в документах SQL92. Но я не слишком углубляюсь в доктрину, но, похоже, это ORM, похожий на Hibernate в Java, например. Следовательно, можно / должно быть возможно определить ваши собственные модели, где вы можете хранить информацию о времени в BIGINT или STRING, и ваша модель отвечает за ее чтение / запись соответственно в ваши PHP-классы.
Кстати: я не ожидаю, что MySQL будет поддерживать TIMESTAMP с миллисекундами в ближайшем будущем, например, в ближайшие 5 лет.
Начиная с версии 5.6.4 Mysql, он сохраняет микросекунды в столбце.
"MySQL теперь поддерживает доли секунды для значений TIME, DATETIME и TIMESTAMP с точностью до микросекунды".
Например:
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;
Нужно только изменить тип данных на datetime на datetime(6);
Для получения дополнительной информации см. Следующее: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
Поскольку вы используете Doctrine для хранения данных, а Doctrine также не поддерживает доли секунды, узким местом является не MySQL.
Я предлагаю вам определить дополнительные поля в ваших объектах, где вам нужна дополнительная точность, и сохранить в них выходные данные microtime(), Возможно, вы хотите сохранить его в двух разных полях - одно для метки времени эпохи, а другое для части микросекунды. Таким образом, вы можете хранить стандартные 32-битные целые числа и легко сортировать и фильтровать их, используя SQL.
Я часто рекомендую хранить эпохальные секунды вместо собственных типов временных отметок, поскольку ими обычно легче манипулировать и избегать проблем с часовым поясом, с которыми вы постоянно сталкиваетесь, используя собственные типы времени и предоставляя услуги на международном уровне.
Еще один обходной путь для времени в миллисекундах. Создана функция "time_in_msec"
ИСПОЛЬЗОВАНИЕ:
Разница между двумя датами в миллисекундах.
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 ;
Теперь вы можете использовать микросекунды
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)
Как уже упоминалось, микросекундная поддержка была добавлена в версии 5.6.4.
Возможно, следующее будет полезно в течение доли секунды:
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 ;
Тестовое задание:
call doSomething123();
+-----------------+----------+
| timeDiff | seconds |
+-----------------+----------+
| 00:00:02.008378 | 2.016756 |
+-----------------+----------+
Другой взгляд на это:
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 |
+----------------------------+----------------------------+--------+
См. Страницу руководства MySQL, озаглавленную Дробные секунды в значениях времени