MySQL 如何存储毫秒微秒

转载自：http://blog.csdn.net/mchdba/article/details/75259947

朋友在做福利彩票业务，遇到一个存储毫秒微秒数据的需求，问我mysql里面有何解决方案。我脑中一搜索，以前没有关注到，于是去官网查看，找到11.3.6 Fractional Seconds in Time Values 这一章节，看到一个描述

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

于是做了一个小例子，check下结果

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql> 

mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(6), c3 TIMESTAMP(2) );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO fractest VALUES ('17:51:04.777', '2017-07-16 12:00:12.234', '2017-07-16 12:00:12.234');
Query OK, 1 row affected (0.00 sec)

mysql> select * from fractest;
+-------------+----------------------------+------------------------+
| c1          | c2                         | c3                     |
+-------------+----------------------------+------------------------+
| 17:51:04.78 | 2017-07-16 12:00:12.234000 | 2017-07-16 12:00:12.23 |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

mysql>     
mysql> 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27

结果看到了，使用DATETIME(3),TIMESTAMP(6)能达到存储毫秒微秒的效果，而且还会自动四舍五入。

应用中如何录入毫秒微秒呢？使用now(3)，now(6)

mysql> select now(3),now(6);
+-------------------------+----------------------------+
| now(3)                  | now(6)                     |
+-------------------------+----------------------------+
| 2017-07-18 09:24:14.969 | 2017-07-18 09:24:14.969081 |
+-------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

应用中如何查询当前的毫秒数，微秒数呢？

mysql> SELECT SUBSTR(MICROSECOND(now(3)),1,3) '毫 秒',MICROSECOND(NOW(3)) '微 秒';       
+--------+--------+
| 毫秒   | 微秒   |
+--------+--------+
| 332    | 332000 |
+--------+--------+
1 row in set (0.00 sec)

mysql> 
mysql> select substr(microsecond(c2),1,3) '毫秒' ,microsecond(c2) '微秒', c2 '日期' from fractest;
+--------+--------+----------------------------+
| 毫秒   | 微秒   | 日期                       |
+--------+--------+----------------------------+
| 234    | 234000 | 2017-07-16 12:00:12.234000 |
+--------+--------+----------------------------+
1 row in set (0.00 sec)

mysql> 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22

参考文章： 
这里写代码片https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html