mysql 5.6中的时间类型的新精度介绍

留意到mysql 5.6中,可以使用select now(6),select sysdate(6),select current_timestamp(6)这样通过位数指定使用多少为的微妙数了!比如

   mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP(6)       |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)


ysql_5.6> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:47:21 | 2013-10-26 15:47:21 | 2013-10-26 15:47:21 |
+---------------------+---------------------+---------------------+

mysql_5.6> SELECT NOW(6),SYSDATE(6),CURRENT_TIMESTAMP(6);
+----------------------------+----------------------------+---------------------+
| NOW(6)                     | SYSDATE(6)                 | CURRENT_TIMESTAMP   |
+----------------------------+----------------------------+---------------------+
| 2013-10-26 15:50:12.378787 | 2013-10-26 15:50:12.378892 |2013-10-27 10:38:59.182530
+----------------------------+----------------------------+---------------------+


但在建立表格的时候,要注意:
ysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------------+
| ts                         |
+----------------------------+
| 2013-10-27 10:47:01.604891 |
+----------------------------+
1 row in set (0.00 sec)

也就是说,要用6位微妙的话,要
CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6));