MySQL内核月报 2015.02-MySQL · 答疑释惑· 5.5 和 5.6 时间类型兼容问题

<b>问题描述</b>

5.6.4及以上版本，datetime,time,timestamp的binlog在5.6.4以下的备库无法执行，如：

5.6.16（主库）: create table t1(t datetime default now()); insert into t1 values(now());

5.5.18（备库）: show slave stauts\g ;

此时备库中断，报错：last_errno: 1677，

描述信息：last_error: column 1 of table t1.t' cannot be converted from type '&lt;unknown type&gt;' to type 'datetime'

<b>问题原因</b>

1) 5.5版本存储的是datetime,time,timestamp这三种数据类型的长整型的数据，insert时的bt为：

2) 5.6.16的相应堆栈为：

从面的两个堆栈可以看出，在构造插入数据的时候，调用的是field的具体函数，根据不同类型调用的方法不同；5.5与5.6之间，datetime的数据类型不一致，当5.5升级到5.6时，其堆栈不变，原因是在表的frm中，记录了表中列的数据类型，其中5.5中的数据类型为mysql_type_datetime，5.6的数据类型为mysql_type_datetime2，所以对于原表升级，不影响复制，但是对于新表中如果含有这三种数据类型的表，复制到备库就会出现问题，因为5.5中，没有mysql_type_datetime2这种数据类型。

<b>解决方法</b>

对表的dml操作或ddl操作，都是依赖于表结构而言的，这也是为什么物理5.5升级到5.6后，对于原本含有datetime,time,timestamp这三种类型的表没有影响，但是对于新建的表就会有影响，原因就是对于产生binlog的操作或存储引擎的操作的field来源于frm文件，所以，当在创建表的时候，如果5.5要使用5.6的binlog，那我们对于ddl含有这三种数据类型的操作，使用5.5可以识别的数据类型：mysql_type_datetime，而不是mysql_type_datetime2，这样在mysql内部的操作过程中就不会有问题，因此我们可以为mysql添加一个参数，当参数打开时，创建datetime,time,timestamp的数据类型为兼容5.5的数据类型，否则为新的数据类型。

<b>timestamp 与 datetime 的区别</b>

1. 值域不同

timestamp has a range of '1970-01-01 00:00:01' utc to '2038-01-19 03:14:07' utc. datetime the supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' timestamp带有时区信息，其中timestamp在存储时，将当前时间转化为utc格式的时间，如北京时间，现在是2014-03-15 23:21:00,那么存储的会是2014-03-15 23:21:00 - 3600s；取数据的时候会加上当前时区时间。

2. 底层的存储结构不同

5.5 是以longlong类型存储的，而5.6 的格式如下：

timestamp：4+max(3); (变长，4-7个字节)，没有sign

datetime： 底层存储(变长，5-8个字节)