MySQLのデータ型TIMESTAMPでハマる

調子に乗って、MySQLのテーブルのカラムにtimestamp型を指定したら、Java側でSQLExceptionの例外が発生したんですよ、な～に～！？やっちまったな！

男は黙って、ALTER TABLE、というわけで、カラムの変更しちゃいますか。

・Timestampに0000-00-00があると例外が発生します。 |

MySQLのTIMESTAMP型

無効な DATE、DATETIME、または TIMESTAMP 値は、適切な型の「ゼロ」値 ('0000-00-00' または '0000-00-00 00:00:00') に変換されます。

MySQL では日付値解釈の特定のプロパティーに注意してください。

MySQL は、文字列として指定された値に、「緩やかな」形式を使用でき、この形式では、どの句読点文字でも日付部分と時間部分の区切り文字として使用できます。場合によっては、この構文は偽りになることがあります。たとえば、'10:11:12' などの値は、「:」区切り文字のために時間値のように見えることがありますが、日付のコンテキストで使用された場合は、'2010-11-12' の年と解釈されます。値 '10:45:15'は、'45' が有効な月ではないので、'0000-00-00' に変換されます。

日付および時間の部分と小数秒部分との間の区切り文字として認識される唯一の文字が小数点です。
サーバーは、月と日の値が、それぞれが 1 から 12 と 1 から 31 の範囲内にあるだけではなく、有効である必要があります。厳密モードが無効になっていると、'2004-04-31' のような無効な日付は '0000-00-00' に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。
MySQL は、日または月カラムにゼロを含んだ TIMESTAMP 値や、無効な日付の値を受け入れません。このルールに対する唯一の例外は、特殊な「ゼロ」値である '0000-00-00 00:00:00' です。
MySQL 5.6.4 より前では、テーブルから選択しない場合、CAST() は TIMESTAMP 値を文字列として扱います。(これは、FROM DUAL を指定した場合にも当てはまります。)セクション12.10「キャスト関数と演算子」を参照してください。
2 桁の年を含む日付の値は、世紀が不明なためあいまいです。MySQL は次のルールを使用して 2 桁の年の値を解釈します。
- 00-69 の範囲の値は 2000-2069 に変換されます。
- 70-99 の範囲の値は 1970-1999 に変換されます。
セクション11.3.8「日付での 2 桁の年」も参照してください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

TIMESTAMP型のデフォルト値

デフォルト値が設定できたようです。

TIMESTAMP および (MySQL 5.6.5 以降の) DATETIME データ型では、自動初期化と現在の日付および時間への自動更新機能が用意されています。詳細は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」を参照してください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

ちなみに、自分がTIMESTAMP型のカラムを持つテーブルを作成したときのSQL文はこんな感じ。

CREATE TABLE supervisor(
  user_name VARCHAR(20) NOT NULL,
  user_pass VARCHAR(50) NOT NULL,
  last_login TIMESTAMP NOT NULL,
  last_logout TIMESTAMP NOT NULL
);

で、テーブル構成見てみると、

f:id:ts0818:20170811150117j:plain

「DEFAULT値」に、「last_login」のほうは「CURRENT_TIMESTAMP」が、「last_logout」のほうは「0000-00-00 00:00:00」とか設定されてる。

で、テーブルにINSERT文でデータを追加しましたら、

INSERT INTO supervisor (user_name, user_pass) VALUES ('fs6p', 'password');

f:id:ts0818:20170811150116j:plain

last_logoutカラムの値に、「0000-00-00 00:00:00」が入るという。カラムに何も値を入れない場合はデフォルト値が入るみたいなんですが、デフォルト値はNULLのはずなんですが...ウッカリ八兵衛ミスをしてました。

DATETIME は、NOT NULL 属性で定義されていないかぎり (この場合、デフォルトは 0 です)、デフォルトは NULL です。

テーブル作るときに思いっきり、NOT NULL 属性を付けてしまってました。（でも、「last_login」のほうは、NOT NULL属性つけたのにデフォルト値が「CURRENT_TIMESTAMP」になっているという謎現象が...）

とりえあず、デフォルト値を変更『ALTER TABLE supervisor CHANGE last_logout last_logout timestamp not null default current_timestamp;』を実行。

MySQL [fs6project_db]> ALTER TABLE supervisor CHANGE last_logout last_logout timestamp not null default current_timestamp;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

f:id:ts0818:20170811150119j:plain