mysql5.6 毫秒时间_mysql5.6 TIME,DATETIME,TIMESTAMP

电玩女神 2023-01-12 07:58 95阅读 0赞

5.6.4以后时间类型(TIME,DATETIME,TIMESTAMP)支持微秒

DATETIME范围 ： '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

TIMESTAMP范围：  '1970-01-01 00:00:01.000000' to'2038-01-19 03:14:07.999999'

TIME 范围：         ‘-838:59:59.000000’ to '838:59:59.000000'.

CREATE TABLE \`b\` (

\`a\`char(4) DEFAULT NULL,

\`b\` datetime(2) DEFAULT NULL,

\`c\`char(4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into b select "aaaa",now(2),"aaaa";

Query OK,1 row affected (0.17sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select \* fromb;+------+------------------------+------+

| a | b | c |

\+------+------------------------+------+

| aaaa | 2016-06-26 06:06:01.11 | aaaa |

\+------+------------------------+------+

1 row in set (0.00 sec)

\[root@localhost test\]\# hexdump -C b.ibd

0000bff000 00 00 00 00 00 00 00 2e 79 d4 a4 00 32 f0 1e |.........y...2..|0000c000 b7 5a db ef00 00 00 03 ff ff ff ff ff ff ff ff |.Z..............|0000c01000 00 00 00 00 32 fa 8f 45 bf 00 00 00 00 00 00 |.....2..E.......|0000c02000 00 00 00 00 75 00 02 00 a1 80 03 00 00 00 00 |.....u..........|0000c03000 80 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|0000c04000 00 00 00 00 00 00 00 00 95 00 00 00 75 00 00 |.............u..|0000c05000 02 00 f2 00 00 00 75 00 00 00 02 00 32 01 00 |.......u.....2..|0000c06002 00 1d 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|0000c07073 75 70 72 65 6d 75 6d 04 04 00 00 00 10 ff f0 |supremum........|0000c08000 00 00 00 0e 06 00 00 00 00 76 60 d6 00 00 01 |..........v\`....|0000c09071 01 10 61 61 61 61 99 99 b4 61 81 0b 61 61 61 |q..aaaa...a..aaa|0000c0a061 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |a...............|0000c0b000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

\[2016-06-26 06:06:01.11\]占用的存储空间：5+1=6

Data Type Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4YEAR1 byte 　　　　　　　　　　　　　　 1 byteDATE3 bytes 　　　　　　　　　　　　　　 3bytes

TIME3 bytes 　　　　　　　　　　　　　　 3 bytes +fractional seconds storage

DATETIME8 bytes 　　　　　　　　　　　　　　　5 bytes +fractional seconds storage

TIMESTAMP4 bytes 　　　　　　　　　　　　　　4 bytes + fractional seconds storage

微秒长度：

Fractional Seconds Precision Storage Required0 0bytes1, 2 1 byte

3, 4 2bytes5, 6 3 bytes

1) 5.6 支持指定小数精度

mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );

Query OK,0 rows affected (0.06sec)

mysql> INSERT INTO fractest VALUES ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');

Query OK,1 row affected (0.01sec)

mysql> SELECT \*FROM fractest;+-------------+------------------------+------------------------+

| c1 | c2 | c3 |

\+-------------+------------------------+------------------------+

| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |

\+-------------+------------------------+------------------------+

1 row in set (0.00 sec)

time类型:

mysql> insert into c select "11:20";

Query OK,1 row affected (0.17sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select \* fromc;+----------+

| a |

\+----------+

| 11:20:00 |

\+----------+

1 row in set (0.00 sec)

mysql> insert into c select "-838:59:59.000000";

Query OK, 1 row affected (0.18 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select \* from c;

\+------------+

| a |

\+------------+

| 11:20:00 |

| -838:59:59 |

\+------------+

2 rows in set (0.00 sec)

2)5.6.4以前 插入的数据支持微秒，但插入存储的数据会忽略微秒

use test

CREATE TABLE fractest( c1 TIME, c2 DATETIME, c3 TIMESTAMP );

INSERT INTO fractest VALUES ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');

SELECT\*FROM fractest;+----------+---------------------+---------------------+

| c1 | c2 | c3 |

\+----------+---------------------+---------------------+

| 17:51:04 | 2014-09-08 17:51:04 | 2014-09-08 17:51:04 |

\+----------+---------------------+---------------------+

3)5.6时间函数可以指定微秒精度

mysql> select now(2);+------------------------+

| now(2) |

\+------------------------+

| 2016-06-26 05:35:20.57 |

\+------------------------+

1 row in set (0.00 sec)

mysql> select CURTIME(2);+-------------+

| CURTIME(2) |

\+-------------+

| 05:39:22.91 |

\+-------------+

1 row in set (0.00 sec)

mysql> select SYSDATE(3);+-------------------------+

| SYSDATE(3) |

\+-------------------------+

| 2016-06-26 05:40:24.591 |

\+-------------------------+

1 row in set (0.00 sec)

mysql> select UTC\_TIMESTAMP(5);+---------------------------+

| UTC\_TIMESTAMP(5) |

\+---------------------------+

| 2016-06-26 12:41:06.24370 |

\+---------------------------+

1 row in set (0.00 sec)

4)存储

5.6.4以前，TIME,DATETIME,TIMESTAMP 分别固定占用3，8，4字节

5.6.4以后，TIME,DATETIME,TIMESTAMP占有大小取决于微秒的精度。

3 bytes + fractional seconds storage

5 bytes + fractional seconds storage

4 bytes + fractional seconds storage

而微秒的存储长度和精度的关系如下

Fractional Seconds PrecisionStorage Required

0 bytes

1, 2

1 byte

3, 4

2 bytes

5, 6

3 bytes

例如上例中的c1 TIME： 占4字节，c2 DATETIME占6字节,TIMESTAMP 占7字节，TIMESTAMP占用5字节

相关函数可以参考my\_datetime\_packed\_to\_binary

5)新老时间类型在源码中的表现

5.6 内部增加了一些新的时间类型

MYSQL\_TYPE\_TIMESTAMP2

MYSQL\_TYPE\_DATETIME2,

MYSQL\_TYPE\_TIME2,

用于支持微秒的存储。

而老的时间类型

MYSQL\_TYPE\_TIMESTAMP，

MYSQL\_TYPE\_DATETIME，

MYSQL\_TYPE\_TIME

仍然保留和支持，从而兼容老的时间数据

5.6 新建的表时间字段默认使用新的类型，参考如下代码

sql/sql\_yacc.yy:6514

| DATETIME type\_datetime\_precision

\{ $$= MYSQL\_TYPE\_DATETIME2; \}

6)binlog与新时间类型

binlog的Table\_map\_log\_event中会记录表的元数据信息，包括库，表，列信息等。新时间类型的微秒精度信息就作为列的元数据(m\_field\_metadata)进行存储。类似的大字段列的列元数据存储大字段的实际长度(Field\_blob::do\_save\_field\_metadata)。

【问题重现】

1 master 上执行

use zy

CREATE TABLE t1 (id int primary key, c1 TIME, c2 DATETIME, c3 TIMESTAMP );

set sql\_log\_bin=0;

alter table t1 modify c3 timestamp(4);

set sql\_log\_bin=1;

INSERT INTO t1 VALUES (10, '17:51:04.98887', '2014-09-08 17:51:04.866666', '2014-09-08 17:51:04.777');

2 slave上执行

show slave status\\G

Last\_Errno: 1677

Last\_Error: Column 3 of table 'zy.t1' cannot be converted from type 'timestamp' to type 'timestamp'