スポンサーサイト

スポンサー広告
-- /-- --
上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

MySQLで値の交換

MySQL
07 /03 2008

2つの列の間で値の交換(SWAP)をしたい場合、PostgreSQLやSQL Serverは簡単だ。

UPDATE tablename SET x=y,y=x;

しかしMySQLではこうはいかない。
MySQLでは、式は左から順に、即時更新されてしまう。
MySQLで値の交換をしたい場合は、多少トリッキーになるが、次のアルゴリズムを使う。

UPDATE tablename SET x=x+y, y=x-y, x=x-y

文字列型の場合は、CONCATとSUBSTRING_INDEXを使う。
※1 MySQLでは文字列の加算に+演算子を使うことが出来ない。
※2 以下の例では、':'がxとyの中に含まれていないことを前提としている。

UPDATE tablename SET x=CONCAT(x, ':', y), y=SUBSTRING_INDEX(x, ':', 1), x=SUBSTRING_INDEX(x, ':', -1);

DATETIME型の場合は、ADDTIMEとTIMEDIFFを使う。
MySQLでは(おそらく他のDBでも) 日時+日時 という足し算は出来ない。また、PHPのmktime()やstrtotime()のように、日時をUNIXタイムスタンプのような数値で表すことも出来ない。
そこで、基準となる日時(以下の例では2000年1月1日)を決め、xとyそれぞれを「基準日時との差」として考える。
例えば、SELECT ADDTIME('2008-5-10 0:00', TIMEDIFF('2007-2-3 0:00', '2000-1-1 0:00')); の結果は、2015-06-13 00:00:00 となる。
これと先ほどのアルゴリズムを組み合わせて、次のようにすれば良い。

UPDATE tablename SET x=ADDTIME(x, TIMEDIFF(y, '2000-1-1 0:00')), y=SUBTIME(x, TIMEDIFF(y, '2000-1-1 0:00')), x=SUBTIME(x, TIMEDIFF(y, '2000-1-1 0:00'));

以下は、うっかりcreate_dtmとupdate_dtmにDATETIMEでなくTIMESTAMP型を指定してしまったために、「更新日時より作成日時の方が新しい」という困った状態になってしまったテーブルを、一括で修正した時の実例。
※3 ALTER TABLEを使い、create_dtm, update_dtm ともにDATETIMEに修正済み。

UPDATE tablename SETcreate_dtm=ADDTIME(create_dtm, TIMEDIFF(update_dtm, '2000-1-1 0:00')), update_dtm=SUBTIME(create_dtm, TIMEDIFF(update_dtm, '2000-1-1 0:00')), create_dtm=SUBTIME(create_dtm, TIMEDIFF(update_dtm, '2000-1-1 0:00')) WHERE create_dtm>update_dtm;

参照:「SQL HACKS」オライリー・ジャパン p.20~23

コメント

非公開コメント

Paq

忘れっぽい中年プログラマが、日々の開発作業の中で、忘れると困ることを書き留めています。

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。