cover

一、背景

最近负责一个数据传输的项目，其中一个需求就是能把一个DB里面的数据拉出来 ，然后回放到另外一个同构的DB。两个DB的服务不在一个时区（其实这不是重点），可能配置不同。之前有过类似的项目，当时是基建的同事负责做数据同步，同步过去以后DateTime、Timestamp字段的时区信息都丢了。老板让我调研下问题根因，不要踩之前的坑。

最早的时候看了下同事写的当时MySQL时区信息丢失的问题总结文档，文档里面当时把DateTime和Timestamp两个时区问题混为一起了，也没分析本质原因，导致我当时没看太明白，然后的武断的认为，之所以时区丢失了，是因为基础组件同步DateTime和Timestamp的时候同步的是字符串，比如2021-11-27 10:49:35.857969这种信息，我们传输的时候，只要转UnixTime然后传过去就行了（这个其实只是问题之一，其实还跟time_zone、loc配置相关，后面会说）。

先说结论，如果你能保证所有项目连接DB的DSN配置的loc和time_zone（time_zone没有配置的话会用MySQL服务端的默认配置） 都是一样的，那不用看下去了。不管你数据在不同DB之间怎么传输，服务读取的DB的时区都是符合你的预期的。

二、基础知识

2.1 Unix时间戳能确定唯一时刻

UNIX时间，是UNIX或类UNIX系统使用的时间表示方式：从UTC 1970年1月1日0时0分0秒起至现在的总秒数('1970-01-01 00:00:00' UTC)。

时间字符串2021-11-27 02:06:50是不能确定确定唯一时刻的（直白点说就是中国人说的2021-11-27 02:06:50和美国人说的2021-11-27 02:06:50不是同一时刻），简单说就是 UnixTime = 2021-11-27 02:06:50 + time_zone,UnixTime + time_zone 可以得到不同地区人看到的time_string。

我们在数据传输和过程中，是希望这个唯一时刻保持不变，并不是希望时区保持不变。我发一条消息在中国时间是2021-11-27 02:06:50，在其他地方应该是显示其他地方的当地时间。

t := time.Unix(1637950010, 0) // 时刻唯一确定，可以打印这个时刻不同时区的时间串
fmt.Println(t.UTC().String()) // 2021-11-26 18:06:50 +0000 UTC
fmt.Println(t.String()) // 2021-11-27 02:06:50 +0800 CST

now := time.Now()
fmt.Println(now.UTC().String()) // 2021-11-27 18:06:50.981506 +0000 UTC
fmt.Println(now.String()) // 2021-11-27 02:06:50.981506 +0800 CST m=+0.000326041

2.2 MySQL DateTime 存储信息不带时区

DataTime 表示范围 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'。5.6.4 版本之前，DateTime占用8字节，5.6.4之后默认是5字节（到秒），如果要更高精度可以配置Fractional Seconds Precision， fsp=1~2占用1字节 ，3~4占用 2个字节，5~6占用3个字节， 如DATETIME(6) 精确到秒后6位，一共占用8字节。

需要注意的是：不论是5.6.4之前，还是5.6.4之后DateTime字段里面都没有带时区信息，不能确定唯一时刻，更多可以看 MySQL官网文档。

datetime_type.jpg


2.3 MySQL Timestamp 和 time_zone

Timestamp: A four-byte integer representing seconds UTC since the epoch (‘1970-01-01 00:00:00’ UTC)
The Timestamp data type is used for values that contain both date and time parts. Timestamp has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

Timestamp就是存的Unix时间戳，表示范围是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'，是不是Timestamp就没有时区问题？并不是。MySQL官方文档有如下一段话如下：

MySQL converts Timestamp values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a Timestamp value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.

简单说，每个session可以设置不同的time_zone，如果你设置session用的time_zone和读取session用的time_zone不一样，那你会得到错误/不同的值。说白了一个Timestamp字段，写入和读取的session必须一样。针对单个DB的场景，建议所有session的dsn都不配置time_zone。

