MySQL の時間タイプとモードの詳細

mysql> Alarm_service の値に挿入します (6, '1970-01-01 08:00:00'); 
エラー 1292 (22007): 行 1 の列 'time' の日付時刻値が正しくありません: '1970-01-01 08:00:00'

# テーブル構造を表示します。mysql> show create table Alarm_service;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| テーブル | テーブルの作成 |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| アラームサービス | テーブル `アラームサービス` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP、
  主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 デフォルト CHARSET=utf8 |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

エラーメッセージには時間値が間違っていることが示されていることがわかりますが、これは明らかに有効な時間ポイントです。

データを検索した結果、 MySQLではtimestamp型の有効な間隔が1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC，保存時に、挿入したデータはまず UTC 時間に変換されてから保存され、読み取り時にローカル時間に変換されます。私のタイムゾーンは East 8 なので、変換すると1970-01-01 00:00:00 UTCとなり、これは不正な時間になります。

解決策は次のとおりです。

時間を法定範囲内に調整する
MySQLの厳密モードを調整して不正な時間を許可する

以下、関連する内容について詳しく説明します。

1. MySQL の時刻型

MySQL の時間型は 3 つのタイプに分けられます。

DATE:日付のみ含まれ、時刻が含まれていない場合、MySQL は形式をYYYY-MM-DDに変換します。有効な範囲は1000-01-01 - 9999-12-31です。
DATETIME:日付 + 時刻を格納する場合、形式はYYYY-MM-DD HH:MM:SSとなり、有効な範囲は1000-01-01 00:00:00 - 9999-12-31 23:59:59なります。
TIMESTAMP:日付 + 時刻を格納する場合、形式はYYYY-MM-DD HH:MM:SSとなり、有効な範囲は1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTCとなります。

同時に、 DATETIMEとTIMESTAMPも 6 桁のマイクロ秒データのサポートをサポートしており、形式はYYYY-MM-DD HH:MM:SS[.fraction] 、有効な範囲は.000000 - .999999です。

DATETIMEとTIMESTAMPどちらも、自動的に初期化され、現在の日付と時刻に更新されるデータも提供します。

TIMESTAMP型の場合、MySQL はデータ値を保存するときに UTC 標準時間に変換し、読み取るときに現在の時刻に変換します。タイムゾーンが変更されていない場合、値は保存した値のままです。タイムゾーンを変更すると、読み取る値も変更されます。この機能はDATETIMEでは動作しません。

2. タイムゾーンを確認する

mysql> '%zone%' のような変数を表示します。                                       
+------------------+--------+
| 変数名 | 値 |
+------------------+--------+
| システムタイムゾーン | CST |
| タイムゾーン | システム |
+------------------+--------+

現在のタイムゾーンは SYSTEM であり、これはオペレーティングシステムと一致していることがわかります。システムのタイムゾーンは CST (中国標準時) です。また、システム時刻は East 8 (+0800) であることもわかります。

$ 日付 -R
2019年4月23日（火）11:22:47 +0800

したがって、 1970-01-01 08:00:00入力すると、 MySQLそれを1970-01-01 00:00:00，これは不正な値になります。

3. 不正な時間値

不正な時刻値の場合、MySQL はそれをさまざまな時刻タイプに適した値（ 0000-00-00または0000-00-00 00:00:00に変換します。

たとえば、月が 1 月から 12 月の場合、 2019-13-01 00:00:00を挿入しようとすると、13 番目の月が存在しないため、不正な値である0000-00-00 00:00:00に修正されます。

4. 厳密モード

不正な時間値を挿入すると修正されますが、厳密モードではデータは挿入されず、代わりにエラーが報告されます。

エラー 1292 (22007): 行 1 の列 'time' の日付時刻値が正しくありません: '1970-01-01 08:00:00'

モードを設定することでMySQLの動作を調整できます。まず、MySQL モードを確認します。

mysql> '%sql_mode%' のような変数を表示します。            
+----------------------------+------------------------------------------+
| 変数名 | 値 |
+----------------------------+------------------------------------------+ |
| sql_mode | STRICT_TRANS_TABLES、NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------------------------+

このモードでは、不正な日付はエラーとして報告されます。モードをALLOW_INVALID_DATESに調整できます。

mysql> セッション sql_mode を 'ALLOW_INVALID_DATES' に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> '%sql_mode%' のような変数を表示します。            
+---------------+---------------------+
| 変数名 | 値 |
+---------------+---------------------+
| sql_mode | ALLOW_INVALID_DATES |
+---------------+---------------------+
セット内の 1 行 (0.00 秒)

このモードでは、日付の有効性は完全にはチェックされず、月が 1 ～ 12 の範囲内であることと、日が 1 ～ 31 の範囲内であることのみがチェックされます。これはユーザー入力を処理する場合に適していますが、このモードはDATEとDATETIMEにのみ適しています。 TIMESTAMPの場合、有効な値が必要であり、そうでない場合は 0000-00-00 00:00:00 に修正されます。

不正な値の場合、このモードが有効になっているとエラーが報告されます。無効になっている場合は、 0000-00-00 00:00:00に修正され、警告が生成されます。

mysql> Alarm_service の値に挿入します (7, '1970-01-01 08:00:00'); 
クエリは正常、1 行が影響を受け、1 つの警告 (0.00 秒)

要約:

この問題には 2 つの解決策があります。

時間を法定範囲内に調整する
MySQLの厳密モードを調整して不正な時間を許可する

5. 事件概要

ERROR 1067 (42000): Invalid default value for 'createTime'

理由を確認し、次のように設定されていることを確認します。

# テーブルを作成するためのステートメントを表示します CREATE TABLE `dimensionsConf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createTime` 日時 DEFAULT CURRENT_TIMESTAMP、
) ENGINE=InnoDB AUTO_INCREMENT=178 デフォルト CHARSET=utf8;

# データベースのバージョンを表示する $mysql --version
mysql Ver 14.14 Distrib 5.1.30、EditLine ラッパーを使用した unknown-linux-gnu (x86_64) 用

これで、MySQL の時間タイプとモードに関するこの記事は終了です。MySQL の時間タイプとモードの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: