複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える

タイトルで何を言ってるのか我ながら良くわからない。。

mysql56> SELECT * FROM t1 JOIN t2 USING(num);
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
| num | val   | created             | updated             | val    | created             | updated             |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
|   1 | one   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | eins   | 2016-03-01 15:38:44 | 2016-03-01 16:40:29 |
|   2 | two   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zwei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:33 |
|   3 | three | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | drei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:36 |
|   4 | four  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | vier   | 2016-03-01 15:38:44 | 2016-03-01 16:40:59 |
|   5 | five  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | funf   | 2016-03-01 15:38:44 | 2016-03-01 16:41:05 |
|   6 | six   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sechs  | 2016-03-01 15:38:44 | 2016-03-01 16:41:11 |
|   7 | seven | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sieben | 2016-03-01 15:38:44 | 2016-03-01 16:41:16 |
|   8 | eight | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | acht   | 2016-03-01 15:38:44 | 2016-03-01 16:41:27 |
|   9 | nine  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | neun   | 2016-03-01 15:38:44 | 2016-03-01 16:41:34 |
|  10 | ten   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zehn   | 2016-03-01 15:38:44 | 2016-03-01 16:41:46 |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

t1.updated, t2.updatedはそれぞれDATETIME ON UPDATE CURRENT_TIMESTAMPなカラム。
やりたいことは、

mysql56> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:35 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:40 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.01 sec)

この時点でt1.updatedとt2.updatedを同じ時刻にすること。



1. リテラル渡す

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

はじめっからUPDATE ON current_timestampなんてつけないでほしかった。


2. 1ステートメントで更新する

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 JOIN t2 USING(num) SET t1.val= 'updated', t2.val= 'updated' WHERE t1.num = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP()はNOW()のシノニムなので、そのまま使うと「ステートメント開始時点の現在時刻」を返す。
という訳で1ステートメントなら開始時刻は1つに定まる。

どう考えても綺麗にJOINできないテーブルとかあるしINSERTやDELETEが混じったトランザクションで詰むので却下。


3. timestamp変数使う

さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。タイムスタンプをゼロ以外の値に設定すると、後続の NOW() が起動されるたびに、その値が返されます。タイムスタンプをゼロに設定すると、この効果が取り消され、再度 NOW() が現在の日付と時間を返すようになります。

http://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now

というわけでこうじゃ。

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= @@timestamp;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql56> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= 0;
Query OK, 0 rows affected (0.00 sec)

コミットした後は（前でもいいけど）timestamp変数を0に戻しておかないと、そのコネクションのNOW()がいつまでもSET timestampした時点の時刻を返すようになる。


…はじめっからUPDATE ON current_timestampなんてつけないでほしかった。