公司開發(fā)了一套數(shù)據(jù)庫中間件系統(tǒng),這兩天發(fā)現(xiàn)寫入到datetime或timestamp字段時(shí)會(huì)出現(xiàn)寫入時(shí)間比真實(shí)時(shí)間大8個(gè)小時(shí)的問題.

經(jīng)過排查,發(fā)現(xiàn)是mysqldump搞得鬼.

因?yàn)槲覀兪褂玫氖莔aya維護(hù)長連接,而且長連接是復(fù)用的,當(dāng)執(zhí)行mysqldump時(shí),mysqldump默認(rèn)啟用--tz-utc參數(shù),將本連接的時(shí)區(qū)設(shè)為UTC時(shí)區(qū)(而北京的timezone是+08:00)，這樣后面某個(gè)業(yè)務(wù)使用這個(gè)數(shù)據(jù)庫連接的,都會(huì)出現(xiàn)datetime/timestamp時(shí)間+8個(gè)小時(shí).

官方文檔解釋:

--tz-utc

This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc. This option was added in MySQL 5.1.2.