mysql 时间类型 精度_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));