MySQLしかやったこと無い僕がPostgreSQLへのドアを叩いてみる。『指定したカラムに更新日を自動で入れてくれる ON UPDATE句について』 #mysql #postgresql

注意

※すみません。 MySQLの話です。

PostgreSQL入門にて気づいた事。

ドットインストール PostgreSQL入門 #05 フィールドに制約をつけてみよう を見てた時に、
知った事について、まとめてみようかと。

驚いたのは、default制約についてですね。
created timestamp default 'now' とする事で、
created という timestamp 型 のカラムに対して、初期値 現在時刻 を代入する事が出来る、という制約です。

※※ ただし、公式のドットインストールにもありましたが、
この制約は、 初期値に テーブル作成の日時 が入ってしまう制約らしいです。※※

正しくは、こちら。

※ ちなみに、ドットインストール だと、 created timestamp default statement_timestamp() としてください、って書いてあったんだけど、何が違うんでしょう?

ともあれ、今まで、ずっと、 created カラムに NOW() を入れていた () 僕としては。。

PostgreSQL いいなー。

MySQL にもあったら良いのに。。

あった

全然、公式ページ ( MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能 ) にあった。。

知らんかった。。。

それだけじゃない

また、読んでいくと..

ON UPDATE CURRENT_TIMESTAMP なんてのを付けると、
なんと、テーブルの更新があれば値を更新日時で更新してくれるらしい、と。

今まで、ずっと、 updated カラムに NOW() を入れていた僕としては・・・

という事で、以下、試してみました。

参考になれば、幸いです。

試してみました。

テストのDB date_test_mysql を作って、 users テーブルを作ります。

mysql> create database date_test_mysql character set utf8;
Query OK, 1 row affected (0.01 sec)

mysql> create table users (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(255), created datetime default CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table users;
+-------+-----------------------------------------+
| Table | Create Table                                                                                                                                               
+-------+-----------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------+
1 row in set (0.01 sec)

データを一件、Insertします。

想定としては、 created カラムに、 現在時刻が入る事を想定します。

mysql> select now();
  2017-01-24 00:00:41

mysql> insert into users (name) values ('test111');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+---------+---------------------+
| id | name    | created             |
+----+---------+---------------------+
|  1 | test111 | 2017-01-24 00:00:50 |
+----+---------+---------------------+
1 row in set (0.01 sec)

おおー!

試しに、もう一件。

mysql> select now();
  2017-01-24 00:02:30

mysql> insert into users (name) values ('test222');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+---------+---------------------+
| id | name    | created             |
+----+---------+---------------------+
|  1 | test111 | 2017-01-24 00:00:50 |
|  2 | test222 | 2017-01-24 00:02:44 |
+----+---------+---------------------+
1 row in set (0.01 sec)

2レコード目もその時の時刻で、入ってくれました。

まとめ:
created datetime default CURRENT_TIMESTAMP とすれば、初期値として、登録日時が入る。

ON UPDATE 句 について

続いて、ON UPDATE CURRENT_TIMESTAMP について調べてみたいと思います。

さっきのテーブルに対して、変更を加えていきます。

mysql> select now();
  2017-01-24 00:12:51

mysql> alter table users add column updated datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+---------+---------------------+---------------------+
| id | name    | created             | updated             |
+----+---------+---------------------+---------------------+
|  1 | test111 | 2017-01-24 00:00:50 | 2017-01-24 00:12:52 |
|  2 | test222 | 2017-01-24 00:02:44 | 2017-01-24 00:12:52 |
+----+---------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select now();
  2017-01-24 00:22:22

mysql> update users set name = 'test111_2' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+-----------+---------------------+---------------------+
| id | name      | created             | updated             |
+----+-----------+---------------------+---------------------+
|  1 | test111_2 | 2017-01-24 00:00:50 | 2017-01-24 00:22:23 |
|  2 | test222   | 2017-01-24 00:02:44 | 2017-01-24 00:12:52 |
+----+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

2017-01-24 00:12:52 から 2017-01-24 00:22:23 になりました。

まとめ:
updated datetime ON CURRENT_TIMESTAMP とすれば、更新したタイミングで、更新した時の日時が入る。

まとめ

これで、また、ビジネスロジックを減らす事が出来ますね。

PostgreSQLを学んでると、MySQLで分かってない事とか知らない事とか、新たな発見がありすぎて、とても面白いですね。

でも、CURRENT_TIMESTAMP ってどの位置づけになるんだろう? 関数?

あ、そういえば、これ書きながら思ったんですけど、 自分、DB には、 登録日時更新日時 カラムを入れるように教わったんですけど、 それって標準なんかな?