MySQLのタイムスタンプ2038問題を解決する方法
- WBOY転載
- 2023-06-02 10:13:272104ブラウズ
タイムスタンプは、グリニッジ時間の 1970 年 1 月 1 日の 00:00:00 (北京時間の 1970 年 1 月 1 日の 08:00:00) から現在までの合計秒数を指します。
MySQL のさまざまなバージョンが運用環境にデプロイされており、MySQL 5.5/5.6/5.7 の 3 つのメジャー バージョンと N 個のマイナー バージョンが含まれます。MySQL の上位互換性が低いため、同じ SQL でもバージョンが異なると動作が異なります。タイムスタンプデータ型については、以下のいくつかの側面から詳しく紹介します。
タイムスタンプ データ アクセス
MySQL の上記の 3 つのメジャー バージョンでは、デフォルトのタイムスタンプ (Timestamp) タイプの値の範囲は ’1970-01-01 00:00:01&rsquo です。 UTC から「2038-01-19 03:14:07」UTC まで、データは第 2 レベルまで正確です。この値範囲には約 22 億の値が含まれるため、MySQL では 4 バイトの INT 型が内部的に使用され、データ:
1. タイムスタンプ データを保存するときは、まずローカル タイム ゾーン時刻を UTC タイム ゾーン時刻に変換し、次に UTC タイム ゾーン時刻を INT 形式のミリ秒値に変換し (UNIX_TIMESTAMP 関数を使用)、それをデータベースに保存します。
2. タイムスタンプ データを読み取るときは、まず INT 形式のミリ秒値を UTC タイム ゾーン時間に変換し (FROM_UNIXTIME 関数を使用)、次にローカル タイム ゾーン時間に変換して、最後にクライアントに返します。
MySQL 5.6.4 以降のバージョンでは、タイムスタンプ型データを最高精度のマイクロ秒 (100 万分の 1 秒) に変換できます。データ型は timestamp(N) として定義され、値の範囲はN は 0 ～ 6 です。デフォルトは 0 です。ミリ秒まで正確にする必要がある場合は、Timestamp(3) に設定します。マイクロ秒まで正確にする必要がある場合は、timestamp(6) に設定します。データ改善のコスト精度は内部ストレージ領域の増加ですが、まだ増加していません。タイムスタンプ タイプの最小値と最大値の範囲を変更します。
タイムスタンプ フィールドの定義
タイムスタンプ フィールドの定義は、主に 2 種類の操作に影響します。
レコードを挿入するとき、タイムスタンプ フィールドには DEFAULT CURRENT_TIMESTAMP が含まれます。など。レコードの挿入時に特定の時刻データが指定されていない場合、タイムスタンプ フィールドの値は現在時刻に設定されます。
レコードが更新されると、タイムスタンプ フィールドには ON UPDATE が含まれます。 CURRENT_TIMESTAMP: レコードの更新時に特定の時刻が指定されていない場合、時刻データの場合、タイムスタンプ フィールドの値を現在時刻に設定します
PS1: CURRENT_TIMESTAMP は、CURRENT_TIMESTAMP() 関数を使用することを意味しますNOW() 関数と同様に、現在時刻を取得します
上記の 2 種類の操作に従って、タイムスタンプ列には 4 つの組み合わせ定義を含めることができます。その意味は次のとおりです。
-
フィールドがタイムスタンプとして定義されている場合、挿入または更新時にフィールドは挿入または更新されず、自動的に現在の時刻に設定されます。
フィールドがタイムスタンプ DEFAULT CURRENT_TIMESTAMP として定義されている場合、値が指定されていない挿入時にのみフィールドに現在時刻が割り当てられ、更新時に変更されないことを意味します。値は指定されていません。
フィールドがタイムスタンプ ON UPDATE CURRENT_TIMESTAMP として定義されている場合、フィールドには挿入時に値「0000-00-00 00:00:00」が割り当てられ、割り当てられないことを意味します。値を指定し、更新時に値を指定しない場合は現在時刻に更新されます。
フィールドがタイムスタンプ DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP として定義されている場合、挿入または更新時にフィールドに値が指定されず、現在の時刻が割り当てられることを意味します。
PS1: MySQL で実行されるテーブル作成ステートメントと最終的なテーブル作成ステートメントには違いがあるため、SHOW CREATE TABLE TB_XXX を使用してテーブル作成ステートメントを取得することをお勧めします。作成されたテーブル。
MySQL のさまざまなバージョンでのタイムスタンプ フィールドの使用の違い
MySQL 5.5 以前のバージョンでは、DEFUALT CURRENT_TIMESTAMP または ON UPDATE CURRENT_TIMESTAMP として定義できるタイムスタンプ フィールドは 1 つだけですただし、この制限は MySQL 5.6 および MySQL 5.7 バージョンでは削除されました。
MySQL 5.6 バージョンでは、パラメータ Explicit_defaults_for_timestamp のデフォルト値は 1 で、MySQL 5.7 バージョンでは、パラメータ明示的デフォルト値_for_timestamp のデフォルト値は 0 です。
MySQL 5.5 および MySQL 5.7 バージョンでは、タイムスタンプ タイプのデフォルトは NOT NULL に設定され、MySQL 5.6 バージョンでは、タイムスタンプ タイプのデフォルトは NULL に設定されます。 ;
テーブル作成ステートメントが c1 タイムスタンプに設定されている場合、
- # は c1 タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;# と同等です。 MySQL 5.5 の ##MySQL 5.6 の c1 タイムスタンプ NULL DEFAULT NULL と同等;
- MySQL の c1 タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP と同等5.7
- c1 タイムスタンプのデフォルト 0 がテーブル作成ステートメントで使用されている場合、
- は MySQL 5.5 の c1 タイムスタンプ NOT NULL DEFAULT と同等です。 ;0000-00-00 00:00: 00’;
- は、MySQL 5.6 では c1 タイムスタンプ NULL DEFAULT ‘0000-00-00 00:00:00’# と同等です。
- # #MySQL 5.7 の c1 タイムスタンプ NOT NULL DEFAULT と同等 ‘0000-00-00 00:00:00’
##PS1: メインMySQL 5.6 と MySQL 5.7 の違い パラメータexplicit_defaults_for_timestampのデフォルト値の影響を受けます。
PS2: タイムスタンプ列のデフォルト値が「0000-00-00 00:00:00」の場合、「タイムスタンプ値の範囲内にありません」というデフォルト値を使用しても警告は生成されません。 。
タイムスタンプ タイプによって発生する例外
MySQL パラメータ time_zone=system の場合、タイムスタンプ フィールドをクエリすると、タイム ゾーン変換のためにシステム タイム ゾーンが呼び出されます。ただし、システム タイム ゾーンのグローバル ロックの問題により、 、複数の同時大容量データ アクセス これにより、スレッド コンテキストが頻繁に切り替わり、CPU 使用率が急増し、システムの応答が遅くなり、アニメーションの一時停止が発生します。
タイムスタンプ タイプと時刻タイプの選択
一部の「データベース ガイダンス」ドキュメントでは、日時フィールドの代わりにタイムスタンプ タイプを使用することが推奨されています。その理由は、タイムスタンプ タイプでは 4 が使用されるためです。バイト、日時フィールドは 8 バイトを使用しますが、ディスク パフォーマンスの向上とメモリ コストの削減により、実際の運用環境では、タイムスタンプ タイプを使用してもパフォーマンスの向上はあまり期待できません。タイムスタンプ タイプの定義と値の範囲により、ビジネスに使用されます。
MySQL 5.6.4 以降のバージョンでは、タイムスタンプ型 (timestamp) のデータは最高精度のマイクロ秒に変換でき、時間型 (datetime) のデータも最高精度のマイクロ秒に変換できます。時刻型 (datetime) についても同様で、たとえば、フィールドを dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); として定義すると、タイムスタンプ型と同じ効果が得られます。時間タイプ (日時) は ’1000-01-01 00:00:00.000000’ から ‘9999-12-31 23:59:59.999999’ であり、各期間のデータをより適切に保存できます。
タイム スタンプ タイプの使用に関する提案
データの最終更新時刻のみを考慮する場合は、タイムスタンプ列を TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;## として定義することをお勧めします。
# 作成時間と更新時間を重視する場合は、更新時間をタイムスタンプ フィールドとして設定し、作成時間を DAETIME または TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’ として定義することをお勧めします。作成時刻を指定する式;テーブルにはタイムスタンプ列を 1 つだけ定義し、DEFAULT 属性と ON UPDATE 属性を明示的に定義することをお勧めします。タイムスタンプ フィールドは MySQL で割り当てたり更新したりできますが、必要な場合にのみタイムスタンプ カラムを明示的に挿入および更新することをお勧めします。time_zone パラメータをシステム外部の値に設定することをお勧めします。 、中国地域サーバーを「8:00」に設定するなど; MySQL オフライン テスト バージョンとオンライン実稼働バージョンの一貫性を保つことをお勧めします。 タイムスタンプと日付時刻の類似点と相違点類似点:- は自動的に更新および初期化でき、デフォルトの表示形式は同じ YYYY です-MM-dd HH:mm :ss ##違い:
- タイムスタンプの時間範囲は次のとおりです: ‘1970-01-01 00: 00:01’ UTC から ‘2038-01-19 03:14:07’ UTC、自動タイムゾーン変換、実際のミリ秒が保存、4 バイトが保存
- 日時範囲: &lsquo ;1000-01 -01 00:00:00’ から ‘9999-12-31 23:59:59’、タイム ゾーン、8 バイト ストレージをサポートしていません
- Setタイムスタンプと日付の自動更新 Time
データの一部が更新されるか、日付と mydate に値を割り当てずに新しいデータが挿入されると、2 つのフィールドの date と mydate は自動的に現在の値にデフォルト設定されます。 time
CREATE TABLE `mytime` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `mydate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
2038Question
タイムスタンプに保存された時間が「2038-01-19 03:14:07」UTC より大きい場合、mysql はエラーを報告します。これは、これが問題であるためです。 mysql自体、つまりタイムスタンプには上限があり、それを超えた場合は当然エラーが報告されます。いくつかのスクリーンショットは次のとおりです。
解決策
タイムスタンプ 上限はありますが、タイムスタンプが保存されるため、タイムスタンプを考慮する必要はありません。タイムゾーンの問題。タイムゾーンに関連するニーズに対処する必要がある場合。2038 の制限を解決する場合は、タイムスタンプを整数型に変更してタイムスタンプを保存し、プログラム内で変換することをお勧めします (この解決策はまだ実装されていません。これは単なる提案です。使用には注意してください!!)
タイムゾーンの問題を考慮する必要がない場合は、timestamp を datatime タイプに置き換えるだけです。これは、datatime の値の範囲がはるかに大きいためです。上の図を参照してください;
置換のアイデア:
1. 元のフィールドの名前を変更します;
ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';
datatime タイプの新しいフィールドを作成します (新しいフィールドを作成します)列を削除して元の列を置き換えます);
ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';
元のフィールド列のデータを新しいフィールド列にコピーします;
UPDATE `student` SET `entry_date` = `temp_entry_date`;
元の列を削除します;
ALTER TABLE `student` DROP `temp_entry_date`;
完了SQLは次のとおりです: (元のタイムスタンプのデフォルト値も追加する必要があることに注意してください)
ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00'; ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00'; UPDATE `student` SET `entry_date` = `temp_entry_date`; ALTER TABLE `student` DROP `temp_entry_date`;
以上がMySQLのタイムスタンプ2038問題を解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。