mysql5.6 毫秒时间_mysql 5.6 更高精度的时间

mysql从5.6开始，时间有了更高的精度：秒支持到小数点后6位。

[DB152](root@localhost) [test]> SELECT now(6), sysdate(6), CURRENT_TIMESTAMP(6);

+----------------------------+----------------------------+----------------------------+

| now(6) | sysdate(6) | CURRENT_TIMESTAMP(6) |

+----------------------------+----------------------------+----------------------------+

| 2014-01-22 13:58:48.141652 | 2014-01-22 13:58:48.141652 | 2014-01-22 13:58:48.141652 |

+----------------------------+----------------------------+----------------------------+

不加参数的话，不显示小数点后面的部分，这样就可以和旧版本的mysql兼容，另外也可以自己指定精确到小数点后几位:

[DB152](root@localhost) [test]> SELECT now(3), sysdate(), CURRENT_TIMESTAMP(0);

+-------------------------+---------------------+----------------------+

| now(3) | sysdate() | CURRENT_TIMESTAMP(0) |

+-------------------------+---------------------+----------------------+

| 2014-01-22 14:00:47.836 | 2014-01-22 14:00:47 | 2014-01-22 14:00:47 |

+-------------------------+---------------------+----------------------+

如果指定大于6的数字，就会报错：

ERROR 1426 (42000): Too big PRECISION 7 specified FOR COLUMN 'now'. Maximum IS 6.

但是在建表语句中，却无法在CURRENT_TIMESTAMP中指定精度。

这样是错误的：

CREATE TABLE t1 (

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

) engine=innodb DEFAULT charset=utf8;

ERROR 1067 (42000): Invalid DEFAULT VALUE FOR 'created_at'

原因在于timestamp类型的字段默认精度为0，但是我们又人工指定了DEFAULT CURRENT_TIMESTAMP(6)，

前后不对应，就会出错。

解决办法就是timestamp和CURRENT_TIMESTAMP使用相同的精度：

CREATE TABLE t1 (

`created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

) engine=innodb DEFAULT charset=utf8;

[DB152](root@localhost) [test]> INSERT INTO t1 VALUES();

Query OK, 1 ROW affected (0.00 sec)

[DB152](root@localhost) [test]> SELECT * FROM t1;

+----------------------------+

| created_at |

+----------------------------+

| 2014-01-22 14:10:05.013561 |

+----------------------------+

1 ROW IN SET (0.00 sec)

写入的时候，我们也可以再次指定精度，小数位数不足就会用后导0来补全：

[DB152](root@localhost) [test]> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(3));

Query OK, 1 ROW affected (0.01 sec)

[DB152](root@localhost) [test]> SELECT * FROM t1;

+----------------------------+

| created_at |

+----------------------------+

| 2014-01-22 14:12:01.515000 |

+----------------------------+

2 ROWS IN SET (0.00 sec)

[DB152](root@localhost) [test]> SELECT version();

+------------+

| version() |

+------------+

| 5.6.13-log |

+------------+

1 ROW IN SET (0.01 sec)

如果建表的时候，指定4位精度，写入时，指定6位精度，那么就会丢失后面2位精度了~ 周知。

另外, 5.6 , 可以支持多个default current_timestamp字段了:

[DB152](root@localhost) [test]> CREATE TABLE t1 (

-> t INT NOT NULL DEFAULT 0,

-> `created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ,

-> `last_op_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> `last_op_time4` TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)

-> ) engine=innodb DEFAULT charset=utf8;

Query OK, 0 ROWS affected (0.00 sec)

[DB152](root@localhost) [test]>

[DB152](root@localhost) [test]> INSERT INTO t1 SET t=1;

Query OK, 1 ROW affected (0.00 sec)

[DB152](root@localhost) [test]> SELECT * FROM t1;

+---+-------------------------+---------------------+--------------------------+

| t | created_at | last_op_time | last_op_time4 |

+---+-------------------------+---------------------+--------------------------+

| 1 | 2014-01-22 14:24:31.201 | 2014-01-22 14:24:31 | 2014-01-22 14:24:31.2016 |

+---+-------------------------+---------------------+--------------------------+

1 ROW IN SET (0.00 sec)

[DB152](root@localhost) [test]> UPDATE t1 SET t=2;

Query OK, 1 ROW affected (0.00 sec)

ROWS matched: 1 Changed: 1 Warnings: 0

[DB152](root@localhost) [test]> SELECT * FROM t1;

+---+-------------------------+---------------------+--------------------------+

| t | created_at | last_op_time | last_op_time4 |

+---+-------------------------+---------------------+--------------------------+

| 2 | 2014-01-22 14:24:31.201 | 2014-01-22 14:24:47 | 2014-01-22 14:24:47.3286 |

+---+-------------------------+---------------------+--------------------------+

1 ROW IN SET (0.00 sec)