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
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'
【分析】
发现备库用备库的表结构信息解析binlog行数据(unpack_row)时出错,因此,此方法修复失败。
2)查看源码:
上面代码进行类型兼容性判断,本例由于精度不一致在is_conversion_ok处会返回失败。