首頁  >  文章  >  資料庫  >  一起聊聊mysql的timestamp會存在時區問題

一起聊聊mysql的timestamp會存在時區問題

WBOY
WBOY轉載
2022-01-10 19:02:433942瀏覽

這篇文章為大家帶來了mysql中timestamp時間類型相關的知識,mysql中有兩個時間類型,timestamp與datetime,希望對大家有幫助。

一起聊聊mysql的timestamp會存在時區問題

眾所周知,mysql有兩個時間類型,timestamp與datetime,但當在網路上搜尋timestamp與datetime區別時,會發現網路上有不少與時區有關的完全相反的結論,主要兩種:

  • timestamp沒有時區問題,而datetime有時區問題,原因是timestamp是以UTC格式儲存的,而datetime儲存類似於時間字串的形式

  • timestamp也有時區問題

兩種觀點讓人迷惑,那timestamp到底會不會有時區問題呢?

基本概念

時區:

由於地域的限制,人們發明了時區的概念,用來適應人們在時間感受上的差異,例如中國的時區是東8區,表示為8:00,或GMT 8,而日本的時區是東9區,表示為9:00,或GMT 9 ,當中國是早上8點時,日本是早上9點,即東8區的8點與東9區的9點,這兩個時間是相等的。

另外時間還有以下兩個概念:

絕對時間:

如unix時間綴,是1970-01-01 00:00:00開始到現在的秒數,如:1582416000,這種表示法是絕對時間,不受時區影響,也叫紀元時epoch。

本地時間:

相對於某一時區的時間,是本地時間,例如東8區的2020-02-23 08:00:00,是中國人的本地時間,而在此時,日本人的本地時間是2020-02-23 09:00:00,所以本地時間都是與某一時區相關的,脫離時區看本地時間,是沒有意義的,因為你並不知道這具體是指的什麼時間點。

例如在Java中,Date物件是絕對時間,透過SimpleDateFormat格式化出來的yyyy-MM-dd HH:mm:ss形式的時間字串,是本地時間,如果SimpleDateFormat沒有呼叫setTimeZone()顯示指定時區,那麼預設用的是jvm運行在的作業系統上的時區,我們開發機上的時區基本上都是GMT 8。

timestamp與datetime區別

如下,我建立了一張表,裡面time_stamp是timestamp類型,date_time是datetime類型,create_timestamp、create_datetime是timestamp與datetime類型,但是它們可以由資料庫自動產生。

CREATE TABLE `time_test` (
  `id` bigint unsigned,
  `time_stamp` timestamp,
  `date_time` datetime,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
)

1、先將資料庫時區設定為8:00,也就是中國的東8區

一起聊聊mysql的timestamp會存在時區問題

2、然後如下手動插入一個固定時間的數據,以及用now()函數插入當前時間

一起聊聊mysql的timestamp會存在時區問題

3、當插入完數據後,然後我們修改當前會話的時區為9:00,即日本的東9區,然後再次查看資料

一起聊聊mysql的timestamp會存在時區問題

4、如上,定義為timestamp類型的列time_stamp、create_timestamp不管是手動插入的,還是now()函數插入的,東9區都比東8區的時間大1個小時,這是正確的,說明timestamp類型是時區相關的,然而定義為datetime類型的date_time、create_datetime字段,時間都沒有變化,這說明datetime類型是時區無關的。

結論:

timestamp在儲存上是包含時區的,而datetime是不包含時區,說明網路上的第一種說法是對的。

再看個例子

我們將東8區的的2020-02-23 08:00:00轉換為unix時間綴(絕對時間),再插入資料庫試試?

如下,使用linux的date指令轉換時間字串為unix時間綴:

$ "date" --date="2020-02-23 08:00:00 +08:00" +%s
1582416000

然後用mysql的from_unixtime()函數,將unix時間綴轉換為mysql時間類型來插入資料。

一起聊聊mysql的timestamp會存在時區問題

如上,查詢出來的時間,也是東9區的9點,時間也是正確的。

為什麼網路上又說timestamp型別存在時區問題?

我發現網上說timestamp有時區問題,都是應用端插入數據,然後到資料庫中去看,結果發現時間不一樣,因此我打算在Java中寫個Demo試一下,看能不能重現這個問題。

1、首先,以下是Java中Entity的定義,與上面的time_test表對應,注意,這裡面時間屬性都是用Date型別定義的,如下:

一起聊聊mysql的timestamp會存在時區問題

2、然後,我寫了兩個介面/insert與/queryAll來插入與查詢數據,如下:

一起聊聊mysql的timestamp會存在時區問題

