今起きた事を話す

MySQL5.6の timestamp型で、こんな感じの default指定のない timestamp型の create tableを書いた場合

1
2
3
4
5
create table xxxxx (
  id bigint(20) unsigned auto_increment not null comment 'ID'
  , created_at timestamp comment '作成日時'
  , updated_at timestamp comment '更新日時'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysqldumpすると、以下のようにデフォルト値が設定されます

1
2
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '作成日時',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新日時',

そしてこの mysqldumpしたSQLをMySQL5.7で流すと、 Invalid default valueとなりました

1
#42000 Invalid default value for 'created_at'

どうしてこうなった

公式より抜粋

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

たぶんこのあたりの仕様に基づいて解釈されている気がする

サーバーは、月と日の値が、それぞれが 1 から 12 と 1 から 31 の範囲内にあるだけではなく、有効である必要があります。厳密モードが無効になっていると、’2004-04-31’ のような無効な日付は ‘0000-00-00’ に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。

とあるので、 ALLOW_INVALID_DATESを有効にしてやれば通る模様。

1
2
3
4
5
6
// 有効にする場合
mysql> SET sql_mode = ALLOW_INVALID_DATES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

// 確認コマンド
mysql> SELECT @@GLOBAL.sql_mode;

しかし、なんか悪い事をしている感じがする

もうちょっと調べた

こちらによると、explicit_defaults_for_timestampというフラグが5.6で追加されててデフォルトではOFFになっているらしい。

1
2
3
4
5
6
7
mysql> show global variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

結局

前述のフラグなどの条件を確認しつつ、 defaultに明示的に何か指定がある方が結果的に良いみたいなので、 default NULLをつけました

1
2
3
4
5
create table xxxxx (
  id bigint(20) unsigned auto_increment not null comment 'ID'
  , created_at timestamp default NULL comment '作成日時'
  , updated_at timestamp default NULL comment '更新日時'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;