MySQL :: MySQL 9.0 參考手冊 :: 13.2.5 TIMESTAMP 和 DATETIME 的自動初始化與更新

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / TIMESTAMP 和 DATETIME 的自動初始化與更新

13.2.5 TIMESTAMP 和 DATETIME 的自動初始化與更新

TIMESTAMP 和 DATETIME 欄位可以自動初始化和更新為目前的日期和時間（也就是目前的 timestamp）。

對於表格中的任何 TIMESTAMP 或 DATETIME 欄位，您可以將目前的 timestamp 指定為預設值、自動更新值，或兩者皆是

對於未指定欄位值的插入列，自動初始化的欄位會設定為目前的 timestamp。
當列中任何其他欄位的值從目前值變更時，自動更新的欄位會自動更新為目前的 timestamp。如果所有其他欄位都設定為目前值，則自動更新的欄位會保持不變。為了防止自動更新的欄位在其他欄位變更時更新，請明確地將其設定為目前的值。若要更新自動更新的欄位，即使其他欄位未變更，請明確地將其設定為它應有的值（例如，將其設定為 CURRENT_TIMESTAMP）。

此外，如果停用了 explicit_defaults_for_timestamp 系統變數，您可以透過指派 NULL 值，將任何 TIMESTAMP (但不是 DATETIME) 欄位初始化或更新為目前的日期和時間，除非該欄位已使用 NULL 屬性定義為允許 NULL 值。

若要指定自動屬性，請在欄位定義中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。子句的順序不重要。如果欄位定義中同時存在這兩者，則任何一個都可以先出現。任何 CURRENT_TIMESTAMP 的同義詞都具有與 CURRENT_TIMESTAMP 相同的含義。這些是 CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP()。

DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 的使用特定於 TIMESTAMP 和 DATETIME。DEFAULT 子句也可以用於指定常數（非自動）預設值（例如，DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'）。

注意

下列範例使用 DEFAULT 0，這個預設值可能會產生警告或錯誤，具體取決於是否啟用嚴格 SQL 模式或 NO_ZERO_DATE SQL 模式。請注意，TRADITIONAL SQL 模式包含嚴格模式和 NO_ZERO_DATE。請參閱第 7.1.11 節，「伺服器 SQL 模式」。

TIMESTAMP 或 DATETIME 欄位定義可以為預設值和自動更新值都指定目前的 timestamp，也可以只為其中一個指定，或者都不指定。不同的欄位可以有不同的自動屬性組合。下列規則描述了各種可能性

同時使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，欄位的預設值為目前的 timestamp，並且會自動更新為目前的 timestamp。
```
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```
使用 DEFAULT 子句但沒有 ON UPDATE CURRENT_TIMESTAMP 子句，欄位具有給定的預設值，且不會自動更新為目前的 timestamp。
預設值取決於 DEFAULT 子句是否指定 CURRENT_TIMESTAMP 或常數值。使用 CURRENT_TIMESTAMP，預設值為目前的 timestamp。
```
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
```
使用常數，預設值為給定的值。在這種情況下，該欄位完全沒有自動屬性。
```
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);
```
使用 ON UPDATE CURRENT_TIMESTAMP 子句和常數 DEFAULT 子句，欄位會自動更新為目前的 timestamp，並且具有給定的常數預設值。
```
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);
```
使用 ON UPDATE CURRENT_TIMESTAMP 子句但沒有 DEFAULT 子句，欄位會自動更新為目前的 timestamp，但其預設值不是目前的 timestamp。
在這種情況下，預設值取決於類型。TIMESTAMP 的預設值為 0，除非使用 NULL 屬性定義，在這種情況下預設值為 NULL。
```
CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
```
DATETIME 的預設值為 NULL，除非使用 NOT NULL 屬性定義，在這種情況下預設值為 0。
```
CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);
```

TIMESTAMP 和 DATETIME 欄位除非明確指定，否則沒有自動屬性，但此例外情況除外：如果停用了 explicit_defaults_for_timestamp 系統變數，則第一個 TIMESTAMP 欄位如果沒有明確指定，則同時具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP。若要抑制第一個 TIMESTAMP 欄位的自動屬性，請使用以下策略之一

