时间：2021-07-01 10:21:17 帮助过：2人阅读
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
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)
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 | +----------+---------------------+---------------------+
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)
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 Precision | Storage 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.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; }
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处会返回失败。
mysql5.6 TIME,DATETIME,TIMESTAMP
标签：