MySQLのテーブルにレコード作成時刻や更新時刻を自動で記録する列を作る

たまに新しいテーブルを作るたびに調べてるのでメモです。
(タイトルにMySQLと書いてますが、実際はそれと互換のAuroraでやってます。)

DBのテーブルに新しい行を追加したり、更新したりするときにその時刻を記録する列を用意しておくと言うのは結構一般的なことだと思います。
phpMyAdmin からGUIでポチポチ設定してもいいのですが、SQLでもできた方が楽です。

さて、その方法ですが、MySQLのドキュメントにそのまま書いてあります。
11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能

DEFAULT CURRENT_TIMESTAMP で、行作成時にタイムスタンプが記録され、
ON UPDATE CURRENT_TIMESTAMP で、行更新時にタイムスタンプも更新されます。

列名として、 created_at, updated_at を使う場合は次のようにすれば良いでしょう。
例えば、タイムスタンプの他にidとnameを格納するテーブルを作るとしたら次のようになります。
(ついでに NOT NULL制約も入れました。)

CREATE TABLE
    sample_table (
        id INTEGER,
        name VARCHAR(255),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

さて、試してみましょう。まず作成時刻から。

INSERT INTO
    sample_table(id, name)
VALUES
    (1, 'テスト1'),
    (2, 'テスト2'),
    (3, 'テスト3')
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | テスト2    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

時刻が勝手に入りましたね。

次に更新です。

UPDATE
    sample_table
SET
    name = 'test 2' 
WHERE
    id = 2
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | test 2     | 2021-04-04 23:36:29 | 2021-04-04 23:38:26 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)

id = 2 のレコードの updated_at も更新されました。