time_zone 有三种设置方法

set time_zone = '+8:00'; // 设置当前 session 的 time_zone，立即生效
set global time_zone = '+8:00'; // 设置MySQL全局默认配置，新的连接才生效

dsn里面指定 time_zone='+8:00'
user:pwd@tcp(host:port)/db?charset=utf8mb4&parseTime=True&loc=Asia%2FShanghai&time_zone=%27%2B8%3A00%27

2.3 SQL 数据传输时候，DataTime和Timestamp都是字符串传输

DROP TABLE IF EXISTS `ts_test`;
CREATE TABLE ts_test (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
    `program_insert_time` varchar(100) COMMENT '代码里面获取的时间字符串，insert 语句用的',
    `time_zone` INT COMMENT '插入的时候当前 session 的 time_zone 设置的是什么',
    `loc` varchar(20) COMMENT '插入这个语句时候，dsn 的 loc',
    `ts` Timestamp(6),
     PRIMARY KEY (id)
);

然后分别执行

INSERT INTO `dt_test` (`loc`,`program_insert_time`,`dt`) VALUES ('Asia/Shanghai','2021-11-27 14:08:07.3751 +0000 UTC','2021-11-27 14:08:07.3751')
SELECT * FORM `dt_test`

wireshark 抓包可知SQL传输的时候，DataTime和Timestamp都是直接传输不带时区的字符串，如2021-11-27 14:08:07.3751这种。

insert_1.jpg

insert_2.jpg

select_req.jpg

select_resp.jpg


三、问题分析

3.1 Datetime 问题分析

上面我们说过SQL请求和响应的Data里面Datetime和Timestamp字段都是用时间字符串，我们用GORM执行SQL的时候，我们传的对Golang的time.Time，这个time类型的时间是怎么最终转换成不带时区的时间字符串呢？翻了下go-sql-driver代码，看到有下面这段逻辑。

case time.Time:
    paramTypes[i+i] = byte(fieldTypeString)
    paramTypes[i+i+1] = 0x00

    var a [64]byte
    var b = a[:0]

    if v.IsZero() {
        b = append(b, "0000-00-00"...)
    } else {
        b, err = appendDateTime(b, v.In(mc.cfg.Loc)) // v 就是我们传入的 time.Time 对象 
        if err != nil {
            return err
        }
    }

看下 appendDateTime 函数逻辑就是把time.Time转成mc.cfg.Loc时区的字符串。

举例说明就是，我们插入一个SQL的时候，假设是代码里面 time.Now() 获取了一个时间对象，这个时间对象是有时区信息的（或者说是能确定唯一时刻的），时区是当前系统的时区。传到go-sql-driver里面去以后，driver需要把这个对象转成不带时区的字符串，具体要转成哪个时区的字符串，就是由mc.cfg.Loc决定的。我们再往上跟下看下mc.cfg.Loc是哪里传入的。找到如下代码，由代码可以知道，loc信息是我们配置dns连接串的时候传入的,loc不传的话，默认是UTC 0时间

https://github.com/go-sql-driver/mysql/blob/master/driver.go#L73
// OpenConnector implements driver.DriverContext.
func (d MySQLDriver) OpenConnector(dsn string) (driver.Connector, error) {
    cfg, err := ParseDSN(dsn) // https://github.com/go-sql-driver/mysql/blob/6cf3092b0e12f6e197de3ed6aa2acfeac322a9bb/dsn.go#L291
    if err != nil {
        return nil, err
    }
    return &connector{
        cfg: cfg,
    }, nil
}

// https://github.com/go-sql-driver/mysql/blob/6cf3092b0e12f6e197de3ed6aa2acfeac322a9bb/dsn.go#L68
// NewConfig creates a new Config and sets default values.
func NewConfig() *Config {
    return &Config{
        Collation:            defaultCollation,
        Loc:                  time.UTC, // loc 传的话，默认是UTC时间
        MaxAllowedPacket:     defaultMaxAllowedPacket,
        AllowNativePasswords: true,
        CheckConnLiveness:    true,
    }
}


