MySQL で日付を保存するためのベスト プラクティス ガイド

序文

日常の開発では、レコードの作成時刻や変更時刻など、時間を記録する必要がある場合がよくあります。データベースに時間を保存する方法はたくさんあります。たとえば、MySQL 自体は DATETIME、TIMESTAMEP などの日付型を提供しています。タイムスタンプを INT 型として直接保存することもできますし、時間を文字列型として直接保存する人もいます。

では、どの時間保存方法がより良いのでしょうか?

時間型を保存するのに文字列を使用しないでください

これは初心者がよくやる間違いです。フィールドを直接 VARCHAR 型に設定し、「2021-01-01 00:00:00」のような文字列を保存しがちです。もちろん、これを行う利点は、比較的シンプルで始めやすいことです。

ただし、次の 2 つの大きな問題があるため、これを実行することは強くお勧めしません。

• 文字列は多くのスペースを占めます
• この方法で保存されたフィールドの比較効率は低すぎます。文字単位でしか比較できず、MySQL が提供する日付 API は使用できません。

MySQL の日付型

MySQL データベースの一般的な日付タイプには、YEAR、DATE、TIME、DATETIME、TIMESTAMEP などがあります。通常、日付は秒単位の正確さが求められるため、より適しているのは DATETIME と TIMESTAMEP です。

日時

DATETIME は、YYYY-MM-DD HH:MM:SS の形式でデータベースに保存され、固定の 8 バイトを占有します。

MySQL バージョン 5.6 以降、DATETIME 型はミリ秒をサポートします。DATETIME(N) の N はミリ秒の精度を表します。たとえば、DATETIME(6) は 6 桁のミリ秒を格納できることを意味します。

タイムスタンプ

TIMESTAMP は実際には「1970-01-01 00:00:00」から現在までのミリ秒数を保存します。 MySQLでは、TIMESTAMP型は4バイトを占めるため、保存できる時刻の上限は「2038-01-19 03:14:07」のみです。

MySQL バージョン 5.6 以降では、TIMESTAMP 型でもミリ秒がサポートされるようになりました。 DATETIME とは異なり、TIMESTAMP はミリ秒が含まれる場合は 7 バイトを占有しますが、DATETIME はミリ秒が格納されているかどうかに関係なく 8 バイトを占有します。

TIMESTAMP 型の最大の利点は、基本的にミリ秒から変換されるため、タイムゾーン属性を持つことができることです。ビジネスでさまざまな国のタイムゾーンに対応する必要がある場合は、TIMESTAMP タイプが適しています。たとえば、ニュース サービスでは、ユーザーは通常、ニュースが公開されたときの自国の時刻を知りたいので、TIMESTAMP がオプションになります。タイムスタンプ型フィールドの値はサーバーのタイムゾーンに応じて変化し、対応する時間に自動的に変換されます。簡単に言えば、同じレコードが異なるタイムゾーンでクエリされると、このフィールドの値は異なります。

TIMESTAMP のパフォーマンスの問題

TIMESTAMP には潜在的なパフォーマンスの問題もあります。

ミリ秒から TIMESTAMP 型への変換自体は多くの CPU 命令を必要としませんが、これによって直接的なパフォーマンスの問題が発生することはありません。ただし、デフォルトのオペレーティング システムのタイム ゾーンを使用する場合は、タイム ゾーンを使用して時間を計算するたびに、基礎となるオペレーティング システム関数 __tz_convert() を呼び出す必要があり、この関数では、オペレーティング システムのタイム ゾーンが変更されていないことを確認するために追加のロック操作が必要になります。したがって、大規模な同時アクセスが発生すると、ホット リソースの競合により次の 2 つの問題が発生します。

• パフォーマンスは DATETIME ほど良くありません。DATETIME にはタイムゾーン変換の問題はありません。
• パフォーマンス ジッター: 同時実行が大量に発生すると、パフォーマンス ジッターの問題が発生します。

TIMESTAMP の使用を最適化するには、オペレーティング システムのタイム ゾーンではなく明示的なタイム ゾーンを使用することをお勧めします。たとえば、システムのタイムゾーンを使用する代わりに、構成ファイルでタイムゾーンを明示的に設定します。

[mysqld]

タイムゾーン = "+08:00"

これら 2 つのデータ タイプの利点と欠点を簡単にまとめてみましょう。

• DATETIME には保存できる時間に上限はありませんが、TIMESTAMP には '2038-01-19 03:14:07' までしか保存できません。
• DATETIME にはタイムゾーン属性がなく、フロントエンドまたはサーバーで処理する必要がありますが、データベースからのデータの保存と読み取りのみに関してはパフォーマンスが向上します。
• TIMESTAMP にはタイムゾーン属性がありますが、毎回タイムゾーンで時間を計算する必要があり、同時アクセス時にパフォーマンスの問題が発生する可能性があります。
• DATETIMEの保存はTIMESTAMEPよりも多くのスペースを消費します

数値タイムスタンプ (INT)

多くの場合、時間を表すために、int または bigint 型の値、つまりタイムスタンプも使用されます。

この保存方法は、Timestamp 型の利点をいくつか備えており、日付の並べ替えや比較に使用するとより効率的であり、数値のみを保存するためシステム間でも便利です。欠点も明らかで、データの可読性が悪すぎて、具体的な時間を直感的に把握することができません。

特定の期間内のデータを表示する必要がある場合

created_at > UNIX_TIMESTAMP('2021-01-01 00:00:00') の t から * を選択します。

DATETIME、TIMESTAMP、INT のうちどれを選択すべきでしょうか?

それぞれの方法には独自の利点があります。これら 3 つの方法を簡単に比較すると次のようになります。

TIMESTAMP と INT は本質的に同じですが、INT は開発者には使いやすいものの、DBA やデータ アナリストには使いにくく、読みやすさも劣ります。そのため、「High Performance MySQL」の著者がTIMESTAMPを推奨する理由は、その数値がより直感的に時間を表しているからです。原文は次のとおりです:

タイムゾーンの問題については、フロントエンドまたはサービスによって一度変換することができ、必ずしもデータベースで解決する必要はありません。

要約する

この記事では、時間を保存するために最も一般的に使用されるいくつかの方法を比較しますが、私のお気に入りは DATETIME です。理由は次のとおりです。

• TIMESTAMPは数値のタイムスタンプよりも読みやすい
• DATETIME の保存上限は 9999-12-31 23:59:59 です。TIMESTAMP を使用する場合は、2038 年に解決策を検討する必要があります。
• DATETIME はタイムゾーン変換を必要としないため、TIMESTAMP よりもパフォーマンスが優れています。
• 時間をミリ秒単位で保存する必要がある場合、TIMESTAMP には 7 バイトが必要ですが、これは DATETIME の 8 バイトとほとんど変わりません。

これで、MySQL の保存時間に関するこの記事は終了です。MySQL の保存時間に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。