MySQL におけるデータタイムとタイムスタンプの違い

MySQL には 3 つの日付型があります。

日付(年-月-日)

テーブル test(hiredate date) を作成します。

datetime (datetime 型)

テーブル test(hiredate datetime) を作成します。

タイムスタンプ（消印タイプ、年月日時分秒を保存）

テーブル test(hiredate タイムスタンプ) を作成します

日付時刻とタイムスタンプの違い

1. 両者の保管方法は異なる

TIMESTAMP の場合、クライアントによって挿入された時間を現在のタイムゾーンから UTC (協定世界時) に変換して保存します。クエリを実行すると、クライアントの現在のタイムゾーンに変換されて返されます。 DATETIME の場合は変更はなく、基本的にそのまま入力・出力されます。

2. 両者で保存できる時間範囲が異なる

タイムスタンプが保存できる時間の範囲は、「1970-01-01 00:00:01.000000」から「2038-01-19 03:14:07.999999」です。

datetime が保存できる時間の範囲は、「1000-01-01 00:00:00.000000」から「9999-12-31 23:59:59.999999」です。

要約: TIMESTAMP と DATETIME は、保存範囲と保存方法を除いて大きな違いはありません。もちろん、タイムゾーンをまたぐビジネスには TIMESTAMP の方が適しています。

3. 自動初期化と更新

自動初期化とは、フィールドに明示的な割り当てがない場合 (上記の例の hiredate フィールドなど)、現在のシステム時刻に自動的に設定されることを意味します。

自動更新とは、他のフィールドが変更された場合、このフィールドの値が現在のシステム時間に自動的に更新されることを意味します。

これは、「explicit_defaults_for_timestamp」パラメータに関連しています。

デフォルトでは、このパラメータの値はOFFです。

mysql> '%explicit_defaults_for_timestamp%' のような変数を表示します。
+---------------------------------+-------+
| 変数名 | 値 |
+---------------------------------+-------+
| タイムスタンプの明示的なデフォルト | オフ |
+---------------------------------+-------+
セット内の行数 (0.00 秒)

要約: timestamp は自動的に初期化され、更新されます。レコードを更新すると、列の値も自動的に更新されます。これが datatime との最大の違いです。

補足: DATETIME と TIMESTAMP の違いと問題点

今日、問題が発生しました。データは更新されましたが、update_time フィールドはデータが作成された時点のままでした。

通常、このフィールドは自動的に更新されます。

テーブル構造を確認しました。

`update_time` 日時 NOT NULL DEFAULT CURRENT_TIMESTAMP

update_timeフィールドの型がdatetimeであることが判明しました

これによって2つの疑問が生じます: (1) タイムスタンプと日付時刻の違い (2) なぜCURRENT_TIMESTAMPが日付時刻型に使用できるのか

タイムスタンプと日付時刻の違い

a) DATETIME のデフォルト値は null です。TIMESTAMP フィールドのデフォルト値は null 以外 (not null) であり、デフォルト値は現在時刻 (CURRENT_TIMESTAMP) です。特別な処理が行われず、更新ステートメントで列の更新値が指定されていない場合、デフォルトの更新は現在時刻です。

この違いは、通常はタイムスタンプを使用するため、このフィールドを自動的に更新するように管理する必要がない理由を説明しています。ここでは datetime を使用しており、現在の時刻を自動的に更新するメカニズムがないため、上位層でこのフィールドを手動で更新する必要があります。

b) DATETIME は 8 バイトのストレージ スペースを使用し、TIMESTAMP は 4 バイトのストレージ スペースを使用します。したがって、TIMESTAMP は DATETIME よりもスペース効率が高くなります。

この違いにより、タイムスタンプ タイプがより頻繁に使用される理由が説明されます。

c)両者の保存方法は異なります。TIMESTAMP の場合、クライアントが挿入した時刻を現在のタイムゾーンから UTC (協定世界時) に変換して保存します。クエリを実行すると、クライアントの現在のタイムゾーンに変換されて返されます。 DATETIME の場合は変更はなく、基本的にそのまま入力・出力されます。

d)保存できる時間範囲が異なる

タイムスタンプが保存できる時間の範囲は、「1970-01-01 00:00:01.000000」から「2038-01-19 03:14:07.999999」です。

datetime が保存できる時間の範囲は、「1000-01-01 00:00:00.000000」から「9999-12-31 23:59:59.999999」です。

CURRENT_TIMESTAMP を datetime 型に使用できるのはなぜですか?

MySQL 5.6 より前のバージョンでは、CURRENT_TIMESTAMP はタイムスタンプ型にのみ使用できます。

バージョン5.6以降では、CURRENT_TIMESTAMPもdatetime型に使用できます。

select version() でデータベースをチェックしたところ、バージョンは 5.6.29 であることがわかりました。

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。間違いや不備な点がありましたら、遠慮なくご指摘ください。