MySQL の時間設定に関する考慮事項の詳細な要約

時間は本当に存在するのでしょうか？時間は人間が考え出した概念に過ぎず、物事の変化を測る基準に過ぎないと考える人もいます。データベースの場合、時間はデータと密接に関係しています。 MySQL の時間の渦に突入します。

1. 時間型フィールド

MySQL 時間型フィールド:

見落としがちなのは以下の点です:

TIMESTAMP は次のようにデータを保存します。

MySQL は、TIMESTAMP 値を現在のタイムゾーンから UTC に変換して保存し、UTC から現在のタイムゾーンに変換して取得します。
(これは、DATETIME などの他のタイプには適用されません) デフォルトでは、各接続の現在のタイムゾーンはサーバーの時間になります。タイムゾーンは接続ごとに設定できます。タイムゾーン設定が同じである限り、保存されている同じ値が返されます。タイムスタンプ値を保存し、タイムゾーンを変更して値を取得すると、取得された値は保存された値と異なります。これは、両方向で同じタイムゾーンを使用して変換が行われていないために発生します。現在のタイムゾーンは、time_zone システム変数の値として利用できます。

TIMESTAMP と SQL_MODE の組み合わせ

sql_mode はタイムスタンプ値にも影響します。

mysql> テーブル ts を作成します (
         id INTEGER NOT NULL AUTO_INCREMENT 主キー、
         列 TIMESTAMP が NULL でない
     ) AUTO_INCREMENT = 1;

mysql> '%sql_mode%' のような変数を表示します。
+---------------+---------------------+
| 変数名 | 値 |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
mysql> INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10');
エラー 1292 (22007): 行 1 の列 'col' の日付時刻値が正しくありません: '1969-01-01 01:01:10'

mysql> sql_mode を ="" に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> '%sql_mode%' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| SQL モード | |
+---------------+-------+
mysql> INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10'),('2999-01-01 01:01:10');
クエリは正常、2 行が影響を受け、2 つの警告 (0.01 秒)
記録: 2 重複: 0 警告: 2
mysql> 警告を表示します。
+---------+------+----------------------------------------------+
| レベル | コード | メッセージ |
+---------+------+----------------------------------------------+
| 警告 | 1264 | 行 1 の列 'col' の値が範囲外です |
| 警告 | 1264 | 行 2 の列 'col' の値が範囲外です |
+---------+------+----------------------------------------------+

mysql> TS から * を選択;
+----+---------------------+
| id | 列 |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
+----+---------------------+
セット内の 2 行 (0.00 秒)

sql_modeを制御することで、タイムスタンプ制限を超えた値は挿入されるものの、空白を0で埋める方式が採用されています。
STRICT_TRANS_TABLES の場合、MySQL は無効な値を最も近い有効な値に変換し、調整された値を挿入します。値が欠落している場合、MySQL は列のデータ型の暗黙的なデフォルト値を挿入します。

2.explicit_defaults_for_timestamp 時間処理メカニズム

デフォルトでは有効になっています。

MySQL 8.0.22 以降では、TIMESTAMP NOT NULL として宣言された列に NULL を挿入しようとすると、エラーが発生して拒否されます。

1.explicit_defaults_for_timestampが無効になっている場合:

NULL 属性で明示的に宣言されていないタイムスタンプ列は、NOT NULL 属性で自動的に宣言されます。このような列に NULL を割り当てることは許可されており、列は現在のタイムスタンプに設定されます。 MySQL 8.0.22 以降では、TIMESTAMP NOT NULL として宣言された列に NULL を挿入しようとすると、エラーが発生して拒否されます。
テーブルの最初の列が NULL 属性または明示的な DEFAULT または ON UPDATE 属性で宣言されていない場合は、デフォルトの CURRENT_TIMESTAMP 属性と ON UPDATE CURRENT_TIMESTAMP 属性で自動的に宣言されます。
NULL 属性または明示的なデフォルト属性で明示的に宣言されていない場合、TIMESTAMP は、デフォルト値「0000-00-00 00:00:00」(「ゼロ」タイムスタンプ) で自動的に宣言されます。
厳密な SQL モードまたは NO_ZERO_DATE SQL モードが有効になっているかどうかに応じて、デフォルト値「0000-00-00 00:00:00」が有効にならない場合があります。

2.explicit_defaults_for_timestampが有効になっている:

