mysql5.6 TIME,DATETIME,TIMESTAMP

http://www.cnblogs.com/justfortaste/p/4119722.html

 

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.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+------+------------------------+------+
| 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
0000bff0  00 00 00 00 00 00 00 00  2e 79 d4 a4 00 32 f0 1e  |.........y...2..|
0000c000  b7 5a db ef 00 00 00 03  ff ff ff ff ff ff ff ff  |.Z..............|
0000c010  00 00 00 00 00 32 fa 8f  45 bf 00 00 00 00 00 00  |.....2..E.......|
0000c020  00 00 00 00 00 75 00 02  00 a1 80 03 00 00 00 00  |.....u..........|
0000c030  00 80 00 05 00 00 00 01  00 00 00 00 00 00 00 00  |................|
0000c040  00 00 00 00 00 00 00 00  00 95 00 00 00 75 00 00  |.............u..|
0000c050  00 02 00 f2 00 00 00 75  00 00 00 02 00 32 01 00  |.......u.....2..|
0000c060  02 00 1d 69 6e 66 69 6d  75 6d 00 02 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  04 04 00 00 00 10 ff f0  |supremum........|
0000c080  00 00 00 00 0e 06 00 00  00 00 76 60 d6 00 00 01  |..........v`....|
0000c090  71 01 10 61 61 61 61 99  99 b4 61 81 0b 61 61 61  |q..aaaa...a..aaa|
0000c0a0  61 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |a...............|
0000c0b0  00 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.4
YEAR    　　　　　　　　　　1 byte   　　　　　　　　　　　　　　 1 byte
DATE   　　　　　　　　　　 3 bytes   　　　　　　　　　　　　　　 3 bytes
TIME   　　　　　　　　　　 3 bytes   　　　　　　　　　　　　　　 3 bytes + fractional seconds storage
DATETIME   　　　　　　　　8 bytes   　　　　　　　　　　　　　　　5 bytes + fractional seconds storage
TIMESTAMP    　　　　　　 4 bytes    　　　　　　　　　　　　　　4 bytes + fractional seconds storage


微秒长度：


Fractional Seconds Precision    Storage Required
0                                       0 bytes
1, 2                                    1 byte
3, 4                                    2 bytes
5, 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.06 sec)

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.01 sec)

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.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from c;
+----------+
| 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占有大小取决于微秒的精度。






TIME
3 bytes + fractional seconds storage




DATETIME
5 bytes + fractional seconds storage




TIMESTAMP
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'


 


【分析】


  


1）先尝试修复，修改slave_type_conversions='ALL_LOSSY';参数slave_type_conversions可以参考 http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#sysvar_slave_type_conversions 














1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23








mysql> show variables like 'slave_type_conversions';                                                                                         


 


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


 


| Variable_name          | Value |


 


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


 


| slave_type_conversions |       |


 


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


 


1 row in set (0.00 sec)


 


mysql> set global slave_type_conversions='ALL_LOSSY';


 


Query OK, 0 rows affected (0.00 sec)


  


show slave status\G


 


  Last_Errno: 1610


 


  Last_Error: Could not execute Write_rows event on table zy.t1; Corrupted replication event was detected, Error_code: 1610; handler error No Error!; the event's master log mysql-bin.000002, end_log_pos 550
































　　


 发现备库用备库的表结构信息解析binlog行数据(unpack_row)时出错，因此，此方法修复失败。


 


2）查看源码：














1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23


24


25


26


27


28


29


30


31


32


33


34


35


36


37


38


39


40


41


42


43


44


45


46


47


48


49


50


51


52


53


54


55


56


57


58


59


60


61


62


63


64


65


66


67


68


69


70


71


72


73


74


75


76


77


78


79


80


81


82


83


84


85








Rows_log_event::do_apply_event


 


  table_def::compatible_with


 


   can_convert_field_to


 


  ....


 


  


 


   if (field->real_type() == source_type)//本例主备类型一致


 


  {


 


    if (metadata == 0) // Metadata can only be zero if no metadata was provided  // 本例主库精度为4


 


    {


 


      /*


 


        If there is no metadata, we either have an old event where no


 


        metadata were supplied, or a type that does not require any


 


        metadata. In either case, conversion can be done but no


 


        conversion table is necessary.


 


       */


 


      DBUG_PRINT( "debug" , ("Base types are identical, but there is no metadata"));


 


      *order_var= 0;


 


      DBUG_RETURN( true );


 


    }


 


  


 


    DBUG_PRINT( "debug" , ("Base types are identical, doing field size comparison"));


 


    if (field->compatible_field_size(metadata, rli, mflags, order_var))


 


      DBUG_RETURN(is_conversion_ok(*order_var, rli));


 


    else


 


      DBUG_RETURN( false );


 


  }


 


   else if (metadata == 0 && //这里有对新老时间类型的兼容处理


 


           ((field->real_type() == MYSQL_TYPE_TIMESTAMP2 &&


 


             source_type == MYSQL_TYPE_TIMESTAMP) ||


 


            (field->real_type() == MYSQL_TYPE_TIME2 &&


 


             source_type == MYSQL_TYPE_TIME) ||


 


            (field->real_type() == MYSQL_TYPE_DATETIME2 &&


 


             source_type == MYSQL_TYPE_DATETIME)))


 


  {


 


    /*


 


      TS-TODO: conversion from FSP1>FSP2.


 


      Can do non-lossy conversion


 


      from old TIME, TIMESTAMP, DATETIME


 


      to new TIME(0), TIMESTAMP(0), DATETIME(0).


 


    */


 


    *order_var= -1;


 


    DBUG_RETURN( true);


 


  }
































　　上面代码进行类型兼容性判断，本例由于精度不一致在is_conversion_ok处会返回失败。














            

            
posted @ 
2016-06-26 22:44 
zengkefu 
阅读(1073) 
评论(0) 
编辑 
收藏 
举报