3、然后我把数据库的时区设置为+09:00时区,即日本的东9区,如下:

一起聊聊mysql的timestamp會存在時區問題

4、然后调用/insert接口插入数据,注意我接口传入的时间是东8区的8点,如下:

一起聊聊mysql的timestamp會存在時區問題

5、插入完后,去数据库中查询一把,如下:

一起聊聊mysql的timestamp會存在時區問題

可以看到,time_stamp字段时间是9点,且我已将数据库时区设置为东9区,东9区的9点与东8区的8点,这两个时间实际是相等的,因此时间数据没错。

6、然后我使用/queryAll接口将数据查询出来,如下:

一起聊聊mysql的timestamp會存在時區問題

timeStamp属性是1582416000000,这是毫秒级的时间缀,秒级则是1582416000,对应是东8区的2020-02-23 08:00:00,时间数据也没错!

7、然后我又将mysql时区修改回+8:00,并重启我们的java应用,如下:

一起聊聊mysql的timestamp會存在時區問題

8、再查询一下数据,如下:

一起聊聊mysql的timestamp會存在時區問題

timeStamp属性还是1582416000000,时间没有变化,这也是正确的。

那为什么网上会说timestamp存在时区问题?

经过一翻查看,我发现他们都提到了jdbc的serverTimezone,会不会是这个配置错误导致的呢?就先试试吧!

1、如图,我把数据库时区修改回+9:00时区,然后故意把jdbc的url上的serverTimezone配置为与数据库不一致的GMT+8时区,然后重启java应用,如下:

一起聊聊mysql的timestamp會存在時區問題

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

其中GMT%2B8就是GMT+8,因为在url上需要urlencode,所以就变成了GMT%2B8。

2、重新插入数据,注意插入的时间还是东8区的8点,如下:

一起聊聊mysql的timestamp會存在時區問題

3、然后,我再到数据库中查询一把,如下:

一起聊聊mysql的timestamp會存在時區問題

time_stamp中时间竟然是8点!要知道我们虽然插入的是东8区的8点,但当前会话可是东9区的,东8区的8点等于东9区的9点,所以正确显示应该为9点才对,时间差了1小时!

4、然后,我又调用/queryAll接口查询了一把,想看看mybatis查询出来的时间数据对不对,如下:

一起聊聊mysql的timestamp會存在時區問題

可以看到timeStamp是1582416000000,秒级是1582416000,这个时间就是东8区的8点,东9区的9点啊!查询出来的时间竟然是正确的,为什么???

serverTimezone的本质

为了找出问题所在,我调试了一下mysql的jdbc驱动代码,终于弄明白了原因,主要可以看看如下这几点:

1.mysql驱动创建连接后,会调用com.mysql.jdbc.ConnectionImpl#configureTimezone()来配置此连接的时区,如果配置了serverTimezone,则会使用serverTimezone配置的时区,没配置时会去取数据库中的time_zone变量,这就是为什么我们没有配置serverTimezone变量时,结果也是正确的。

//若使用普通驱动,使用此方法配置mysql连接的时区
com.mysql.jdbc.ConnectionImpl#configureTimezone()
//若使用cj驱动,使用此方法配置mysql连接的时区
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()

2.调用jdbc的setTimestamp()方法时,实际调用的是com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp(),这里面会根据serverTimezone指定的时区,将对应的Timestamp对象转换为serverTimezone指定时区的本地时间字符串。

3.执行sql语句时,会执行com.mysql.cj.jdbc.ClientPreparedStatement#execute(),这里面sendPacket变量保存着真实会发送到mysql的sql语句。

注:看的是8.0.11版本mysql-connector-java驱动源码,不同版本代码会稍有差异,比如5.2.16版本驱动,jdbc url上需要同时配置这两个配置:useTimezone=true&serverTimezone=GMT%2B8,且setTimestamp()对应的是com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。

原理总结如下:

mysql驱动在发送sql前,会将jdbc中的Date对象参数,根据serverTimeZone配置的时区转化为日期字符串后,再发送sql请求给mysql server,同样在mysql server返回查询结果后,结果中的日期值也是日期字符串,mysql驱动会根据serverTimeZone配置的时区,将日期字符串转化为Date对象。

因此,当serverTimeZone与数据库实际时区不一致时,会发生时区转换错误,导致时间偏差,如下:

a、比如sql参数是一个Date对象,时间值是东8区的2020-02-23 08:00:00,注意它里面存储的可不是2020-02-23 08:00:00这个字符串,它是Date对象(绝对时间),只是我用文字表达出来是东8区的2020-02-23 08:00:00。