// Connect implements driver.Connector interface.
// Connect returns a connection to the database.
func (c *connector) Connect(ctx context.Context) (driver.Conn, error) {
    var err error

    // New mysqlConn
    mc := &mysqlConn{
        maxAllowedPacket: maxPacketSize,
        maxWriteSize:     maxPacketSize - 1,
        closech:          make(chan struct{}),
        cfg:              c.cfg,
    }
    mc.parseTime = mc.cfg.ParseTime

再来看查询的时候，时间字符串的转换问题，上面用WireShark抓包的时候，知道我们执行Select查询数据的时候，MySQL给我们返回的也是时间字符串。那客户端代码是如何转成time.Time对象的？我们知道dsn里面有个parseTime字段是来控制，从parseTime相关代码我们可以找到如下代码。

if !mc.parseTime {
    continue
}

// Parse time field
switch rows.rs.columns[i].fieldType {
case fieldTypeTimestamp,
    fieldTypeDateTime,
    fieldTypeDate,
    fieldTypeNewDate:
    if dest[i], err = parseDateTime(dest[i].([]byte), mc.cfg.Loc); err != nil {
        return err
    }
}

看下 parseDateTime 函数，就是用mc.cfg.Loc加时间字符串转换成了time.Time

func parseDateTime(b []byte, loc *time.Location) (time.Time, error) {
    const base = "0000-00-00 00:00:00.000000"
    switch len(b) {
    case 10, 19, 21, 22, 23, 24, 25, 26: // up to "YYYY-MM-DD HH:MM:SS.MMMMMM"
        if string(b) == base[:len(b)] {
            return time.Time{}, nil
        }

3.2 Datetime 总结

Datetime在MySQL服务端保存的只是一个字符串，时区信息都是由连接串的loc字符串控制的。如果要想时区保证一致，写入和读取的loc必须保证一致。

需要注意几点：

  1. loc配置是给插入的时候用time.Time转时间字符串用的。如果你裸写插入SQL（RawSQL），loc怎么配置，都不会影响时间串，数据存的时间，就是你Insert语句里面拼接的时间串。
  2. 如果们插入的是time.Time (能确定唯一时刻)对象，插入客户端所在的系统的时区信息对插入结果没影响，因为客户端是用time.Time+loc来得到时间字符串。
  3. loc 没有配置的话，默认是UTC0

datetime.jpg


3.3 Timestamp

Timestamp在go-sql-driver里面的处理流程跟Datetime一样，区别是是时间字符串到了服务端，服务端会用time_zone加字符串得到UnixTime然后保存（这部分只是个人猜想，并没有去找MySQL源码验证，只是通过简单的代码测试和官方文档来验证自己的想法），从结果上来看，读入和写入的session的time_zone必须保持一致读的数据才是对的。

time_zone 相关官方文档

Timestamp 存的4字节UTC时间

Timestamp 和 time_zone 关系 第七段


3.4 Timestamp 总结

如果真的要存时间戳，建议用bigint存，这样不管数据怎么传输，不管loc、time_zone 怎么配置，都没有时区问题。

Timestamp.jpg


四、数据传输的时候如何保证数据正确

知道了上面的基本信息以后，数据传输系统要做的事就很明确了。

  1. 读取和写入的数据的时候，loc和time_zone配置跟业务方保持一致就行了。
  2. DTS数据传输的时候，因为binlog字段都是字符串，需要把时间字符串+loc转成时间戳，然后发送到对端。

dts.png

五、问题本质

MySQL 存储、写入读取传输时候都是时间字符。客户端发送和接收的时候需要用loc来标明这个字符串的时区信息，所以读取和写入的loc必须要保证是相同的，所以这个字符串才有相同的语义。

如果所有业务方，都不设置loc，统一都是默认配置。时间戳，直接用bigint存那就没有任何时区问题。世界美好一点不好吗？何必自己给自己折腾一堆莫名其妙问题。