MySQL 如何在mysql中插入微秒精度的日期时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14038746/
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
How to insert a microsecond-precision datetime into mysql？
提问by piano7heart
I have a string like this:
我有一个这样的字符串：
2011-11-11 11:11:11.111111
and I need to insert it in MySql, into a datetime column. But after I insert it, it becomes
我需要将它插入到 MySql 中的日期时间列中。但是插入后就变成了
2011-11-11 11:11:11
What's going wrong?
怎么了？
回答by fthiella
MySql 5.6+ supports fractional secondsin Time Values, while previous versions don't.
MySql 5.6+ 支持时间值中的小数秒，而以前的版本不支持。
A standard datetimecolumn will not hold microsecond values, while a datetime(6)will. You can test it in MySql 5.6:
标准datetime列不会保存微秒值，而datetime(6)会。您可以在 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
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:
如果您不使用 MySql 5.6+，我建议您使用两列，一列用于日期时间部分，另一列用于微秒：
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'));
回答by IgK
use data type of datetime(6) or timestamp(6) in your table and insert current time with NOW(6) to get 6 digits of fractional seconds.
在表中使用 datetime( 6) 或 timestamp( 6)数据类型，并使用 NOW( 6)插入当前时间以获得 6 位小数秒。
回答by eggyal
As documented under Fractional Seconds in Time Values:
A trailing fractional seconds part is permissible for temporal values in contexts such as literal values, and in the arguments to or return values from some temporal functions. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473'); +-------------------------------------------+ | MICROSECOND('2010-12-10 14:12:09.019473') | +-------------------------------------------+ | 19473 | +-------------------------------------------+However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.
尾随小数秒部分允许用于上下文中的时间值，例如文字值，以及某些时间函数的参数或返回值。例子：
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473'); +-------------------------------------------+ | MICROSECOND('2010-12-10 14:12:09.019473') | +-------------------------------------------+ | 19473 | +-------------------------------------------+但是，当 MySQL 将值存储到任何时间数据类型的列中时，它会丢弃任何小数部分并且不存储它。
Note that this behaviour has changedin v5.6.4.
请注意，此行为在 v5.6.4 中已更改。
回答by Radha Ramaiah
Some samples of inserting datetime values into MySQL(5.6.17)
MySQL 中插入日期时间值的一些示例(5.6.17)
create database if not exists mydb;
use mydb;
drop table if exists sample;
create table sample (c1 integer, c2 datetime(6),c3 varchar(30));
insert into sample values (1, '2014-07-25 11:18:10.999999', 'Actual Given Value');
insert into sample values (2, now(6), 'From NOW(6) function');
insert into sample values (3, now(), 'From NOW() function');
insert into sample values (4, sysdate(6), 'From sysdate(6) function');
insert into sample values (5, sysdate(), 'From sysdate() function');
select * from sample;
# c1, c2, c3
'1', '2014-07-25 11:18:10.999999', 'Actual Given Value'
'2', '2014-07-25 11:27:27.314114', 'From NOW(6) function'
'3', '2014-07-25 11:27:27.000000', 'From NOW() function'
'4', '2014-07-25 11:27:27.429121', 'From sysdate(6) function'
'5', '2014-07-25 11:27:27.000000', 'From sysdate() function'
