前兩天有做一個基于binglog的數據庫實時同步,一張老數據表里有DATETIME、TIMESTAMP不同的時間字段類型,看起來值都是一樣的,并且默認值都設置的 0000-00-00 00:00:00,導致我這邊讀取binlog更新數據庫直接悲劇。

由于本人之前一直習慣使用int存儲時間戳(問題相對比較少,容易處理),所以對這幾個時間字段類型了解不是很多.后來參考了今天分享的這篇文檔,終于有了一個基本的認識。

因為本文的翻譯者水平比較差, 翻譯的初衷也是想順便提升下英語能力,導致下面的有很多地方讀起來可能并不是很通順,但過分加工怕會是錯誤的描述.所以如果有誤導讀者地方,本文概不負責

原版文檔鏈接或直接閱讀原文：

The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

DATE、DATETIME 和 TIMESTAMP這三個字段是相關聯的。咱們這個章節就是來介紹一下它們的特點，它們有哪些相似之處，又有哪些不同之處。MySQL認證了DATE, DATETIME 和 TIMESTAMP 它們的值的有效格式，具體的描述在9.1.3章節中介紹(Date and Time Literals)。“supported” means that although earlier values might work, there is no guarantee.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

DATE 這個類型的值是由日期組成的且沒有時分秒等。 在MySQL中DATE檢索和顯示的格式是'YYYY-MM-DD'(年月日例如2018-11-17).DATE的支持的取值范圍是'1000-01-01' to '9999-12-31'

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

DATETIME 這個類型的值是由日期和時分秒組成的。在MySQL中DATETIME檢索和顯示的格式是'YYYY-MM-DD HH:MM:SS''(年月日例如2018-11-17 14:30:45).DATETIME的支持的取值范圍是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

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 這個類型的值是由日期和時分秒組成的。TIMESTAMP的支持的取值范圍是'1970-01-01 00:00:01' to '2038-01-19 03:14:07'。這個就比較確切了，使用有風險，使用需謹慎。

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

一個DATETIME 或者 TIMESTAMP 類型的值會在以微妙(6位)作為精確度時會引入極小的延遲。需要特別注意的是，不管多小的數值只要是以DATETIME or TIMESTAMP 類型添加到數據庫就會被存儲而不是舍棄。由于有這些小數，所以它們格式化以后展現形式是'YYYY-MM-DD HH:MM:SS[.fraction]'， DATETIME的取值范圍為'1000-01-01 00:00:00.' 到 '9999-12-31 23:59:59.999999'，TIMESTAMP的取值范圍為'1970-01-01 00:00:01.' to '2038-01-19 03:14:07.999999'.。這些小數部分是用小數點分開的，不是別的公認的小數分隔符。有關微秒數的信息，看11.3.6章節的內容(“Fractional Seconds in Time Values”)可以看到。

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

TIMESTAMP 和 DATETIME 類型提供了自動初始化和更新當前的日期和時間。更多詳情將參考11.3.5章節中查看(“Automatic Initialization and Updating for TIMESTAMP and DATETIME”)

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.13, “MySQL Server Time Zone Support”.

MySQL將 TIMESTAMP 的值從當前時區轉換成UTC進行存儲，并且會從UTC轉成當前時區進行檢索(這樣就不會發生出現別的類型例如DATETIME)。默認情況下，每個連接的當前時區是服務器的時間。在每個基礎連接中時區都可以被設置。由于時區設置能保持不變，你也就能拿到你存儲的數值。如果你存儲了TIMESTAMP的值，然后更改了時區并且從數據庫取回這個值，這個被取回的值跟你存儲的值是不一樣的。這個發生的原因是用的不是相同的時區用了相同的轉變的方式。當前時區是可以根據當前時區的系統變量獲取的。想要了解更多信息請查看5.1.13章節的信息(“MySQL Server Time Zone Support”)

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

無效的DATE, DATETIME, or TIMESTAMP 值會被修改為0 即合適的類型(('0000-00-00' or '0000-00-00 00:00:00'))

MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the :, but is interpreted as the year '2010-11-12' if used in a date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a valid month.

MySQL 對指定字符串的值的格式要求比較寬松，在date部分和時間部份可以使用任何標點符號作為定界符。在一些事例中，這樣的語法可以欺騙你。例如：'10:11:12'這個值因為：看起來像時間的值，但是如果它被當日期內容就會被理解成'2010-11-12'。 數值'10:45:15' 會被修改為'0000-00-00' 因為45不是月份的值。

The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

在date和time的微妙數 唯一被公認的定界符是小數點。

MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'.

MySQL 不接受 TIMESTAMP的值(在天或者月份里只有一個0，在MySQL中這是無效的日期)。這條規則唯一的例外是0它被理解為'0000-00-00 00:00:00'。

Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:

日期里面用兩位數表示年的值是模糊的因為不知道是哪個世紀。MySQL 對兩位數表示年的規定了以下規則：

Year values in the range 00-69 are converted to 2000-2069.

范圍在00-69年的被修改為2000-2069

Year values in the range 70-99 are converted to 1970-1999.

范圍在70-99年的被修改為1970-1999.

－－－－還沒有完－－－－

問題總結:

1. 無效的值和默認設置0000-00-00 00:00:00,顯示值插入會報錯。

2.底層存儲時間都是UTC,然后操作時候根據時區環境變量轉換成相應時區。

總結

以上是生活随笔為你收集整理的mysql 有always on么,mysql 关于时间类型的刨坑之路的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。