留意到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));
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));