b、然后,由于serverTimeZone配置的是东8区,mysql驱动会将这个Date对象转为2020-02-23 08:00:00,注意这时已经是字符串了,然后再将sql发送给mysql,注意这里的sql里面已经将Date参数替换为2020-02-23 08:00:00了,因为Date对象本身是无法走网络的。

c、然后mysql数据库接收到这个时间字符串2020-02-23 08:00:00后,由于数据库时区配置是东9区,它会认为这个时间是东9区的,它会以东9区解析这个时间字符串,这时数据库保存的时间是东9区的2020-02-23 08:00:00,也就是东8区的2020-02-23 07:00:00,保存的时间就偏差了1个小时。

d、查询结果里时间为什么又对了呢,因为查询结果返回了东9区的时间字符串,而java应用又将其理解为是东8区的时间,负负得正了!

将serverTimezone与mysql时区保持一致

so,那么如果我们将serverTimezone配置改正确,即与数据库保持一致时,应该查询到的时间就会是错的,会少1个小时。

1、jdbc url中使用与数据库一样的东9区GMT+9,如下:

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

其中的GMT%2B9,即是GMT+9。

2、然后重启Java应用,再查询一把看看,如下:

一起聊聊mysql的timestamp會存在時區問題

返回的是毫秒级时间缀1582412400000,秒级就是1582412400,使用linux的date命令转换为时间字符串形式:

$ "date" --date="@1582412400" +"%F %T %z"
2020-02-23 07:00:00 +0800

看到没,它是东8区的7点,刚好差了1个小时。

3、所以,使用mysql的timestamp类型时,对于java应用来说,一定要保证jdbc url中的serverTimezone与数据库中的时区配置是一致的。

另外一点是,当没有配置serverTimezone时,mysql驱动会自动读取mysql server中配置的时区,这里面也有坑!如下:

mysql驱动自动读取数据库时区的坑

3.1 mysql安装好后,默认时区是SYSTEM,而SYSTEM指的是system_time_zone变量的时区,如下:

一起聊聊mysql的timestamp會存在時區問題

3.2 当mysql驱动读到time_zone变量是SYSTEM时,会再去读取system_time_zone变量,而system_time_zone对于国内来说,默认是CST,这是一个混乱的时区,是4个不同时区的缩写,如下:

一起聊聊mysql的timestamp會存在時區問題

对于Linux或MySQL,会认为CST是中国标准时间(+8:00),但Java却认为CST是美国标准时间(-6:00)(注:可能和Java运行在Windows中有关):

如下,linux中CST等于+0800,即中国时区:

$ "date" +"%F %T %Z %z"
2021-09-12 18:35:49 CST +0800

如下,java中CST等于-06:00,美国时区:

一起聊聊mysql的timestamp會存在時區問題

3.3 因此mysql驱动取到CST这个时区值时,它会以为这是-6:00时区,但MySQL却理解为+8:00时区,因此MySQL时区一定不要配置为CST,而要配置为具体的时区,如+8:00,但如果MySQL时区为CST且不可修改的情况下,一定要配置jdbc的serverTimezone为清晰的时区(如:GMT+8)。

Entity中日期属性是String呢?

1、我们将Entity对象中的时间属性改为String(不推荐),如下:

一起聊聊mysql的timestamp會存在時區問題

2、然后也写两个接口,/insert2与/queryAll2,如下:

一起聊聊mysql的timestamp會存在時區問題

3、然后插入数据,注意这时我是直接将无时区的8点,作为参数给到sql的,如下:

一起聊聊mysql的timestamp會存在時區問題

4、然后再查询一把,如下:

一起聊聊mysql的timestamp會存在時區問題

如上所示,time_stamp字段值是8点,但此时数据库时区是东9区,所以这是东9区的8点。

5、然后我将数据库与jdbc中serverTimezone都改为东8区呢,改完后重启Java应用,如下:

一起聊聊mysql的timestamp會存在時區問題

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

6、再次插入数据,参数还是无时区的8点,如下:

一起聊聊mysql的timestamp會存在時區問題

7、再查询一把,如下:

一起聊聊mysql的timestamp會存在時區問題

如上所示,time_stamp字段值是8点,但现在数据库时间是东8区,所以这是东8区的8点。

8、然后我再将jdbc url上的serverTimezone调整为东9区,然后重启Java应用,如下:

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

现在serverTimezone与数据库中不一致,数据库是东8区,serverTimezone是东9区。

9、我们再次插入无时区的8点,如下:

一起聊聊mysql的timestamp會存在時區問題

10、然后再查询一把,如下:

一起聊聊mysql的timestamp會存在時區問題

