• 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 statusG        
    

      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 statusG
    

     
    

      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处会返回失败。
    



    



    

    
    
          
    • 

          
  • 

            相关阅读:
    
            
              PAT T1001 Battle Over Cities-Hard Version
                
    PAT甲级2019冬季考试题解
                
    L3-016 二叉搜索树的结构
                
    PAT A1135 Is It A Red Black Tree
                
    PAT A1114 Family Property
                
    PAT A1034 Head Of Gang
                
    PAT A1151 LCA in Binary Tree
                
    什么是一揽子交易
                
    子公司自购买日（或合并日）开始持续计算的可辨认净资产（对母公司的价值）与购买日子公司可辨认净资产的公允价值有什么区别
                
    借少数股东权益，贷少数股东损益
                
                    
          
    • 
          
  • 
            原文地址：https://www.cnblogs.com/zengkefu/p/5618798.html
          
    • 

        

      



      
      

        

        

      

    

  


  

  
Copyright © 2020-2023 
  润新知