[mysql] 混乱的时区

Link: https://github.com/j2gg0s/j2gg0s/blob/main/20221116_mysql_%E6%B7%B7%E4%B9%B1%E7%9A%84%E6%97%B6%E5%8C%BA.md

MySQL 的时间类型, 无论是 TIMESTAMP 还是 DATETIME, 都是不带时区信息.

Binary Protocol 中对应的数据类型 MYSQL_TYPE_DATETIME 和 MYSQL_TYPE_TIMESTAMP 都没有任何字段来传递时区信息. go-sql-driver/mysql 中解析时间类型的 parseBinaryDateTime 和 parseDateTime 也没有从 server 返回的内容中解析出时间类型.

MySQL 将时区附加在链接上, 每个链接都有对应的时区, 在没有明确指定的情况下默认是 server 的时区.

链接的时区对 TIMESTAMP 和 DATETIME 的影响却又大不相同. 当插入或更新 TIMESTAMP 时, MySQL 会将更新的内容从链接的时区转换到 UTC 再存储. 当查询 TIMESTAMP 时, MySQL 会将读取的内容从 UTC 转换到链接的时区再展示. 而 DATETIME 的插入, 更新和查询却完全不受链接时区的影响.

go-sql-driver/mysql 类似的 driver 或者 orm, 一定程度上不区分 TIMESTAMP 和 DATETIME 又进一步加剧了混乱. 当然这不是 go-sql-driver/mysql 的问题, go-sql-driver/mysql 的实现质量还是非常又保证的.

上述的总总, 在写入和读取的代码不是同一套时, 格外的明显. examples/mysql-stamp 中构造了几个典型的例子验证理解.

mysql> SELECT * FROM visitor;
+----+--------+---------------------+---------------------+---------------------+
| id | name   | visited_timestamp   | visited_datetime    | created_at          |
+----+--------+---------------------+---------------------+---------------------+
|  1 | j2gg0s | 2022-11-16 22:17:00 | 2022-11-16 22:17:00 | 2022-11-16 14:18:29 |
+----+--------+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> SET @@session.time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM visitor;
+----+--------+---------------------+---------------------+---------------------+
| id | name   | visited_timestamp   | visited_datetime    | created_at          |
+----+--------+---------------------+---------------------+---------------------+
|  1 | j2gg0s | 2022-11-17 06:17:00 | 2022-11-16 22:17:00 | 2022-11-16 22:18:29 |
+----+--------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

func ExampleStamp() {
	for i, dsn := range []string{
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true",
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true&loc=Asia%2FShanghai",
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true",
	} {
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			panic(err)
		}
		if i == 2 {
			db.Exec("SET @@session.time_zone='+08:00'")
		}

		rows, err := db.Query("SELECT * FROM visitor")
		if err != nil {
			panic(err)
		}
		for rows.Next() {
			var id int64
			var name string
			var visitedTimeStamp, visitedDateTime time.Time
			var createdAt time.Time
			err := rows.Scan(&id, &name, &visitedTimeStamp, &visitedDateTime, &createdAt)
			if err != nil {
				panic(err)
			}
			fmt.Println(id, name, visitedTimeStamp.Format(time.RFC3339), visitedDateTime.Format(time.RFC3339))
		}
		db.Close()
	}
	// Output:
	// 1 j2gg0s 2022-11-16T22:17:00Z 2022-11-16T22:17:00Z
	// 1 j2gg0s 2022-11-16T22:17:00+08:00 2022-11-16T22:17:00+08:00
	// 1 j2gg0s 2022-11-17T06:17:00Z 2022-11-16T22:17:00Z
}