time_stamp字段值还是8点,数据库是东8区,所以这是东8区的8点,但我们serverTimezone与数据库的时区不一致啊,没看到时间有偏差,为什么?

解释一下

前面说过了,对于jdbc中的Date对象,在发送给mysql前,会先根据serverTimezone转换为相应时区的时间字符串,但现在Entity中时间属性是String类型,mysql驱动不会进行转换,所以不管serverTimezone怎么配置,对String类型的时间串都没影响。

这样的话,似乎java中日期类型用时间字符串来存还好些,不容易出错,但请再认真考虑一下,调用方传了一个无时区的8点,数据库自作主张,就将其认为是东9区的8点,但如果这个时间字符串实际是东8区的8点呢?这时如果保存到数据库中为东9区的8点,那数据就存错了!

那如果目前api接口就传的无时区的时间串,Entity中就定义的String,怎么解决呢?

1、询问接口定义人员,这个接口的时间串指的是哪个时区的,比如是东8区的2020-02-23 08:00:00。

2、然后接口接收到时间后,要以东8区将时间字符串转换为Date对象,如下:

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
Date date = sdf.parse("2020-02-23 08:00:00");

3、然后如果Entity中时间属性定义的是String,那么我们要再将Date对象以数据库的时区格式化为对应的时间字符串,比如数据库时区是东9区,那么格式化后就是2020-02-23 09:00:00,如下:

SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+9"));
String dateStr = sdf.format(date);
entity.setTimeStamp(dateStr);

4、然后将Entity保存到mysql中的,就也会是东9区的2020-02-23 09:00:00,结果正确。

所以,使用String类型来存储时间数据,要想将时间值保存正确,超级麻烦,不建议在实际开发中这种使用。

最佳实践

1、大多数团队会规定api中传递时间要用unix时间缀,因为如果你传一个2020-02-23 08:00:00时间值,它到底是哪个时区的8点呢?对于unix时间缀,就不会有此问题,因为它是绝对时间。而如果某些特殊原因,一定要使用时间字符串,最好使用ISO8601规范那种带时区的时间串,比如:2020-02-23T08:00:00+08:00。

2、Mybatis中Entity定义要与数据库定义一致,数据库中是timestamp,那么Entity中要定义为Date对象,因为mysql驱动在执行sql时,会自动根据serverTimezone配置帮你转换为数据库时区的时间串,如果你自己来转换,你极有可能因为忘记调用setTimeZone()方法,而使用当前java应用所在机器的默认时区,一旦java应用所在机器的时区与数据库的时区不一致,就会出现时区问题。

3、jdbc的serverTimezone参数,要配置正确,当不配置时,mysql驱动会自动读取mysql server的时区,此时一定要将mysql server的时区指定为清晰的时区(如:+08:00),切勿使用CST。

4、如果数据库时区修改后,jdbc的serverTimezone也要跟着修改,并重启Java应用,就算没有配置serverTimezone,也需要重启,因为mysql驱动初始化连接时,会将当前数据库时区缓存到一个java变量中,不重启Java应用它不会变。

数据库中用timestamp还是int来存储时间?

如果用int型时间缀存储,不管数据库时区是啥,都不影响,因为存储的是绝对时间,看起来完美解决了时区问题。

但從某些角度看,這種方案只是把時區問題從資料庫端推到應用端去了,時區問題將出現在將時間字串轉換為時間綴的過程中,例如某程式設計師從api介面中拿到時間字串後,沒考慮時區,直接轉為unix時間綴,就可能出現時區問題。

因此,對於不帶時區的時間字串解析,一定要問清楚這是哪個時區的時間,並在程式碼中明確指定!

另外,用int儲存時間還有如下3個不好的點:

  • #開發人員看到這個欄位後,無法一目了然的了解到這個時間綴大概是個什麼時間,需要去轉換一下,會很繁瑣。

  • 像update_time這樣的字段,資料庫提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP的機制,這樣在更新任何字段時,update_time會自動更新,如果使用int存儲,就需要程序員每次更新表時,重新set這個字段,容易遺忘。

  • 由於int只有4個字節,用它來儲存時間,會在2038年後溢出,而對於timestamp來說,MySQL將其底層儲存統一修改為8個字節,相對來說還是比較容易的。

當然,也並不是建議不用int,這是見仁見智的,不管用timestamp還是int,都沒有致命性問題的。

總結

timestamp本身是沒有時區問題的,時區問題是由於serverTimezone設定錯誤、mysql使用CST這種混亂時區或Entity中將日期定義String類型導致的。

推薦學習:mysql影片教學

#

以上是一起聊聊mysql的timestamp會存在時區問題的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:juejin.im。如有侵權,請聯絡admin@php.cn刪除