MySQLでのdatetimeとtimestampの違いと使用の詳細

一、MySQLでは現在の時間をどう表していますか？
実は、表現がかなり多いです。まとめて次のようにします。
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP（）
NOW()
LOCALTIIME
LOCALtIME（）
LOCALtIMESTAMP
LOCALtIMESTAMP（）
二、TIMESTAMPとDATETIMEの比較について
完全な日付フォーマットは以下の通りです。YYYY-M-DD HH:MM:SS[.fraction]は、2つの部分に分けられます。date部分とtime部分に分けられます。ここで、date部分はフォーマットの「YYYY-M-DD」に対応しています。time部分はフォーマットの「HH:MM:SS.fraction」に対応しています。dateフィールドでは、date部分だけをサポートしていますが、time部分を挿入すると、その部分の内容は破棄され、warningが提示されます。
以下の通りです

mysql> create table test(id int,hiredate date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'20151208104400');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
mysql> select * from test;
+------+------------+
| id  | hiredate  |
+------+------------+
|  1 | 2015-12-08 |
|  1 | 2015-12-08 |
+------+------------+
2 rows in set (0.00 sec)

注：最初にwarningを提示していない理由は、そのtime部分は全部0です。
TIMESTAMPとDATETIMEの同じ点：
1>どちらもYYYY-M-DD HH:MM:SS[.fraction]タイプの日付を表すために使用できます。
 TIMESTAMPとDATETIMEの違い：
1>両者の記憶方式が異なる。
TIMESTAMPは、クライアントが挿入した時間を現在のタイムゾーンからUTC（世界標準時間）に変換して記憶しています。照会するときは、クライアントの現在のタイムゾーンに変換して返します。
DATETIMEについては、何も変わりません。基本的にはそのまま入出力します。
以下、私達は検証します。
まず二つのテスト表を作成します。一つはtimestamp形式を使って、一つはdatetime形式を使います。

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> create table test1(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id  | hiredate      |
+------+---------------------+
|  1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)

mysql> select * from test1;
+------+---------------------+
| id  | hiredate      |
+------+---------------------+
|  1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

両者の出力は同じです。
次に現在のセッションのタイムゾーンを変更します。

mysql> show variables like '%time_zone%'; 
+------------------+--------+
| Variable_name  | Value |
+------------------+--------+
| system_time_zone | CST  |
| time_zone    | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id  | hiredate      |
+------+---------------------+
|  1 | 2015-12-07 16:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+------+---------------------+
| id  | hiredate      |
+------+---------------------+
|  1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)

上記の「CST」とは、MySQLのホストコンピュータのシステム時間を指し、中国標準時間の略語である。China Standard Time UT+8:00
結果から、testの戻り時間は8時間前で、test 1の時間は変わらないことが分かりました。これは両者の違いを十分に検証した。
2>両者が記憶できる時間範囲が異なる。
timestampに格納できる時間範囲は、'1970-01-01 00:00:01.00'から'2038-01-19 03:14:07.9999'です。
datetimeに記憶できる時間範囲は'1000-01-01 00:00:00:00.00'から'9999-12-31 23:59:59.9999'です。
まとめ：TIMESTAMPとDATETIMEは、記憶範囲と記憶方式が違っています。もちろん、タイムゾーンをまたぐ業務に対して、TIMESTAMPはもっと適しています。
三、TIMESTAMPとDATETIMEの自動初期化と更新について
まず、下の操作を見ます。

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id  | hiredate      |
+------+---------------------+
|  1 | 2015-12-08 14:34:46 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
    Table: test
Create Table: CREATE TABLE `test` (
 `id` int(11) DEFAULT NULL,
 `hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ちょっとおかしいと思いますが、私はhiredateフィールドを挿入操作していません。その値は自動的に現在の値に修正されています。また、表を作成する時には、「show create table test\G」の結果に表示されている「DEFALT CURRENT＿u」を定義していません。TIMESTAMP ON UPDATE CURRENT_TIMESTAMP」。
実は、この特性は自動初期化と自動更新です。
自動初期化とは、このフィールド（例えば、上記の例のhiredateフィールド）に対して明示的な割り当てがない場合、自動的に現在のシステム時間に設定されることを意味する。
自動更新とは、他のフィールドが変更された場合、このフィールドの値が自動的にシステム時間に更新されます。
「explicit_」とdefaults_for_timestampのパラメータは関係があります。
デフォルトでは、このパラメータの値はOFFとなり、以下のようになります。

mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF  |
+---------------------------------+-------+
1 row in set (0.00 sec)

官の説明を見てみましょう。
By default，the first TIMESTAMP column has both DEFALT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicity。
多くの場合、これは私達が欲しいものではないですが、どうやって無効にしますか？
1.「explicit_」をdefaults_for_タイムスタンプの値はONします。
2.「explicit_defaults_for_timestampの値は相変わらずOFFです。無効にする方法は二つあります。
     1>DEFAULT文でこの列にデフォルト値を指定します。
     2>この列にNULL属性を指定します。
以下の通りです

mysql> create table test1(id int,hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1\G
*************************** 1. row ***************************
    Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(11) DEFAULT NULL,
 `hiredate` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table test2(id int,hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
    Table: test2
Create Table: CREATE TABLE `test2` (
 `id` int(11) DEFAULT NULL,
 `hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySQL 5.6.5バージョンの前に、Automatic Initialization and UpdatingはTIMESTAMPにしか適用されません。また、1つのテーブルでは、TIMESTAMPフィールドの採用が最大で許可されます。MySQL 5.6.5から、Automatic Initialization and Updatingは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
以上が本文の全部です。皆さんの勉強に役に立つように、私たちを応援してください。