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

スポンサーサイト

MySQLのTIMESTAMP型

MySQL
07 /03 2008

ふと気づくと、このサイトを立ち上げてから丁度1年が経過していた。丸1年放置していたわけだ。
もったいないのでこのサイトを使って、開発時の備忘録を残すことにする。

第1回は、MySQLのTIMESTAMP型について。
これについては以前も1度ハマった事があるが、またやってしまったので、忘れないようにここに書いておくことにする。

TIMESTAMP型とDATETIME型は似ているが、決定的な違いがある。
テーブルの最初にあるTIMESTAMP型のフィールドは、自動更新されると言うことだ。
例えば、次のようなケース。

CREATE TABLE tablename VALUES (id INT, create_dtm TIMESTAMP, update_dtm TIMESTAMP, a INT);
INSERT INTO tablename VALUES (1, NOW(), NOW(), 3);
UPDATE tablename SET update_dtm=NOW(), a=4 WHERE id=1;

この場合、影響を受けるのは update_dtm と a だけのように見えるが、実際には create_dtm にも NOW() がセットされてしまう。
回避するには、明示的に
UPDATE tablename SET create_dtm=create_dtm, update_dtm=NOW(), a=4 WHERE id=1;
と書くか(これは美しくない)、TIMESTAMP型でなくDATETIME型にする(この場合、テーブル内のどの列にもTIMESTAMP型がないようにしなければ意味がない)。

MySQLの設計者は便利な機能として提供してくれているのだろうが、大きなお世話だと思う。

Paq

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

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