かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

MySQL CREATE TABLE timestamp 型のカラムの default 値を NULL にしようとしてハマる

Docker の MySQL で build 時にデータベースとテーブルを作ろうとしていたのですが、エラーが発生してコンテナが起動できなくなっていました。
原因を調べてみた結果どうやらテーブル作成でエラーが出ていたのが原因でした。

MySQL v5.7

エラーの発生した

CREATE TABLE IF NOT EXISTS users (
  # … 略
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT null ON UPDATE CURRENT_TIMESTAMP
);

=> ERROR 1067 (42000): Invalid default value for 'updated_at'

TIMESTAMP 型はデフォルトで NOT NULL が設定されている

TIMESTAMP の初期化と NULL 属性
デフォルトでは、TIMESTAMP カラムは NOT NULL であり、NULL 値を含めることはできず、NULL を割り当てると現在のタイムスタンプが割り当てられます。NULL を含めるように TIMESTAMP カラムを許可するには、NULL 属性で明示的に宣言します。
cf. MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能

ということらしい。
TIMESTAMP 型でデフォルト値を NULL にしたい場合は次のようにすればOK

CREATE TABLE IF NOT EXISTS users (
  # … 略
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP null ON UPDATE CURRENT_TIMESTAMP
);

明示的にするなら updated_at TIMESTAMP null DEFAULT null ON UPDATE CURRENT_TIMESTAMP

TIMESTAMP 型とデフォルト値

次のようなテーブルを作成して試してみました

CREATE TABLE t1 (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  ts1 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

テーブル構造

mysql> desc t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | NO   | PRI | NULL              | auto_increment              |
| ts1   | timestamp | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| ts2   | timestamp | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| ts3   | timestamp | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts4   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

VALUE を NULL でデータ挿入

INSERT INTO `t1`
  (`id`, `ts1`, `ts2`, `ts3`, `ts4`)
  VALUES
  (NULL, NULL, NULL, NULL, NULL);

👇

mysql> SELECT * FROM t1;
+----+------+------+------+---------------------+
| id | ts1  | ts2  | ts3  | ts4                 |
+----+------+------+------+---------------------+
|  1 | NULL | NULL | NULL | 2020-07-13 14:33:43 |
+----+------+------+------+---------------------+

TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP な設定だとデフォルト値は CURRENT_TIMESTAMP だが明示的に NULL を INSERT すると NULL が入るようになっていました。

更新してみる

UPDATE t1
  SET id=2
  WHERE id=1;

👇

mysql> SELECT * FROM t1;
+----+---------------------+---------------------+---------------------+---------------------+
| id | ts1                 | ts2                 | ts3                 | ts4                 |
+----+---------------------+---------------------+---------------------+---------------------+
|  2 | 2020-07-13 14:47:21 | 2020-07-13 14:47:21 | 2020-07-13 14:47:21 | 2020-07-13 14:47:21 |
+----+---------------------+---------------------+---------------------+---------------------+

on update CURRENT_TIMESTAMP がセットされていれば UPDATE 時に値を渡さなければ自動的に現在の時間が入るっぽい

 

おわり


[参考]

SQLアンチパターン

SQLアンチパターン

  • 作者:Bill Karwin
  • 発売日: 2013/01/26
  • メディア: 大型本