朋友在做福利彩票业务，遇到一个存储毫秒微秒数据的需求，问我mysql里面有何解决方案。我脑中一搜索，以前没有关注到，于是去官网查看，找到11.3.6 Fractional Seconds in Time Values 这一章节，看到一个描述
于是做了一个小例子，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>
结果看到了，使用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>
应用中如何查询当前的毫秒数，微秒数呢？
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>
参考文章： 这里写代码片https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html