MySQL における datetime と timestamp の違いと使い方

1. MySQL で現在の時刻を表現するにはどうすればよいでしょうか?

実際、表現方法はいろいろありますが、まとめると次のようになります。

現在のタイムスタンプ

CURRENT_TIMESTAMP()

今（）

現地時間

ローカル時間()

ローカルタイムスタンプ

ローカルタイムスタンプ()

2. TIMESTAMPとDATETIMEの比較

完全な日付形式は、YYYY-MM-DD HH:MM:SS[.fraction] です。これは、日付部分と時刻部分の 2 つの部分に分けられます。日付部分は「YYYY-MM-DD」形式に対応し、時刻部分は「HH:MM:SS[.fraction]」形式に対応します。日付フィールドでは、日付部分のみがサポートされます。時刻部分が挿入されると、その部分の内容が破棄され、警告が表示されます。

以下のように表示されます。

mysql> テーブル test(id int, hiredate date) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> テスト値に挿入(1,'20151208000000');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> テスト値に挿入(1,'20151208104400');
クエリは正常、1 行が影響を受け、1 つの警告 (0.01 秒)

mysql> 警告を表示します。
エラー 1064 (42000): SQL 構文にエラーがあります。1 行目の「警告」付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。
mysql> テストから * を選択します。
+------+-------------+
| ID | 雇用日 |
+------+-------------+
| 1 | 2015-12-08 |
| 1 | 2015-12-08 |
+------+-------------+
セット内の 2 行 (0.00 秒)

注: 最初のものは時間部分が0なので警告は出ません。

TIMESTAMP と DATETIME の類似点:

1> どちらも、YYYY-MM-DD HH:MM:SS[.fraction] 型の日付を表すために使用できます。

TIMESTAMP と DATETIME の違いは次のとおりです。

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

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

DATETIME の場合は変更はなく、基本的にそのまま入力・出力されます。

次に検証してみましょう

まず、タイムスタンプ形式を使用するテストテーブルと日時形式を使用するテストテーブルを 2 つ作成します。

mysql> テーブル test(id int, hiredate timestamp) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> テスト値に挿入(1,'20151208000000');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> テーブル test1(id int, hiredate datetime) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> test1 に値 (1,'20151208000000') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> テストから * を選択します。
+------+---------------------+
| ID | 雇用日 |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
セット内の1行（0.01秒）

mysql> test1 から * を選択します。
+------+---------------------+
| ID | 雇用日 |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
セット内の 1 行 (0.00 秒)

両方の出力は同じです。

次に、現在のセッションのタイムゾーンを変更します

mysql> '%time_zone%' のような変数を表示します。 
+------------------+--------+
| 変数名 | 値 |
+------------------+--------+
| システムタイムゾーン | CST |
| タイムゾーン | システム |
+------------------+--------+
セット内の 2 行 (0.00 秒)

mysql> time_zone='+0:00' を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> テストから * を選択します。
+------+---------------------+
| ID | 雇用日 |
+------+---------------------+
| 1 | 2015-12-07 16:00:00 |
+------+---------------------+
セット内の 1 行 (0.00 秒)

mysql> test1 から * を選択します。
+------+---------------------+
| ID | 雇用日 |
+------+---------------------+
| 1 | 2015-12-08 00:00:00 |
+------+---------------------+
セット内の1行（0.01秒）

上記の「CST」は、MySQLが配置されているホストのシステム時間を指し、これは中国標準時、中国標準時UT+8:00の略語です。

結果は、test で返された時刻が 8 時間早いのに対し、test1 の時刻は変更されていないことを示しています。これにより、両者の違いが完全に検証されます。

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. TIMESTAMPとDATETIMEの自動初期化と更新

まず、次の操作を見てみましょう

mysql> テーブル test(id int, hiredate timestamp) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> test(id) に値(1) を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> テストから * を選択します。
+------+---------------------+
| ID | 雇用日 |
+------+---------------------+
| 1 | 2015-12-08 14:34:46 |
+------+---------------------+
セット内の 1 行 (0.00 秒)

mysql> show テーブル作成 test\G
************************** 1. 行 ****************************
    表: テスト
テーブルの作成: CREATE TABLE `test` (
 `id` int(11) デフォルト NULL,
 `hiredate` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) エンジン=InnoDB デフォルト文字セット=latin1
セット内の 1 行 (0.00 秒)

少し奇妙に見えますか? hiredate フィールドを挿入しなかったため、その値は現在の値に自動的に変更されました。さらに、テーブルを作成するときに、「show create table test\G」の結果に表示される「DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP」を定義しませんでした。

実際、この機能は自動初期化と更新です。

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

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

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

デフォルトでは、このパラメータの値は以下のように OFF になっています。

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

以下の公式の説明を見てみましょう。

デフォルトでは、どちらも明示的に指定されていない場合、最初の TIMESTAMP 列には DEFAULT CURRENT_TIMESTAMP と ON UPDATE CURRENT_TIMESTAMP の両方が含まれます。

多くの場合、これは私たちが望んでいることではありません。これを無効にするにはどうすればよいでしょうか?

1. 「explicit_defaults_for_timestamp」の値をONに設定します。

2. 「explicit_defaults_for_timestamp」の値がまだオフになっています。これを無効にする方法は 2 つあります。

1> DEFAULT句を使用して列のデフォルト値を指定します

2> 列に NULL 属性を指定します。

以下のように表示されます。

mysql> テーブル test1(id int、hiredate timestamp null) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> テーブル test1\G の作成を表示します
************************** 1. 行 ****************************
    表: test1
テーブルの作成: CREATE TABLE `test1` (
 `id` int(11) デフォルト NULL,
 `hiredate` タイムスタンプ NULL デフォルト NULL
) エンジン=InnoDB デフォルト文字セット=latin1
セット内の 1 行 (0.00 秒)

mysql> テーブル test2(id int、hiredate タイムスタンプ デフォルト 0) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> テーブル test2\G の作成を表示します
************************** 1. 行 ****************************
    表: test2
テーブルの作成: CREATE TABLE `test2` (
 `id` int(11) デフォルト NULL,
 `hiredate` タイムスタンプ NOT NULL デフォルト '0000-00-00 00:00:00'
) エンジン=InnoDB デフォルト文字セット=latin1
セット内の 1 行 (0.00 秒)

MySQL 5.6.5 より前では、自動初期化および更新は TIMESTAMP にのみ適用され、テーブル内の最大 1 つの TIMESTAMP フィールドでのみこの機能を使用できました。 MySQL 5.6.5 以降では、自動初期化と更新は TIMESTAMP 値と DATETIME 値の両方に適用され、値の数に制限はありません。

参照:

1. http://dev.mysql.com/doc/refman/5.6/en/datetime.html

2. http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

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