啟用 explicit_defaults_for_timestamp 系統變數。在此情況下，指定自動初始化和更新的 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句可使用，但除非明確包含在欄位定義中，否則不會指派給任何 TIMESTAMP 欄位。
或者，如果 explicit_defaults_for_timestamp 已停用，請執行下列任一操作
- 使用指定常數預設值的 DEFAULT 子句定義欄位。
- 指定 NULL 屬性。這也會導致欄位允許 NULL 值，這表示您無法透過將欄位設定為 NULL 來指派目前的時間戳記。指派 NULL 會將欄位設定為 NULL，而不是目前的時間戳記。若要指派目前的時間戳記，請將欄位設定為 CURRENT_TIMESTAMP 或同義詞，例如 NOW()。

請考慮下列資料表定義

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
  ts1 TIMESTAMP NULL,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
  ts1 TIMESTAMP NULL DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);

資料表具有下列屬性

在每個資料表定義中，第一個 TIMESTAMP 欄位沒有自動初始化或更新。
資料表在 ts1 欄位處理 NULL 值的方式上有所不同。對於 t1，ts1 為 NOT NULL，並且將其值指派為 NULL 會將其設定為目前的時間戳記。對於 t2 和 t3，ts1 允許 NULL，並且將其值指派為 NULL 會將其設定為 NULL。
t2 和 t3 在 ts1 的預設值上有所不同。對於 t2，ts1 定義為允許 NULL，因此在沒有明確的 DEFAULT 子句的情況下，預設值也是 NULL。對於 t3，ts1 允許 NULL，但明確的預設值為 0。

如果 TIMESTAMP 或 DATETIME 欄位定義在任何地方包含明確的小數秒精確度值，則整個欄位定義中都必須使用相同的值。這是允許的

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

這是不允許的

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

時間戳記初始化和 NULL 屬性

如果 explicit_defaults_for_timestamp 系統變數已停用，TIMESTAMP 欄位的預設值為 NOT NULL，不能包含 NULL 值，並且指派 NULL 會指派目前的時間戳記。若要允許 TIMESTAMP 欄位包含 NULL，請使用 NULL 屬性明確宣告它。在此情況下，預設值也會變成 NULL，除非使用指定不同預設值的 DEFAULT 子句覆寫。 DEFAULT NULL 可用於明確指定 NULL 作為預設值。(對於未使用 NULL 屬性宣告的 TIMESTAMP 欄位，DEFAULT NULL 無效。) 如果 TIMESTAMP 欄位允許 NULL 值，則指派 NULL 會將其設定為 NULL，而不是目前的時間戳記。

下表包含數個允許 NULL 值的 TIMESTAMP 欄位

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

除非符合下列其中一個條件，否則允許 NULL 值的 TIMESTAMP 欄位在插入時不會採用目前的時間戳記

其預設值定義為 CURRENT_TIMESTAMP，且未指定欄位的值
CURRENT_TIMESTAMP 或其任何同義詞 (例如 NOW()) 已明確插入到欄位中

換句話說，只有在其定義包含 DEFAULT CURRENT_TIMESTAMP 時，定義為允許 NULL 值的 TIMESTAMP 欄位才會自動初始化

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果 TIMESTAMP 欄位允許 NULL 值，但其定義未包含 DEFAULT CURRENT_TIMESTAMP，您必須明確插入對應於目前日期和時間的值。假設資料表 t1 和 t2 具有下列定義

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

若要將任一資料表中的 TIMESTAMP 欄位設定為插入時的目前時間戳記，請明確將該值指派給它。例如

INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());

如果啟用 explicit_defaults_for_timestamp 系統變數，只有在使用 NULL 屬性宣告時，TIMESTAMP 欄位才允許 NULL 值。此外，無論使用 NULL 或 NOT NULL 屬性宣告，TIMESTAMP 欄位都不允許指派 NULL 來指派目前的時間戳記。若要指派目前的時間戳記，請將欄位設定為 CURRENT_TIMESTAMP 或同義詞，例如 NOW()。

上一步首頁上一層下一步