如何在MySQL中插入微秒精度的datetime? [英] How to insert a microsecond-precision datetime into mysql？

问题描述

我有一个像这样的字符串:

I have a string like this:

2011-11-11 11:11:11.111111

，我需要将它在MySql中插入datetime列.但是插入后，它变成了

and I need to insert it in MySql, into a datetime column. But after I insert it, it becomes

2011-11-11 11:11:11

怎么了?

推荐答案

MySql 5.6+支持时间值中的小数秒，而以前的版本则没有.

MySql 5.6+ supports fractional seconds in Time Values, while previous versions don't.

标准datetime列将不包含微秒值，而datetime(6)则将包含微秒值.您可以在MySql 5.6中对其进行测试:

A standard datetime column will not hold microsecond values, while a datetime(6) will. You can test it in MySql 5.6:

CREATE TABLE your_table (
  d1 datetime,
  d2 datetime(6)
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', '2011-11-11 11:11:11.111111');

SELECT MICROSECOND(d1) as m1, MICROSECOND(d2) as m2
FROM your_table;

m1 | m2
-----------
0  | 111111

如果您不使用MySql 5.6+，我建议您使用两列，一列用于datetime部分，一列用于微秒:

If you are not using MySql 5.6+ I would suggest you to use two columns, one for the datetime part, and one for the microseconds:

CREATE TABLE your_table (
  dt datetime,
  us int
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', MICROSECOND('2011-11-11 11:11:11.111111'));

这篇关于如何在MySQL中插入微秒精度的datetime?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！