レコードを登録・更新した場合のタイムスタンプを保存するためのテーブル設定。
- 日時の型は
DATETIMEで設定 - 作成時の初期値を作成時点の日時とするには
DEFAULTを指定DEFAULT CURRENT_TIMESTAMP
- 更新時に更新時点の日時で書き換える場合はON UPDATEを指定
ON UPDATE CURRENT_TIMESTAMP
テーブル作成のSQL例。
|
1 2 3 4 5 |
CREATE TABLE test ( number INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); |
SQL実行結果。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> CREATE TABLE test ( -> number INT, -> created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.01 sec) mysql> DESCRIBE test; +------------+----------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+-------------------+-----------------------------------------------+ | number | int | YES | | NULL | | | created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +------------+----------+------+-----+-------------------+-----------------------------------------------+ 3 rows in set (0.00 sec) |
テストデータ登録。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> INSERT INTO test (number) VALUES (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test; +--------+---------------------+---------------------+ | number | created_at | updated_at | +--------+---------------------+---------------------+ | 1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 | | 2 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 | | 3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 | +--------+---------------------+---------------------+ 3 rows in set (0.00 sec) |
2つ目のデータを変更して、更新日時が変更されていることを確認。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> UPDATE test SET number=9 WHERE number=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test; +--------+---------------------+---------------------+ | number | created_at | updated_at | +--------+---------------------+---------------------+ | 1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 | | 9 | 2021-08-20 06:41:34 | 2021-08-20 06:43:13 | | 3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 | +--------+---------------------+---------------------+ 3 rows in set (0.00 sec) |