TIMESTAMP に NULL 値を指定して現在のタイムスタンプに設定することはできません。現在のタイムスタンプを指定するには、CURRENT_TIMESTAMP または NOW() などの同義語を設定します。
明示的に NOT NULL 属性で宣言されていない TIMESTAMP 列は、自動的に NULL 属性で宣言され、NULL 値が許可されます。
NOT NULL 属性で宣言されたタイムスタンプ列では、NULL 値は許可されません。このような列に NULL を指定する挿入では、厳密な SQL モードが有効になっている場合は単一行挿入に対してエラーが発生し、厳密な SQL モードが無効になっている場合は複数行挿入に対して「0000-00-00 00:00:00」が発生します。いずれの場合でも、列に NULL 値を割り当てても、現在のタイムスタンプは設定されません。
NOT NULL 属性で明示的に宣言され、明示的なデフォルト属性を持たないタイムスタンプ列は、デフォルト値を持たないものとして扱われます。このような列に明示的な値を指定しない挿入行の場合、結果は SQL モードによって異なります。厳密な SQL モードが有効になっている場合は、エラーが発生します。厳密な SQL モードが有効になっていない場合、列はデフォルトの暗黙的な値「0000-00-00 00:00:00」で宣言され、警告が表示されます。
タイムスタンプタイプの列は、デフォルトの CURRENT_TIMESTAMP 属性または更新された CURRENT_TIMESTAMP 属性を使用して自動的に宣言されません。これらのプロパティは明示的に指定する必要があります。

テスト：

テーブル `test1` を作成します (
id bigint not null AUTO_INCREMENT COMMENT '主キーID', 
name varchar(20) COMMENT '主キーID',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'cr time',
主キー(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 ;
'explicit_defaults_for_timestamp' のような変数を表示します。
グローバルexplicit_defaults_for_timestampをONに設定します。
グローバルexplicit_defaults_for_timestampをOFFに設定します。
test1(id,name,create_time) に挿入 VALUES(1,'Kit',NULL);

3.mysqlシステム構成

システム関連のイベントパラメータには次の 3 つが含まれます。

mysql>Variable_name like '%time_zone%' または Variable_name like 'log_timestamp%' のグローバル変数を表示します。
+------------------+--------+
| 変数名 | 値 |
+------------------+--------+
| システムタイムゾーン | CST |
| タイムゾーン | システム |
| ログタイムスタンプ | UTC |
+------------------+--------+
セット内の 3 行 (0.00 秒)

1. システムタイムゾーン: サーバーが起動すると、ホストのタイムゾーンを自動的に判別し、それを使用して system_time_zone システム変数を設定します。それ以降は値は変わりません。

2.time_zone: 完全な time_zone は、サーバーが現在実行されているタイムゾーンを示します。初期の time_zone 値は「SYSTEM」です。これは、サーバーのタイムゾーンがシステムのタイムゾーンと同じであることを意味します。

SYSTEM に設定すると、MySQL 関数呼び出しはシステムライブラリを呼び出して現在のシステムタイムゾーンを決定します。この呼び出しはグローバルミューテックスによって保護されている可能性があり、競合が発生します。 CPU 使用率が高い問題。
セッションのタイムゾーンを設定すると、タイムゾーンに依存する時間値の表示と保存に影響します。これには、NOW() や CURTIME() などの関数によって表示される値だけでなく、タイムスタンプ列に格納され、そこから取得される値も含まれます。タイムスタンプ列の値は、保存時にはセッションタイムゾーンから UTC に変換され、取得時には UTC からセッションタイムゾーンに変換されます。
セッションのタイムゾーン設定は、UTC_TIMESTAMP() などの関数によって表示される値や、DATE、時刻、DATETIME 列の値には影響しません。これらのデータ型の値も UTC では保存されません。タイムゾーンは、タイムスタンプ値から変換する場合にのみ適用されます。

注: MySQL では、タイムゾーンを MySQL システムライブラリにインポートする方法も提供されています。 mysql_tzinfo_to_sql プログラムを使用して、/usr/share/zoneinfom にタイムゾーン情報を読み込みます。

mysql> mysql.time_zone_nameからCOUNT(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 0 |
+----------+

##タイムゾーン値をインポートするためのmysql_tzinfo_to_sqlツール

シェル>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql> mysql.time_zone_nameからCOUNT(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 1780 |
+----------+

3. ログタイムスタンプ

この変数は、エラーログに書き込まれるメッセージ、およびファイルに書き込まれる一般クエリログとスロークエリログメッセージのタイムスタンプのタイムゾーンを制御します。

一般クエリログテーブルとスロークエリログテーブル (mysql.general_log と mysql.slow_log) が書き込まれるタイムゾーンには影響しません。

許可される log_timestamps 値は UTC (デフォルト) と SYSTEM (ローカルシステムタイムゾーン) です。

注: UTC は通常、協定世界時を指します。協定世界時（世界統一時刻、世界標準時刻、国際協定時刻とも呼ばれる）は、UTC + 8時間 = 中国時間です。

もちろん、より適切に管理するには、この値はシステムの記録時間と一致する必要があります。

#タイムゾーンを設定し、東 8 番目のゾーンに変更します SET GLOBAL time_zone = '+8:00';

提案：

mysql 設定ファイル my.cnf

[mysqld]
log_timestamps=システム
デフォルトのタイムゾーン = '+8:00'

mysql>Variable_name like '%time_zone%' または Variable_name like 'log_timestamp%' のグローバル変数を表示します。
+------------------+--------+
| 変数名 | 値 |
+------------------+--------+
| ログタイムスタンプ | システム |
| システムタイムゾーン | CST |
| タイムゾーン | +08:00 |
+------------------+--------+