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なんてつけないでほしかった。
0 件のコメント :
コメントを投稿